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

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 »

SQL Server assigns each object (tables, view, stored procedure, functions etc) a unique ID and that ID we call as an Object_ID. In simple words, we can say that it creates uniqueness between two objects.
In this article, we will learn how it works, what is the next counter, etc.

What will be the first object_ID in the new database ?
If you create a new object in the database, the first ID will always be 2073058421 in SQL SERVER 2005 and 245575913 in SQL SERVER 2012.
The difference between both versions is due to some new internal tables introduced in SQL SERVER 2012 and got the new ID 2073058421 (filestream_tombstone_2073058421) once you create a database in 2012.

How to get this Object_ID from SQL ?
You just need to use a function OBJECT_ID, to retrieve object ID of any object. For example :

Select Object_ID('test_table');

What will be the object_ID increment counter for user defined objects ?
It will add 16000057 + Last user defined object_ID and will give you a new ID.

What will be the object_ID increment counter for system objects ?
It varies from 1 onwards.

If we drop an object and create the same object, does SQL assign the same object_ID ?
No, SQL will assign a new ID every time.

Why some object IDs are in negative and some IDs are in positive ?

select * from sys.all_objects

If you execute above query, you can see Some IDs are in positive and some are in negative. All user defined objects are assigned a positive object ID plus system tables. Apart from these objects, the rest of the system objects are assigned negative object IDs.

All the above facts and figures can vary in different versions of SQL. If you know something new about Object_ID do share with me.

Read Full Post »

I was working on statistical reports and I had to display whether the given year was leap year or not in a field. So, I developed few ways using SQL SERVER 2012 functions to detect whether leap year or not.

Given below are the multiple ways to detect leap year:

Method 1 :
In method 1, I made a date (2012-02-28) using DATEFROMPARTS function then used EOMONTH function to find the last date (2012-02-29) of February. Then to find the last day (29), used DAY function and after that used IIF to check whether it is 29 or not, to detect if its a leap year or not.

DECLARE @Year INT =2012
SELECT IIF(DAY(EOMONTH(DATEFROMPARTS(@Year,2,28)))=29 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)
Method 2 :
In method 2, I simply made 2 dates using DATEFROMPARTS function. First is the 1st date of the year and 2nd date is the last date of the year and then used DATEDIFF function to find total no of days in a year and finally used IIF function to check if it is 366 or 365 days. If 366, then leap year and if 365 then it is not.

DECLARE @Year INT =2012
SELECT IIF(DATEDIFF(DAY,DATEFROMPARTS(@Year,1,1), DATEFROMPARTS(@year,12,31))+1=366 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)
Method 3 :
In method 3, I first made a date(2012-02-28) using DATEFROMPARTS function then added one day to it. After addition I took the day and checked it using IIF function. If it is 29, then leap year, else, it is not.

DECLARE @Year INT =2012
SELECT IIF(DAY(DATEADD(DAY,1,DATEFROMPARTS(@Year,2,28)))=29 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)

Method 4 :
In method 4, I made 2 dates (2012-02-28, 2012-03-01) using DATEFROMPARTS function and then found the date difference using DATEDIFF function, and finally used IIF function to check the difference. If it is 2 days, means the year is leap, else not.

DECLARE @Year INT =2012
SELECT IIF(DATEDIFF(DAY,DATEFROMPARTS(@Year,2,28), DATEFROMPARTS(@year,3,01))=2 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)

Note : Try @Year as 2013 and all the result set will return that 2013 is not a leap year using any above methods.

Let me know if you know a better method to detect leap year.

Read Full Post »

Web browser has been launched in sql server since SQL Server 2005. And it is available in Menu (View >> Other windows >> Web Browser). And this sql server browser uses the same internet explorer setting on windows server except home page. Whatever home page you setup in windows internet explorer, it will not impact in SQL server web browser settings.

This functionality is same in all sql server from SQL server 2005 and above. In addition to this, SQL Server 2012 provided a functionality to set up internet explorer setting from SQL Server as well and you can setup separate home page for SQL SERVER browser also.

Given below is the path to configure this setting in SQL Server 2012.
Tools >> Options >> Environment >> Web browser

ie_settings1.1

Read Full Post »

Older Posts »