Feeds:
Posts
Comments

Archive for the ‘Tips & Tricks’ Category

Contained databases are one of the favorite features introduced in SQL Server 2012. Contained databases are isolated / independent databases from the other databases and from the instance it is hosted on.

Today, we will go through a very simple script related to contained databases and in this script we need to find the list of all contained databases only. Given below is the script.

Select name as [Database Name], containment ,containment_desc
from sys.databases
Where [containment] =1
--OUTPUT

listcontained DB1.2

Advertisements

Read Full Post »

In my earlier article, I wrote about different aspect of Concat function introduced in SQL Server 2012. In this article, we will compare the Concat function with the traditional concatenation techniques and analyze the performance.

Method 1 : Using ISNULL With + (String Concatenation)

Use AdventureWorks2012
GO
Select
ISNULL([firstName],'')
+ ' '
+ ISNULL([MiddleName],'')
+ ' '
+ ISNULL([LastName],'') from [HumanResources].[vEmployee]

Method 2 : Using COALESCE With + (String Concatenation)

Use AdventureWorks2012
GO
Select
COALESCE([firstName],'')
+ ' '
+ COALESCE([MiddleName],'')
+ ' '
+ COALESCE([LastName],'')
from [HumanResources].[vEmployee]

Method 3 : Using Concat function of SQL Server 2012

Use AdventureWorks2012
GO

Select
CONCAT (
[firstName]
, ' '
, [MiddleName]
, ' '
, [LastName]) from [HumanResources].[vEmployee]

All of the above methods will give you the same result sets but lets view their performance given below.

Method

Concatenation expression

CPU Time

Elapsed Time

1

ISNULL([firstName],”) + ‘ ‘+ ISNULL([MiddleName],”) + ‘ ‘ + ISNULL([LastName],”)

141 ms

2234 ms

2

COALESCE([firstName],”) + ‘ ‘ + COALESCE([MiddleName],”)  + ‘ ‘ + COALESCE([LastName],”)

187 ms

3185 ms

3

CONCAT( [firstName], ‘ ‘, [MiddleName], ‘ ‘, [LastName])

94 ms

1821 ms

Conclusion :
It is needless to stress that Concat function is much faster than the other methods.

Note : The above queries have been tested on ~100K records.

Read Full Post »

Mostly, there are multiple ways to write a script to perform a single task. It varies from Developers to Developers, who normally try to follow the best practice. But sometimes some standards discard from time to time (version to version). In this article, I will go through some standard formats that we used in the prior version of SQL server but does not work in SQL Server 2012. It may break your script.

Let me create a sample in SQL Server 2005/ 2008 to explain it.

USE [test]
GO
CREATE TABLE [dbo].[t_Student](
	[Student ID] [int] IDENTITY(1,1) NOT NULL,
	[Student Name] [nvarchar](50) NULL
)

After creating the sample table lets alter this table. But there are three different formats that you can use to alter the table in earlier version of SQL server (2005/2008)


Method 1 :
In method 1, the format to alter the table will be .Database Name.Schema.Table Name

Alter table .[test].dbo.[t_Student] Add [Contact Details] varchar(50) NULL
--OUTPUT

Command(s) completed successfully

Method 2 :
In method 2, the format to alter the table will be ..Schema.Table Name

use test
GO
Alter table ..dbo.[t_Student] Add [Date of Birth] Datetime NULL
--OUTPUT

Command(s) completed successfully

Method 3 :
In method 3, the format to alter the table will be Schema.Table Name

use test
GO
Alter table dbo.[t_Student] Add [Date of Admission] Datetime NULL
--OUTPUT

Command(s) completed successfully

All of the above methods executed successfully in SQL Server 2005/ 2008.

Lets browse the table.

Select * from t_Student

alter table 1.1

Now, lets do the same exercise in SQL Server 2012.

Method 1 :
In method 1, the format to alter the table will be .Database Name.Schema.Table Name

Alter table .[test].dbo.[t_Student] Add [Contact Details] varchar(50) NULL
--OUTPUT

Msg 117, Level 15, State 1, Line 1
The object name ‘.test.dbo.t_Student’ contains more than the maximum number of prefixes. The maximum is 2.

Method 2 :
In method 2, the format to alter the table will be ..Schema.Table Name

use test
GO
Alter table ..dbo.[t_Student] Add [Date of Birth] Datetime NULL
--OUTPUT

Msg 117, Level 15, State 1, Line 1
The object name ‘..dbo.t_Student’ contains more than the maximum number of prefixes. The maximum is 2.

Method 3 :
In method 3, the format to alter the table will be Schema.Table Name

use test
GO
Alter table dbo.[t_Student] Add [Date of Admission] Datetime NULL
--OUTPUT

Command(s) completed successfully

Conclusion :
If you are planning to upgrade your SQL Server earlier version to SQL Server 2012 or you are using SQL Server 2012, remember, it will not allow you to use methods 1 & 2. In case your are using above methods 1 & 2 in Dynamic SQL or Static SQL, it may break your script.

Reference : MSDN

Read Full Post »

sys.sysprocesses is one of the helpful system views when it comes to query the processes of SQL Server. I have been using this system view for quite a while. But a few days ago I read on MSDN, “This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently uses this feature.”

So, I planned to upgrade my query.
Given below is my query that I used to retrieve processes using specified database since SQL Server 2000.

--This query will work fine on SQL Server 2000 and above.
Declare @database_name as varchar(max)
Set @database_name ='AdventureWorks2012'

Select [spid] as [session_id], [last_batch],[status]
,cmd as [Command], hostname as [host_name]
,[program_name], [loginame] as [Login_name]
from sys.sysprocesses
Where dbid = db_id(@database_name)

sysprocesses1.1

Given below is my new query that works on SQL Server 2012 and above, having the same result set as the above.

Declare @database_name as varchar(max)
Set @database_name ='AdventureWorks2012'

Select A.session_id,A.last_request_end_time as [last_batch],A.[status]
, B.[command],A.[host_name]
,A.[program_name],A.[login_name]
from sys.dm_exec_sessions A
Left Join sys.dm_exec_requests B
On A.[session_id]=B.[session_id]
Where A.[database_id]=db_id(@database_name)

sysprocesses1.2

Conclusion :
You can see that both the above queries result set is same but first query (sys.sysprocesses) will not be applicable for future versions of SQL.

Let me know your feedback regarding both queries.

Read Full Post »

In my previous article, I explained how to use offset rows and fetch next statement. In this article, I will explain Dont's for Offset Rows and Fetch Next.

Let me explain it with simple examples.

Example 1 : (Fetch clause must be greater than zero)

USE AdventureWorks2012
GO
SELECT * FROM [HumanResources].[vEmployee]
ORDER BY [JobTitle]
OFFSET 10 ROWS
FETCH NEXT 0 ROWS ONLY
--OUTPUT

Msg 10744, Level 15, State 1, Line 4
The number of rows provided for a FETCH clause must be greater then zero.

Example 2 : (Offset clause must have integer only)

USE AdventureWorks2012
GO
SELECT * FROM [HumanResources].[vEmployee]
ORDER BY [JobTitle]
OFFSET 1.1 ROWS
FETCH NEXT 10 ROWS ONLY
--OUTPUT

Msg 10743, Level 15, State 1, Line 3
The number of rows provided for an OFFSET clause must be an integer.

Example 3 : (Offset clause may not be negative)

USE AdventureWorks2012
GO
SELECT * FROM [HumanResources].[vEmployee]
ORDER BY [JobTitle]
OFFSET -10 ROWS
FETCH NEXT 10 ROWS ONLY
--OUTPUT

Msg 10742, Level 15, State 1, Line 3
The offset specified in an OFFSET clause may not be negative.

Example 4 : (A Top cannot be used in the same query or sub query as an offset.)

USE AdventureWorks2012
GO
SELECT Top 5 * FROM [HumanResources].[vEmployee]
ORDER BY [JobTitle]
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY
--OUTPUT

Msg 10741, Level 15, State 2, Line 2
A TOP cannot be used in the same query or sub-query as an OFFSET.

If you know any other Dont’s, do share.

Reference : MSDN

Read Full Post »

Auto Recovery Scripts is also one of the helpful features introduced in SQL SERVER 2012. It is GOOD NEWS for those who generally forget to save the scripts in SQL Server Management Studio (SSMS) or for some reason could not save the script or close the SSMS abnormally with scripts. By default in SQL SERVER 2012 (SSMS), this feature is turned on with default settings.

Location of Auto Recovery Option in SSMS :
This option is available in Tools >> Options >> Environment >> AutoRecover

Settings available in Auto Recovery :
Given below are the settings available in Auto Recovery.

  • Save Recovery Information :
    • You can set after how many minutes your script should be saved automatically. By default it is after 5 minutes.
  • Keep auto recovered information :
    • You can also set for how many days SSMS should keep those files. By default it is 7 days.

autorecovery 1.1

You can find the recovery files here :C:\Users\windows user\Documents\SQL Server Management Studio\Backup Files\Solution1

Given below are the images of recovered files :

autorecovery 1.3

autorecovery 1.4

Read Full Post »

Cycle Clipboard Ring is one the nice features available in SQL Server 2012. This feature is available in Edit >> Cycle Clipboard Ring.

cycleclipboardring1.1

Let me explain this feature with an example.

If you copy different stuff in SQL server (SSMS) and paste it, it pastes only whatever stuff you copied Last.
For example :
There are three characters given below:

  1. A
  2. B
  3. C

If you copy/cut all of the above one by one, then paste it in SQL Server (SSMS), it will paste only C because, SQL Server keeps only the last copied item in the clipboard and pastes it.

Now, I have a situation where I need to copy all of the above one by one and paste it also one by one. You can easily do it with the help of Cycle Clipboard Ring

Let me explain it step by step :

Step 1 :
First of all, copy/cut all of the above characters one by one in the same sequence.

Step 2 :
Now, wherever you want to paste, go to to the destination, click on the place.

Step 3 :
Now,

  • if you need to paste C (last character) press and Hold Ctl+Shift and press V one time. Or you can click on Edit menu \Cycle Clipboard Ring just once
  • if you need to paste B (2nd last character) press and Hold Ctl+Shift and press V two times. Or you can click on Edit menu \Cycle Clipboard Ring two times
  • if you need to paste A (1st character) press and Hold Ctl+Shift and press V three times.Or you can click on Edit menu \Cycle Clipboard Ring three times

cycleclipboardring1.2-1

I liked this feature. Let me know how helpful it is for you.

Read Full Post »

Older Posts »