SQL SERVER – How to Hide Yourself from SQL Server? – Guest Post by Balmukund Lakhani

 SQL SERVER   How to Hide Yourself from SQL Server?   Guest Post by Balmukund LakhaniBalmukund Lakhani (Blog | Twitter | Site) is currently working as Technical Lead in SQL Support team with Microsoft India GTSC. In past 7+ years with Microsoft he was also a part of the Premier Field Engineering Team for 18 months. During that time he was a part of rapid on-site support (ROSS) team. Prior to joining Microsoft in 2005, he worked as SQL developer, SQL DBA and also got a chance to wear his other hat as an ERP Consultant.

Balmukund is a great friend and one of the finest SQL Server Expert I know. When I requested him for Guest Post, he has indeed come up with a fantastic blog post and very interesting title of the blog post. I am sure many wants to learn the trick to hide from SQL Server, so here it goes in his own words.

Important: It is Balmukund’s Birthday Today – please wish him all the best for exciting SQL filled New Year! Wish him on Twitter or leave a comment on his Blog.


I am sure that all of us have used SQL Server Management Studio countless times. It’s the common tool used by all DBA and developers. Most of the time we give server name, user name and password (if needed) and hit connect. Right? Have you ever clicked on “Options >>” button and made some changes in connection properties? Pinal wrote about color coding of servers, using “use custom color” option, here and posted a video here which is available in connection properties tab.

advanceconnection SQL SERVER   How to Hide Yourself from SQL Server?   Guest Post by Balmukund Lakhani

In this blog, we are going to talk about the third tab “Additional Connections Parameters”. When we want to test connecting string and those options are not available in earlier two tabs, then we can provide them in this tab (available in SQL 2008 onwards). The parameters should be separated by semicolons (;). There are a few parameters in connection string which can be spoofed. Let us start from hiding from SQL Server.

Attempt 1: Hide from SQL Server

Enter following parameters in the Additional Connection Parameter and click on Connect.
Application Name=Pinal-SSMS;Workstation ID=Pinal-PC

advanceconnection1 SQL SERVER   How to Hide Yourself from SQL Server?   Guest Post by Balmukund Lakhani

Once we connect and try to check our connection properties, the result of this would be as follows:

SELECT HOST_NAME, program_name
FROM sys.dm_exec_sessions
WHERE session_id = @@spid

advanceconnection2 SQL SERVER   How to Hide Yourself from SQL Server?   Guest Post by Balmukund Lakhani

If any DBA sees this, they would think that Pinal is working but it’s Balmukund in reality. And, I have hidden myself.

Attempt 2: Hide SQL Server from User (Deceive User)

Now we have hidden ourselves from SQL Server – let us do something crazy. Let us hide the SQL Server from the user. This can be extremely confusing so I suggest to use it carefully or never use it at all. If I can, I would have put red flashing lights around previous statement.

Now let us change the Data Source property in Additional Connection Parameters place. Let us confuse user that they are connecting to instance SQLServer2000 even though they are really connecting to instance BIG. My localhost is named as BIG and in following connection I am providing a incorrect server name Big\SQLServer2000 in the Server Name. In normal case it will give us the error. However, in this case we will provide an incorrect name in the Login >> ServerName connection.

advanceconnection3 SQL SERVER   How to Hide Yourself from SQL Server?   Guest Post by Balmukund Lakhani

We will right away click on “Additional Connection Parameters” and will enter following text in the string Parameters:  Data Source=localhost

advanceconnection4 SQL SERVER   How to Hide Yourself from SQL Server?   Guest Post by Balmukund Lakhani

Now let us hit connect and you will see following situation.

advanceconnection5 SQL SERVER   How to Hide Yourself from SQL Server?   Guest Post by Balmukund Lakhani

Well, as warned earlier, SSMS would override the value and hence we are connected to a server which is in additional connection parameters. Everywhere in SSMS, we would see instance SQLServer2000 which is in login tab, but actual server is the SQL Server 2012. Now imagine a drop database executed in SSMS, thinking that this was ainstance SQLServer2000 but they end up on instance BIG. It is quite possible some of the commands will now return error if they are deprecated in instance SQLServer2000. This will be fun prank – which you should not play with anyone.

Moral of the story: Be careful while using this tab. Use this only for those settings which are not available in management studio by default.

Hope you have learned something new today.


Thanks Balmukund Twitter for this wonderful guest post.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Wrap SQL Code in SSMS – SQL in Sixty Seconds #038 – Video

60x60 SQL SERVER   Wrap SQL Code in SSMS   SQL in Sixty Seconds #038   VideoEvery developer has a different habit. Some like to format the functions in upper case and some wants it in lower case. I often see developers listing the columns in SELECT clause in a different way. I have my own preference but I do respect the other developer’s preference as well. I do not advise to change anybodies habit but there is one thing which I strongly prefer to do on the client side when I am editing code.

WRAP THE CODE!

Well, it is indeed very difficult to read the code when users have to horizontally scroll the code. Now when I am working on the file originally created by another developer, I do not like to change their formatting. I have a very wide screen on my personal desktop computer but when I see the same on my 12″ laptop, I see the horizontal scrolling. There can’t be a single solution in the case of the wrapping the code, which fits all the screens.

Fortunately, there is a solution which will fit all the screens.

SQL Server Management Studio has a setting which will wrap the code displayed on the screen. Even if you resize the screen the code wraps. It will be very difficult to explain that in words – I suggest you watch following SQL in Sixty Seconds Video on this subject.

You can access the settings from SSMS >> Tools >> Options >> Text Editor >> Transact-SQL. It will show the following screen.

wrapsql SQL SERVER   Wrap SQL Code in SSMS   SQL in Sixty Seconds #038   Video

Let us see the same concept in this SQL in Sixty Seconds:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Take Database Backup using SSMS – SQL in Sixty Seconds #037 – Video

60x60 SQL SERVER   Take Database Backup using SSMS   SQL in Sixty Seconds #037   VideoWhenever I am suggesting something which changes how database works or the existing status of the database, my suggestion along with it is to take the database backup before making such changes. If the changes are in configurations, that can be easily revert but if the changes are such that it will impact the data, I always suggest to take backup. The nature of this blog is such that we have readership from readers with different expertise, some are experts and some are novice.

One of the questions I keep on receiving comments are whenever I give suggestion to take a full database backup is – how to take database backup. It is not possible to explain in each and every blog regarding how to take backup of the database as it will be of no interest to the users who are very much familiar with this concept. To help the beginners who have yet to learn regarding how to take database backup, here is the SQL in Sixty Series episode which explains how to take database backup.

There are three types of the backup – 1) Full Backup 2) Differential Backup and 3) Log Backup. We can take the differential backup as well log backup from the same screen where we take full backup from. I have explained the same also in later parts of the video.

If you are using SQL Server Management Studio – here is the path which will bring you to the screen where you can take various kinds of backup.

Right Click on Database >> Tasks >> Back up… 

Let us see how to take backup in this SQL in Sixty Seconds:

Please note that if your database is large, it will take long time to take backup. We will focus on the various steps to initiate the backup in this video.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video

60x60 SQL SERVER   Select and Delete Duplicate Records   SQL in Sixty Seconds #036   VideoDevelopers often face situations when they find their column have duplicate records and they want to delete it. A good developer will never delete any data without observing it and making sure that what is being deleted is the absolutely fine to delete. Before deleting duplicate data, one should select it and see if the data is really duplicate.

In this video we are demonstrating two scripts – 1) selects duplicate records 2) deletes duplicate records.

We are assuming that the table has a unique incremental id. Additionally, we are assuming that in the case of the duplicate records we would like to keep the latest record. If there is really a business need to keep unique records, one should consider to create a unique index on the column. Unique index will prevent users entering duplicate data into the table from the beginning. This should be the best solution. However, deleting duplicate data is also a very valid request. If user realizes that they need to keep only unique records in the column and if they are willing to create unique constraint, the very first requirement of creating a unique constraint is to delete the duplicate records.

Let us see how to connect the values in Sixty Seconds:

Here is the script which is used in the video.

USE tempdb
GO
CREATE TABLE TestTable (ID INT, NameCol VARCHAR(100))
GO
INSERT INTO TestTable (ID, NameCol)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Second'
UNION ALL
SELECT 4, 'Second'
UNION ALL
SELECT 5, 'Second'
UNION ALL
SELECT 6, 'Third'
GO
-- Selecting Data
SELECT *
FROM TestTable
GO
-- Detecting Duplicate
SELECT NameCol, COUNT(*) TotalCount
FROM TestTable
GROUP BY NameCol
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
GO
-- Deleting Duplicate
DELETE
FROM
TestTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TestTable
GROUP BY NameCol)
GO
-- Selecting Data
SELECT *
FROM TestTable
GO
DROP TABLE TestTable
GO

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

http://www.pinaldave.com/bimg/sixty00.jpgConcatenating  string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL Server 2012 has introduced new function CONCAT which concatenates the strings much efficiently.

When we concat values with ‘+’ in SQL Server we have to make sure that values are in string format. However, when we attempt to concat integer we have to convert the integers to a string or else it will throw an error. However, with the newly introduce the function of CONCAT in SQL Server 2012 we do not have to worry about this kind of issue. It concatenates strings and integers without casting or converting them. You can specify various values as a parameter to CONCAT functions and it concatenates them together.

Let us see how to concat the values in Sixty Seconds:

Here is the script which is used in the video.

-- Method 1: Concatenating two strings
SELECT 'FirstName' + ' ' + 'LastName' AS FullName
-- Method 2: Concatenating two Numbers
SELECT CAST(1 AS VARCHAR(10)) + ' ' + CAST(2 AS VARCHAR(10))
-- Method 3: Concatenating values of table columns
SELECT FirstName + ' ' + LastName AS FullName
FROM AdventureWorks2012.Person.Person
-- Method 4: SQL Server 2012 CONCAT function
SELECT CONCAT('FirstName' , ' ' , 'LastName') AS FullName
-- Method 5: SQL Server 2012 CONCAT function
SELECT CONCAT('FirstName' , ' ' , 1) AS FullName

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Auto Recovery File Settings in SSMS – SQL in Sixty Seconds #034 – Video

Every developer once in a while facing an unfortunate situation where they have not yet saved the work and their SQL Server Management Studio crashes. Well, you can minimize the loss by optimizing auto recovery settings. In this video we can see how to set the auto recovery settings.

Go to SSMS >> Tools >> Options >> Environment >> AutoRecover

There are two different settings:

1) Save AutoRecover Information Every Minutes

This option will save the SQL Query file at certain interval. Set this option to minimum value possible to avoid loss. If you have set this value to 5, in the worst possible case, you can loose last 5 minutes of the work.

2) Keep AutoRecover Information for Days

This option will preserve the AutoRecovery information for specified days. Though, I suggest in case of accident open SQL Server Management Studio right away and recover your file. Do not procrastinate this important task for future dates.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)

 

SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video

A very common requirement of developers is to format datetime to their specific need. Every geographic location has different need of the date formats. Some countries follow the standard of mm/dd/yy and some countries as dd/mm/yy. The need of developer changes as geographic location changes. In SQL Server there are various functions to aid this requirement. There is function CAST, which developers have been using for a long time as well function CONVERT which is a more enhanced version of CAST. In the latest version of SQL Server 2012 a new function FORMAT is introduced as well.

In this SQL in Sixty Seconds video we cover two different methods to display the datetime in specific format. 1) CONVERT function and 2) FORMAT function.

Let me know what you think of this video. Here is the script which is used in the video:

-- http://blog.SQLAuthority.com
-- SQL Server 2000/2005/2008/2012 onwards
-- Datetime
SELECT CONVERT(VARCHAR(30),GETDATE()) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),10) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),110) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),5) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),105) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS DateConvert;
GO
-- SQL Server 2012 onwards
-- Various format of Datetime
SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS DateConvert;
SELECT FORMAT ( GETDATE(), 'dd mon yyyy HH:m:ss:mmm', 'en-US' ) AS DateConvert;
SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS DateConvert;
SELECT FORMAT ( GETDATE(), 'HH:m:ss:mmm', 'en-US' ) AS DateConvert;
GO
-- Specific usage of Format function
SELECT FORMAT(GETDATE(), N'"Current Time is "dddd MMMM dd, yyyy', 'en-US') AS CurrentTimeString;

This video discusses CONVERT and FORMAT in simple manner but the subject is much deeper and there are lots of information to cover along with it. I strongly suggest that you go over related blog posts in next section as there are wealth of knowledge discussed there.

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Rename Columnname or Tablename – SQL in Sixty Seconds #032 – Video

We all make mistakes at some point of time and we all change our opinion. There are quite a lot of people in the world who have changed their name after they have grown up. Some corrected their parent’s mistake and some create new mistake. Well, databases are not protected from such incidents. There are many reasons why developers may want to change the name of the column or table after it was initially created. The goal of this video is not to dwell on the reasons but to learn how we can rename the column and table.

Earlier I have written the article on this subject over here: SQL SERVER – How to Rename a Column Name or Table Name. I have revised the same article over here and created this video.

There is one very important point to remember that by changing the column name or table name one creates the possibility of errors in the application the columns and tables are used. When any column or table name is changed, the developer should go through every place in the code base, ad-hoc queries, stored procedures, views and any other place where there are possibility of their usage and change them to the new name. If this is one followed up religiously there are quite a lot of changes that application will stop working due to this name change.  One has to remember that changing column name does not change the name of the indexes, constraints etc and they will continue to reference the old name. Though this will not stop the show but will create visual un-comfort as well confusion in many cases.

Here is my question back to you – have you changed ever column name or table name in production database (after project going live)? If yes, what was the scenario and need of doing it. After all it is just a name.

Let me know what you think of this video. Here is the updated script.

USE tempdb
GO
CREATE TABLE TestTable (ID INT, OldName VARCHAR(20))
GO
INSERT INTO TestTable
VALUES (1, 'First')
GO
-- Check the Tabledata
SELECT *
FROM TestTable
GO
-- Rename the ColumnName
sp_RENAME 'TestTable.OldName', 'NewName', 'Column'
GO
-- Check the Tabledata
SELECT *
FROM TestTable
GO
-- Rename the TableName
sp_RENAME 'TestTable', 'NewTable'
GO
-- Check the Tabledata - Error
SELECT *
FROM TestTable
GO
-- Check the Tabledata - New
SELECT *
FROM NewTable
GO
-- Cleanup
DROP TABLE NewTable
GO

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video

Copy data from one table to another table is one of the most requested questions on forums, Facebook and Twitter. The question has come in many formats and there are places I have seen developers are using cursor instead of this direct method.

Earlier I have written the similar article a few years ago – SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE. The article has been very popular and I have received many interesting and constructive comments. However there were two specific comments keep on ending up on my mailbox.

1) SQL Server AdventureWorks Samples Database does not have table I used in the example
2) If there is a video tutorial of the same example.

After carefully thinking I decided to build a new set of the scripts for the example which are very similar to the old one as well video tutorial of the same. There was no better place than our SQL in Sixty Second Series to cover this interesting small concept.

Let me know what you think of this video. Here is the updated script.

-- Method 1 : INSERT INTO SELECT
USE AdventureWorks2012
GO
----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Person
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO
---------------------------------------------------------
---------------------------------------------------------
-- Method 2 : SELECT INTO
USE AdventureWorks2012
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Person
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Introduction to Big Data – Guest Post

BIG Data – such a big word – everybody talks about this now a days. It is the word in the database world. In one of the conversation I asked my friend Jasjeet Sigh the same question – what is Big Data? He instantly came up with a very effective write-up.  Jasjeet is working as a Technical Manager with Koenig Solutions. He leads the SQL domain, and holds rich IT industry experience. Talking about Koenig, it is a 19 year old IT training company that offers several certification choices. Some of its courses include SharePoint Training, Project Management certifications, Microsoft Trainings, Business Intelligence programs, Web Design and Development courses etc.


Big Data, as the name suggests, is about data that is BIG in nature. The data is BIG in terms of size, and it is difficult to manage such enormous data with relational database management systems that are quite popular these days.

Big Data is not just about being large in size, it is also about the variety of the data that differs in form or type. Some examples of Big Data are given below :

  • Scientific data related to weather and atmosphere, Genetics etc
  • Data collected by various medical procedures, such as Radiology, CT scan, MRI etc
  • Data related to Global Positioning System
  • Pictures and Videos
  • Radio Frequency Data
  • Data that may vary very rapidly like stock exchange information

Apart from difficulties in managing and storing such data, it is difficult to query, analyze and visualize it.

bigdataimage SQL SERVER   Introduction to Big Data   Guest Post

The characteristics of Big Data can be defined by four Vs:

  1. Volume: It simply means a large volume of data that may span Petabyte, Exabyte and so on. However it also depends organization to organization that what volume of data they consider as Big Data.
  2. Variety: As discussed above, Big Data is not limited to relational information or structured Data. It can also include unstructured data like pictures, videos, text, audio etc.
  3. Velocity:  Velocity means the speed by which data changes. The higher is the velocity, the more efficient should be the system to capture and analyze the data. Missing any important point may lead to wrong analysis or may even result in loss.
  4. Veracity: It has been recently added as the fourth V, and generally means truthfulness or adherence to the truth. In terms of Big Data, it is more of a challenge than a characteristic. It is difficult to ascertain the truth out of the enormous amount of data and the one that has high velocity. There are always chances of having un-precise and uncertain data. It is a challenging task to clean such data before it is analyzed.

Big Data can be considered as the next big thing in the IT sector in terms of innovation and development. If appropriate technologies are developed to analyze and use the information, it can be the driving force for almost all industrial segments. These include Retail, Manufacturing, Service, Finance, Healthcare etc. This will help them to automate business decisions, increase productivity, and innovate and develop new products.


Thanks Jasjeet Singh for an excellent write up.  Jasjeet Sign is working as a Technical Manager with Koenig Solutions.

Reference: Pinal Dave (http://blog.SQLAuthority.com)