SQL Server tip : Query a range of characters ?

If you need to query all People from Person table with FirstName start with A, you can easily query like below,


What if you want to list all the people from person table with first name start with A and B? You can combined Like operator with OR and query like below,


But imagine scenario where you need to query all the People with FirstName start with A to M. How will you do that?

Solution is you need to put like operator in the square bracket as below,


If you modify one little thing in above query and replace like operator with equal operator , you will get no error message however you will get no rows returned as well.

SQL Server Tip : . Where to use SQURE Bracket around table name?

Have you ever thought, what if your table name

1. Is exactly as of SQL Server keyword

2. Is having space in between like Student Fav Actor

If you go ahead and query against table Student Fav Actor like below, most likely you will get error message as below,


There may me one more scenario where table name is something like FROM. If you query in usual way, obviously you will be getting expected error message.


So how to query table named [However this is wrong practice to put spaces and use SQL Server keyword as table name] like above?

Very simple is the solution, put table name in square brackets.


You might encounter columns name of table are having spaces or named as keywords. In that case also you need to apply square bracket with that particular column name to work with.

Output clause in SQL Server

Level: Beginners [level 100]

Output   clause gives access to Inserted, updated or deleted rows of table. In SQL Server 2005 this was possible only through Triggers.

Note: In all explanation, I am going to use School Database. You can download script from here

Imagine a scenario that while inserting a value in Person table, for the audit purpose you need to insert value FirstName column and insertion date in audit table. This was possible in SQL Server through Triggers. Whereas in SQL Server 2008 we can achieve this using Output clause  Smile

I created a table called Audit_Person in school database.



Now while inserting in Person table, I need to insert a row in this table also.


In above statement if you notice we are fetching inserted first name using Inserted.FirstName and current date using GetDate and inserting in Audit_Person table.

Let us say you have another scenario in which

  1. You want to delete all the person First Name started with P
  2. Insert First Name of deleted row with deleted date in Audit_Person table

To write above query you can use Output clause.


Scripts used in above explanation are below. Feel free to use them  Smile

Create  table Audit_Person
         (PersonName nvarchar(max),
          DateOfEntry date);

Insert into Person(LastName ,FirstName)
    Output Inserted.FirstName,
    into Audit_Person

Delete from Person
  Output deleted.Firstname ,
   Into audit_person
 where FirstName like 'P%';

I hope this post was useful. Thanks for reading  Smile

SQL Server: WAITFOR Statement

WAITFOR statement used to delay execution of T-SQL command for a specified period of time. This can be used to block the execution of batch statement, stored procedure and T SQL commands for a specified time.

There are two parameters can be used with WAITFOR statement.


Let us create simple stored procedure


Let us execute above stored procedure after delay of 1 minute


In above query we are using delay with WAITFOR.


Above query will execute stored procedure at 10.06PM. Time we will need to provide on scale of 24 hrs. For 1 Pm we need to give input as 13.00.

String Concatenation and Scope Resolution Operator in SQL Server 2008 R2

Target Audience: Beginners

SQL Server 2008 R2 comes with String Concatenation Operator.

String Concatenation operator is denoted by symbol plus ‘+’.


Output we will get,


String concatenation operator can concatenate string with real time data of any type also.


In above query , firstName and lastName column is being concatenated using string concatenation operator of SQL Server 2008 R2. In above query we are also concatenating HireDate . Since HireDate is of type DateTime , we need to convert that first before concatenating.


SQL Server 2008 R2 comes with Scope Resolution Operator.

Scope Resolution operator is denoted by symbol ‘::’

It is used to access static members of compound data type.


In above query hierarchyId is a compound data type. And we are accessing static function GetRoot() of this type.



Microsoft Sql Server, Error : 10061

I tried to connect SQLTEST instance of SQL Server and got below error


This is very common error and can be caused due to many reasons. The way I solved it

1. Go to Start

2. Type Run

3. Type Services.msc

4. Then locate for SQL Server instance you are trying to connect. In my case this is SQL TEST. Right click and select Properties


5. In Properties Dialog box go to Log On tab


Now if you have recently changed the login pass word of the system. Make sure for that particular account you have changed password here also. Better option would be make Log on as Local System account

6. Navigate to General tab and change the Startup type to Automatic and Start the service


7. Now try to connect SQL Server for that particular instance. You should successfully able to connect.

Tracking Change Data Capture in SQL Server 2008

A very important feature of SQL Server 2008 is that we can enable CDC [Change Data capture] on database or table.

We can track the database had CDC enabled by querying IS_CDC_ENABLED column


In above query School is name of the database.

If we want to track tables in database whether CDC enabled or not then


Above query will list name of all tables in School database CDC enabled.

Data Compression in SQL Server 2008

SQL Server 2008 automatically compress data stored in database. SQL server does Lossless data compression.


SQL server uses Dictionary based compression algorithm.

Row Level Data Compression

For Row level Data Compression SQL Server does not use explicitly any standard compression algorithm. It works on very simple algorithm. Say,

1. You created a column of CHAR(50)

2. Normally SQL server requires 50 bytes regardless of the actual byte needed by your Data.

3. If you are storing “DEBUG MODE” in that column then you really need 10 bytes of storage.

So in Row Level Data Compression, rather than fixed format data storage, SQL Server stores data in variable format.


We need to say at time of table creation that DATA_COMPRESSION = ROW

Estimate Row Level compression saving


In above query dbo is name of the schema and TempTable is name of the table and ROW parameter says to estimate Row level estimation.

Page Level Data Compression

In SQL server 2005 page level compression was done by minimizing data redundancy but in SQL Server 2008 it is performed by

1. Reducing Data Redundancy

2. Lossless Data Compression algorithm

3. Column Prefix compression


With column prefix compression first SQL Server identifies repeated byte sequence in beginning of column in all rows on the page. If same column is having same byte pattern in more than one row then SQL server stores the byte pattern once and replaces the other byte patterns with the pointer.

SQL Server creates Dictionary per page and stores repeating vales of the page in the dictionary and performs the compression on Dictionary.

Compression Saving is directly proportional to repeated byte patterns


Estimate Page Level compression saving


In above query dbo is name of the schema and TempTable1 is name of the table and PAGE parameter says to estimate Row level estimation.

Create a SQL Login user in SQL Server 2008

This article will give a walkthrough on creating a SQL Login user.

Step 1

Login to SQL Server 2008 instance using windows authentication


Step 2

Right click on server instance name and select properties.


Step 3

Property window will be open. In that select the Security tab.


In server authentication tab you can see your SQL Server in configured for

1. Windows authentication mode

2. SQL Server and Windows Authentication mode

So if you want to enable SQL User login to SQL Server then select checked mixed mode here .


And then click ok. Now SQL Server is configured for mixed mode login.

Step 4

Now to create SQL LOGIN, right click on Security tab and select New and then Login .


Step 5

When you click on Login you will get the below window


Give the Login Name


Select SQL Server Authentication and provide your desired password


Disable Enforce password policy


Select default database


Leave default language and click OK to create a new SQL Server login

Now in object explorer you can see User1 login .


Now at time of connecting to SQL Server login User1 can be used.