Can we insert explicit values into IDENTITY column in MS SQL

This is one of my favorite interview question. I hope you also would have faced this question, if not then there is a high possibility that you may face it soon!
Answer to the question Can we insert explicit values into IDENTITY column is MS SQL is YES. You can insert values into the IDENTITY column as you require.

Lets start step by step.

First lets create a table for the demo purpose,

CREATE TABLE DemoIdentity

(

    Id int IDENTITY (1, 1) not null,

    Name varchar(50) not null,

    [Address] varchar (100) not null,

    Phone char (15) not null

)

 

Table is ready, now we will insert some values to the above table.

INSERT INTO [DemoIdentity] ([Name] ,[Address] ,[Phone]) VALUES ('Asheej','Bangalore','9916123456')

INSERT INTO [DemoIdentity] ([Name] ,[Address] ,[Phone]) VALUES ('David','Birmingham','7543789234')

INSERT INTO [DemoIdentity] ([Name] ,[Address] ,[Phone]) VALUES ('Cliff','Columbus','4081234567')

Above is the standard way to insert the values into the table which has IDENTITY column.

Now lets try to insert a value with identity column value,

 

INSERT INTO [DemoIdentity] ([ID], [Name] ,[Address] ,[Phone]) VALUES (4,'Johnson','Perth','61478976782')

If you execute above SQL statement you will get below error,

Msg 544, Level 16, State 1, Line 11
Cannot insert explicit value for identity column in table 'DemoIdentity' when IDENTITY_INSERT is set to OFF.

Yes, now we will see the property which MS SQL provides you to insert values in IDENTITY column.

In SQL we have the property named IDENTITY_INSERT. Whenever you wanted to insert values to an IDENTITY column we just need to enable the property IDENTITY_INSERT.

Lets enable this property and insert a value in above table.

Syntax for enabling the IDENTITY_INSERT is,

SET IDENTITY_INSERT [DemoIdentity] OFF

Now we will insert the value by executing the same above statement by specifying the IDENTITY column value and see the result.
 
One important thing you have to remember is, in order to enable or disable the IDENTITY_INSERT  property you should be part of the sysadmin group.

Make sure you disable the IDENTITY_INSERT ON once you are done with the insert statement as this column is not intended to insert the values explicitly.

Syntax for disabling the IDENTITY_INSERT is,

SET IDENTITY_INSERT [DemoIdentity] ON

 

I hope now you understood the whole concept of the property IDENTITY_INSERT and how you can insert explicit values into an IDENTITY column.

Rank VS Dense_Rank in MS SQL

Today we will some interesting SQL feature. You would have seen in SQL RANK and DENSE_Rank function. Did you ever think why we have these two functions and what exactly is the difference between RANK and DENSE_RANK in MS SQL.

My task for the day is to teach you the difference between RANK and DENSE_Rank in MS SQL.

First I will give a small description about the difference and then we will go through the sample.

RANK—> Rank function will help you to find the RANK within your ordered partition. If you have same rank for the two rows then the next rank will be skipped. Which means if you have 3 items with the same rank as 3 then the next rank will be 6. 4th and 5th rank will be skipped.

DENSE_RANK—> Dense_Rank function will help you to rank the the item within your partition same like RANK function but it will not skip any ranking, it will be consecutive. Which means if you have the same scenario we taken above that is 3 items with the same rank as 3 then the next rank will be 4 in case of dense_rank.

Now lets see the demo.

First we will create the table

CREATE TABLE [dbo].[tblStatistics](

    [ID] [numeric](18, 0) NULL,

    [Name] [varchar](50) NULL,

    [Salary] [numeric](18, 0) NULL,

    [City] [nchar](20) NULL

) ON [PRIMARY]

Lets query the table and see the output,

Above one is the simple select query. Now we will query using RANK and DENSE_RANK together to see the difference.

Rank and Rank_Dense query syntax is slightly different from our regular query as you have to mention the partition in query.

 

SELECT [Name]

      ,[Salary]

      ,[City]

      ,RANK() over (partition by City order by Salary) Rank_Demo

      ,dense_rank() over (partition by City order by Salary) Dense_Rank_Demo

      FROM [tblStatistics]

Lets execute above query and see the result.

You would have noticed that the skipping happened in the RANK for Rank column and consecutive rank for the Rank_Dense column in the above screen print.

That’s all..now you learned the difference between RANK and DENSE_RANK in SQL.

Thanks for reading my article!

Compatibility level 90 is deprecated in MS SQL 2014

As we are going to use MS SQL 2014 in near future, compatibility level setting is one of the important thing we should know. Especially when you are migrating SQL from older version we should set this compatibility level properly otherwise your database migration will be a flop.

In MS SQL 2014 Microsoft declared that compatibility level 90 is deprecated.

What it means for the DBA or a developer?

To know the answer for the above question first you should know what is compatibility level. I will give you a short table below to understand how it is related to each version MS SQL.

Compatibility Level	MS SQL Server Version	Applies to
80	SQL Server 2000	SQL Server 2008 through SQL Server 2008 R2
90	SQL Server 2005	SQL Server 2008 through SQL Server 2012
100	SQL Server 2008 and SQL Server 2008 R2	SQL Server 2008 through SQL Server 2014
110	SQL Server 2012	SQL Server 2012 through SQL Server 2014
120	SQL Server 2014	SQL Server 2014 through SQL Server 2014

 

Now you might be clear what is this compatibility level all about and why Level 90 is deprecated in SQL 2014.

When Microsoft says compatibility level 90 is deprecated which means when you are migrating from SQL server 2005 or older version to SQL server 2014 you have to change the compatibility level from 90 to 100, that is the minimum compatibility level SQL 2014 supports.

Once you install SQL server 2014 then the default compatibility level will be set it as 120. Where as when you migrate your database from older version database retains its existing compatibility level if it is at least 100, which means it retains its compatibility level if the database you are migrating is SQL 2008 or above.

So here comes the catch. What will happen if you are migrating SQL server 2005 or older version. In such cases we have to change the compatibility level to 100 manually.
Microsoft recommends below steps to change the compatibility level,

 

1. Login to the database and set the database to single-user access mode by using ALTER DATABASE SET SINGLE_USER.
   
2. Change the compatibility level of the database.
   

   ALTER DATABASE AdventureWorks2012
   SET COMPATIBILITY_LEVEL = 100;
   GO

3. Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.

Other option is login to the database. Select the DB you wanted to change the compatibility level and then right click to open the properties.

Click on properties and then select Options. Scroll down to change the Database to Single User mode and then click OK.

Now click on the Compatibility level dropdown and select the preferred compatibility level. Once you change the compatibility level then scroll down again and change the database to multi user mode.
You are done now!!!

I would like to offer you further reading in this regard too, here is the MSDN link related to the same topic.

I hope you enjoyed reading this and learnt something interesting.

Error 8672 in MS SQL 2008 while using Merge statement

This is one of the weird error you get in MS SQL 2008 if you have Merge statement in your query.

Below is the complete exception message which you may receive. 

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. [SQLSTATE 42000] (Error 8672).

Interesting thing about the above exception is you will get this exception even if you don’t have duplicate rows are there during the join operation. When I got this message I was checking for the duplicate rows first as the error message was highlighting that.

Any way Microsoft informed that this is a bug in SQL Server 2008. In order to resolve this issue you have to install Cumulative Update 6 for SQL Server 2008 Service Pack 1. You can download the same from Microsoft Download centre. Here is the link for “Cumulative Update 6 for SQL Server 2008 Service Pack 1”.

Microsoft created a KB article to address this issue. If you are interested to know more about this issue please refer Microsoft KB article.

I hope after installing the above updates your error is disappeared and the same query is running without any issues.

TransactionScope in ASP.NET

This is one of the good feature which will help you when you have to do multiple database operations and you wanted to make sure that all the transaction has to be either committed or roll backed as a single unit of work.
TransactionScope is exactly same as the one we use in database level. But sometimes we may not use transaction at database level if the data/table we are operating is completely different and there is no connections.
Couple of important points you have to remember while using TransactionScope is, TransactionScope class cannot be inherited, also this is part of the System.Transactions which means if you wanted to use the class TransactionScope then first you have to refer to the assembly System.Transactions.

Filtered Index in MS SQL

What is filtered index? What is the benefit of using filtered index? Does filtered index really improves the performance? All these queries will be answered in this article.
Lets start with what is filtered index? A kind of interview question.
Filtered index is an optimized non clustered index. Filtered index is more suitable for the queries which select the data from a well defined subset. Important thing you have to remember about the filtered index is it uses the filter predicate to index only a portion of rows in a table. This is what I have mentioned above that “filtered index is suitable for the queries which select the data from a well defined subset”.
Does the filtered index improve the performance?

Connect LocalDB using Visual Studio

Today let me answer few interview question and answer about local DB. We are going to cover, What is LocalDB? How to start LocalDB? How to stop LocalDB? How to connect LocalDB from visual Studio? How to create tables in LocalDB using Visual Studio.
Lets start with what is Local DB?
In earlier version Visual Studio installation SQL express edition gets installed automatically. People were really confused with this database and was having difficult time in connecting and managing this database. So from Visual Studio 2012 onwards Microsoft decided to have LocalDB engine in place of SQL Server express. This is a very light weight database with a minimum set of files required to create a database and it will directly tied up with the project it is attached to.
Lets see how to start LocalDB.

How to use OUTPUT Clause in MS SQL / What is the use of OUTPUT Clause?

Today we will learn some SQL. Have you ever used OUTPUT clause in MS SQL? What exactly us the use of OUTPUT clause in MS SQL? What is the advantage of using OUTPUT clause in SQL? All these questions are answered in this article.

What is OUTPUT clause in SQL?

OUTPUT clause gives you the information about each row impacted by running INSERT or UPDATE or DELETE or MERGE SQL statement. Normally in applications we use this information to return to the user in a meaningful manner to show the result of submitting the data.

What you can do is, you may save this information in a temporary table or in a permanent table itself for future use. You can use this information for auditing purpose also because it has got complete information about the sol statement which you executed row by row.

The main disadvantage of this OUTPUT clause is it returns the same information even if your SQL execution throws error or it is rolled back. So you need to make sure that such information should not be used for any auditing purpose or to pass the information to client.

Now lets see what kind of information we gets when we use OUTPUT clause.

First we will create a sample table.

CREATE TABLE [dbo].[tblBill](

    [BillDate] [datetime] NULL,

    [Expense] [numeric](18, 0) NULL,

    [travel] [numeric](18, 0) NULL

)

GO

Lets query the table and see the values we have inserted in this table.

Now we will delete one row with the OUTPUT clause and see the result and also the syntax to use output clause.

  delete from [DemoAzure].[dbo].[tblBill] 

  OUTPUT DELETED.*

  where travel = 150

As you can see above OUTPUT clause should be before where clause.

Once you execute this statement it will return the complete row which got deleted like a select statement. This is the reason I mentioned earlier that we can directly insert this values into a table for future use or for auditing purpose.

Lets see the result set now.

Lets see a sample code to insert the value in a table variable.

DECLARE @tblBillVar table( 

    [BillDate] [datetime] NULL,

    [Expense] [numeric](18, 0) NULL,

    [travel] [numeric](18, 0) NULL);

INSERT INTO [dbo].[tblBill] ([BillDate],[Expense],[travel]) 

OUTPUT INSERTED.[BillDate], INSERTED.[Expense], INSERTED.[travel]

        INTO @tblBillVar

VALUES ('2015-01-06 00:00:00.000',875,475)

select * from @tblBillVar

Once you execute above query you will find that the inserted values will be displayed in the query analyzer.

Similar to table variable you can have your own table as well.

I hope you are now clear about the OUTPUT clause in MS SQL.

GROUPING SETS in MS SQL

I have been doing some reporting work today. In that report I have to show some trending. In fact I have to show average salary based on the city and department. I was thinking what will be the easiest option to display in that manner, only thing is I don’t want to write huge SQL query to show this data.  Nothing to worry now, you have an option in MS SQL that is nothing by grouping set.
Today we will see how we can use GROUPING SET in SQL query and when exactly we can use this query.
First we will create a table with the name tblStatistics.

GROUP BY clause to get comma separated values in MS SQL

Today we will see how to get the comma separated values from SQL table. This may require during the project development to send the report or to send an email to multiple people.
 
We will create the table first.

CREATE TABLE [dbo].[StatusReport](

    [StudentID] [int] NULL,

    [Status] [nchar](10) NULL,

    [Email] [varchar](50) NULL

)

How to find Quarter Name and Month Name from the Datetime in MS SQL

Recently I was developing one Material Management System and got a requirement to get the details quarterly with the Month name in a report.
Since it is a simple query I thought I will share you he same as it may might be useful.
Lets create the table first.

create table Items

(

ItemName varchar(500),

uom char(200),

Quantity float(50),

Price numeric(5,2),

Bill_Date datetime

)

How to get Month wise summary in MS SQL

One of the common query I used to see in Microsoft forum is “How to get month and year from a datetime column in SQL Server”, Since I saw this question today again I thought I will write small article about this.
Mostly we require this query when we generate reports. Month wise report or Month and Year wise report definitely require such kind of query.
We will directly jump on to the solution and the query.
First we will create a table with datetime field and two numeric columns to get the sum based on the month and year.

Synchronize local MS SQL database to SQL Azure

In this article I will be taking you through the steps to synchronize your local MS SQL Database to SQL Azure. This is one of the regular requirement you may find in your application development when you are using Windows Azure. Synchronizing the local database will allow you to export all the data from your local database to SQL Azure in few seconds or in few minutes.
SQL Azure allows you to configure this synchronization automatically and will allow you to set the frequency of data synchronization as well. 
Ok, as usual we will go through the complete procedure step by step.
First we will create a database in our local database system and insert few data in the table.

Deploy ASP.NET web application in cloud with SQL Azure Connectivity

Today I am taking you to the complete deployment of an ASP.NET web application with database connectivity in Microsoft Azure. Since database is in cloud we have to do some configuration changes while doing the deployment. In this article we will do the sample development in local system and we will do only the deployment in Microsoft Azure. As you are aware once deploy the application in Microsoft Azure by using SQL Azure as database everything will be online and people will be able to access the application over the net.
Lets go through the complete procedure you have to follow to do the web application deployment with SQL Azure.

How to Create Database in SQL Azure

Are you looking for a way to connect windows Azure and create database? If you wanted to know how to create database table in Microsoft cloud then ready this article. Here I am going to explain how to create database, how to create table and then finally how to query the table from SQL Azure.
First we will connect to the Windows Azure. You should have a windows Azure account in order to login to Azure site. Here is the URL to login to Windows Azure.
Once you login to your account you will find below page.

How to find Operating System version using SQL query

There are some cases you wanted to check the Operating system installed on your SQL server. You may not have remote login access to the server where SQL server is installed in that case it will be difficult to know what is the operating system installed on your server where SQL database is installed.

In this article I am going to explain you the simple query which you can run in your SQL server Query analyzer and it will show you the Operating System version installed on your database server.

SQL Server by default provides you an inbuilt table with all the details of the operating system installed on your database server. sys.dm_os_windows_info is the table you may query in this regard.

Your final query will be,

SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version

FROM sys.dm_os_windows_info

And the output of the above query will be,

windows_release	windows_service_pack_level	windows_sku	os_language_version
6.1	Service Pack 1	10	1033

In the above result you can see that windows release is 6.1 means Windows Server 2008 R2 in my case. It can be Windows 7 if your database is not installed on the server.

You can find all the operating system version number in below chart.

Operating system	Version number
Windows 8.1	6.3
Windows Server 2012 R2	6.3
Windows 8	6.2
Windows Server 2012	6.2
Windows 7	6.1
Windows Server 2008 R2	6.1
Windows Server 2008	6
Windows Vista	6
Windows Server 2003 R2	5.2
Windows Server 2003	5.2
Windows XP 64-Bit Edition	5.2
Windows XP	5.1
Windows 2000	5

What is SET NOCOUNT in SQL Server

You must have seen the statement SET NOCOUNT ON in most of the stored procedure. When I saw this message first time I was thinking why this has been used in almost all stored procedure what will happen if I set SET NOCOUNT OFF in place of ON. Here in this article I thought I will share you the advantage of using SET NOCOUNT ON in SQL Stored Procedure and how this will improve the performance of your query.

The main advantage of using SET NOCOUNT ON in your stored procedure or TSQL statement is, this statement automatically suppress the message which shows how many rows has been affected by the query. Which means, this will reduce the overhead of the server and improves the performance when you execute query in a table which has got millions of data.

What exactly is happening inside the server is when you execute a stored procedure with SET NOCOUNT ON  it prevents the sending of DONE_IN_PROC messages to the client for each statement in your stored procedure. Imagine you have a loop or several statement has to be executed!!! So using SET NOCOUNT ON will definitely boost your query performance and adding the line SET NOCOUNT ON is one of the main DBA tasks when they find that query or procedure is not performing well.

If you wanted to know the message of affected rows then you can add SET NOCOUNT OFF in your query. This will show the number of rows affected by executing the query.

Irrespective of using SET NOCOUNT ON or SET NOCOUNT ON  SQL server internally updates @@ROWCOUNT function. So querying @@ROWCOUNT function will give you the affected rows details at any time.

Now let’s see how it shows in SQL query analyzer,

Below is my stored procedure, you can notice that I have commented the line SET NOCOUNT ON. 

CREATE PROCEDURE [dbo].[sp_GetCustomerList]

AS

BEGIN

    --SET NOCOUNT ON;

    SELECT * from Customer

END

We will execute this procedure and see the result.

Now we will un comment the line SET NOCOUNT ON and see the output,

CREATE PROCEDURE [dbo].[sp_GetCustomerList]

AS

BEGIN

    SET NOCOUNT ON;

    SELECT * from Customer

END

Hope you are now clear about the usage of SET NOCOUNT ON.

Rename Stand-Alone Instance of SQL Server 2012 Database

Are you looking for an option to rename the SQL 2012 database? If you have an existing stand-alone instance of an SQL 2012 database you wanted to rename the database then this cannot be down from object explorer. When you right click on your database you will be surprised that Rename option will be disabled. Ok, don’t get panic we an do this renaming by running simple query.

Let’s how does it looks if you right click on your database in SQL Server 2012,

Since this is disabled we will use SQL query to rename the database.

USE master;

GO

ALTER DATABASE DemoRename

Modify Name = DemoRenameDB ;

GO

 

Now just refresh your Database and see the result,

How to insert comma separated value in a table using Stored procedure

Are you looking for a way to insert comma separated value in a table using SQL Stored Procedure? This article will help you to insert comma separated value in a different row. I thought I will write an article with full details because I used to see this question in Microsoft forums. 

Let’s start by creating a sample table first.

 

--Create table: 

CREATE TABLE tblNoOfAttempt(UserID INT IDENTITY,NoOfAttemp VARCHAR(50)) 

Since we have the table ready we will create the stored procedure to insert the comma separated value in our table.

 

-- Create Stored Procedure: 

Create PROCEDURE sp_InsertNoOfAttempt 

-- Add the parameters for the stored procedure here 

      @NoOfAttempt VARCHAR(100) 

AS 

BEGIN 

SET NOCOUNT ON; 

declare @XML xml 

SELECT @XML=CONVERT(xml,'<root><s>' + REPLACE(@NoOfAttempt,',','</s><s>') + '</s></root>') 

INSERT INTO tblNoOfAttempt 

SELECT [Value] = XM.SP.value('.','varchar(50)') 

FROM @XML.nodes('/root/s')XM(SP) 

END 

GO 

Now it’s the time to execute the stored procedure by passing some comma separated value.

-- Execute stored procedure by passing the comma separated value 
sp_InsertNoOfAttempt '8,7,10,9,5' 

Since the first field is an identity column we don’t need to pass the parameter for that. Value will be inserted automatically.

At last let’s see how the value is inserted in to he table by running select query.

-- Select the table values to see the out put 

select * from tblNoOfAttempt

Out put of the above query will be,

The Report Server Installation Is Not Initialized (rsReportServer Not Activated)

Technorati Tags: The Report Server Installation Is Not Initialized (rsReportServer Not Activated)

This is one of the common error you may get when you rebuild or restore or upgrade your reporting server database and browse the report service manager URL. You may get the similar error when you browse the .rdl report also which was working earlier when your server was running fine.

To resolve this error you may follow one of the below steps,

Option 1:

• Open Reporting Services Configuration Manager
• Go to Encryption Keys
• Delete Encrypted Content
• Click on Delete

Option 2:

• Go to Reporting Services Configuration Manager.
• Go to "Encryption Keys" to backup the key.
• Navigate to "Database" tab.
• Click "Change Credentials" to reset the connections.

Now you will be able to browse the report without any issue.