Feeds:
Posts
Comments

Archive for the ‘Tips’ Category

SQL SERVER 2012 – Insert Snippet …

Posted in SQL SERVER, Tips, tagged , , , , , on May 3, 2013| 4 Comments »

SQL Server 2012 came up with many new features and enhancements. Insert Snippet is one of the new features introduced in it. This feature reduces the development time and increases productivity. How ? It basically generates the code snippet for the most frequently used objects in SQL Server instantly (with few clicks or key strokes).  But it is not limited to the frequently used objects, custom code snippet template can also be added in it and can generate it at any point of time, to make life easier. How to create custom code snippet will be demonstrated in my up coming article.

Given below are the object’s code snippet available in SQL Server as a standard template :

  • Function 
  • Index
  • Login
  • Role
  • Schema
  • Stored Procedure
  • Synonym
  • Table
  • Trigger
  • User
  • User Defined Data Type
  • User Defined Table Type
  • User Defined Type
  • View

Let me insert a Create Table code snippet step by step using Insert snippet .

Step 1:

Open a new query window in the Database Engine and place the cursor where the code snippet has to be inserted.

Step 2:

In this step, launch snippet picker to insert Snippet. It can be done using three different methods. Given below are the details of these methods.

Method 1:

Press CTRL+K, CTRL+X to launch snippet picker.

 Method 2:

Go to the Edit Menu then point to IntelliSense and then select Insert Snippet to launch snippet picker as shown in the picture below .

insertsnippet1.1

Method 3:

Right click and select Insert Snippet to launch snippet picker as shown in the picture below.

insertsnippet1.2

Step 3:

Once the snippet picker is launched, select table from the list as shown in the picture below.

insertsnippet1.3

Step 4:

After selecting the Table press TAB or Enter key to proceed as shown in the picture below.

insertsnippet1.4

Step 5:

After selecting the Create Table script as show in the above picture then again press TAB or Enter key to generate the code snippet as shown below.

insertsnippet1.5

Step 6:

Notice that the create table code snippet in the above image has been generated. Now just change the highlighted area to create desired customized table.  Additional columns as well can be added.

insertsnippet1.6

Conclusion :

Insert Snippet is a very nice feature introduced in SQL Server 2012. Time saving tool. The example discussed above took less than 10 seconds to create it. Also it is not limited to create table code snippet alone, any object code snippet can be created in less than 10 seconds.

Will post how to create the customized code snippet in my upcoming post.

Read Full Post »

SQL SERVER 2012 – FORCESEEK Table Hint – Enhancement

Posted in SQL SERVER, Tips, tagged , , , , on April 16, 2013| 2 Comments »

SQL Server new versions always come up with the new performance optimization techniques, enhancements and additions. FORCESEEK table hint enhancement is also one of the new enhancements that came up in SQL Server 2012.

Let me create an index on Purchasing. [PurchaseOrderDetail] table to demonstrate this enhancement.

USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_PurchaseOrderID]
ON Purchasing.[PurchaseOrderDetail]
(
[PurchaseOrderID] ASC
) WITH (DROP_EXISTING = OFF) ON [PRIMARY]

FORCESEEK in earier version of SQL :
In the ealier version of SQL Server, FORCESEEK table hint needed only an index on the table and it could optimize your query. But sometimes you may have more than one index and you cannot define those indexes anywhere in the syntax of forceseek (Limitation). Let me script it to elaborate the same.

---This query will work on SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d with (forceseek)
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

FORCESEEK in SQL Server 2012 :
In SQL Server 2012, you can define index name along with the column in forceseek table hint to further optimize your query. But you can also use the above syntax  and it will not break your code.

Let me explain it with examples :

Example 1 : (Define the index name in forceseek) 

---This query will work on SQL Server 2012 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d with
(forceseek,INDEX(IX_PurchaseOrderDetail_PurchaseOrderID))
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

Example 2 : (Define the index name along with the column name in forceseek) 

---This query will work on SQL Server 2012 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d
with (forceseek(IX_PurchaseOrderDetail_PurchaseOrderID(PurchaseOrderID)))
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

Conclusion:

In SQL Server 2012, FORCESEEK  came up with the very nice enhancement that you can define index name along with the column in FORCESEEK, to have further control on query optimization. However, remember this is the last option we should choose at the time of optimization. Finally, this is only recommended for experienced programmers.

Reference : MSDN

Read Full Post »

SQL SERVER – Undocumented function – fn_dblog

Posted in Functions, SQL SERVER, Tips, tagged , , , on April 15, 2013| 10 Comments »

fn_dblog is one of my favorite undocumented functions; I use it to design multiple recovery solutions. Today, I will discuss its purpose, syntax, parameters and one of the problems that I faced with respect to its parameters.

Purpose :
It gives you the detail view of the transaction log.

Syntax:

fn_dblog (@start,@end)

Parameters:
It accepts two parameters, given below are the details.
@start : Start LSN but it must be in the integer format.
@end : End LSN but it must be in the integer format.

Let me explain it with simple examples :
Example 1: (With default parameters) 

Select * from fn_dblog(NULL,NULL)
--OUTPUT

fn_dblog1.1

Example 2: (Passed LSN)
Lets take a current LSN from above example and pass it as a parameter.The purpose to pass a single LSN is to pick up only one transaction log record.

use test
Go
Select * from fn_dblog('000005c9:0000133d:0022','000005c9:0000133d:0022')
--OUTPUT

Msg 9005, Level 16, State 3, Line 1
Either start LSN or end LSN specified in OpenRowset(DBLog, …) is invalid.

Ooops…… I am unable to execute it.

The reason behind the above error message is that I just picked up LSN from above result set and passed it into fn_dblog.
Whereas the correct way to do it is, first convert the LSN to integer and then pass it to fn_dblog.

Given below is the script that will convert your hexa decimal LSN into integer LSN.

Declare @Xml Xml
Declare @String Varchar(Max)
Declare @delimiter Varchar(5)
Set @delimiter=':'
Set @String ='000005c9:0000133d:0022'
SET @Xml = cast(('<a>0x'+replace(@String,@delimiter,'</a><a>0x')+'</a>')
AS XML)

;With CTE as (SELECT A.value('.', 'varchar(max)') as [Column]
from @Xml.nodes('a') AS FN(A) )
Select Stuff((Select ':' +
Convert (varchar(max),Convert(INT,cast('' AS XML).value
('xs:hexBinary(substring(sql:column("[Column]"),3) )', 'varbinary(max)')))
from CTE for xml path('') ),1,1,'') as [Current LSN]
--OUTPUT

1481:4925:34

Lets pass the above output (1481:4925:34) to the fn_dblog and view the result set.

use test
Go
Select * from fn_dblog('1481:4925:34','1481:4925:34')
--OUTPUT

fn_dblog1.2

Now, you can see that fn_dblog filter worked and it picked up only one LSN.

Conclusion :
No doubt fn_dblog is one of the helpful undocumented functions but do not use this function in the production server unless otherwise required. Also, remember to convert the hexa decimal to integer before passing it to fn_dblog as a parameter.

Read Full Post »

SQL Server 2012 – Breaking Changes – ALTER TABLE statements

Posted in DML, SQL SERVER, Tips, tagged , , , , , , on March 7, 2013| Leave a Comment »

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 »

SQL SERVER – How to run sp_spaceused for all tables in a database

Posted in Solutions, SQL SERVER, Tips, tagged , , , , , on February 27, 2013| 7 Comments »

sp_spaceused is one of the frequently used stored procedures when it comes to view the number of rows, reserved size, data size, index size, unused space in a table. I myself utilized it many a time.

Given below are the three methods to run sp_spaceused or equivalent for all tables in a database to get number of rows, reserved size, data size, index size, unused space but this time we will measure the performance as well.

Method 1 :
Given below is one line script that can achieve the required result but it will cost you a performance issue because it will loop all the tables in the database one by one and will give you the result set.

EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?]';

Given below is one of the server execution times.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 1768 ms.

If you have more tables, it will give you the error message as given below.
The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

Method 2 :
Given below is another method to achieve it but this method will also cost you a performance issue because it will also loop all the tables in the database one by one and will give you the result set. Reference

Set statistics time on
CREATE TABLE ##TempTable
(name nvarchar(128)
,rows char(11)
,reserved varchar(18)
,data varchar(18)
,index_size varchar(18)
,unused varchar(18)
)
declare @UserTableName nvarchar(40)
declare UserTableSize cursor for
select rtrim(name) from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name

open UserTableSize
fetch next from UserTableSize into @UserTableName
while @@fetch_status = 0
begin
Insert ##TempTable
exec sp_spaceused @UserTableName
fetch next from UserTableSize into @UserTableName
end
close UserTableSize
deallocate UserTableSize
Select *  from ##TempTable
--Drop Table ##TempTable
Set statistics time off
GO

Given below is one of the server execution times.
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 1034 ms.

Method 3 :
This method will not loop all the tables in the database one by one and also you don’t need to insert its result set into a temporary table to utilize it further. The performance of this method is also better than the other two methods also.
Given below is the script.

Set statistics time on
declare @PageSize float
select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'

Select object_Name(i.object_id) as [name]
,p.rows
,Convert(varchar(50),@PageSize * SUM(total_pages)) + ' KB' as [reserved]
,Convert(varchar(50),@PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) + ' KB' as [data]
,Convert(varchar(50),@PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) +  ' KB' as [index_size]
,Convert(varchar(50),@PageSize * SUM(total_pages-used_pages)) +  ' KB' as [unused]
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
JOIN sys.tables t ON i.object_id=t.object_id
Where i.type<=1 and a.type=1
and
t.type='U' and is_ms_shipped=0
Group By i.object_id,p.rows
GO
Set statistics time off

Given below is the server execution times.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.

Conclusion :
You can see that all the above methods produce the same result set but the issue is with the performance. I recommend Method 3 because it will take less effort and time to generate the same result set.

Any comments ?

Read Full Post »

SQL Server – Dont’s for Offset Rows And Fetch Next

Posted in SQL SERVER, Tips, tagged , , , , , , on February 24, 2013| Leave a Comment »

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 »

SQL Server – Clear Find and Replace window history

Posted in SQL SERVER, Tips, tagged , , , on February 21, 2013| 1 Comment »

In Find and Replace window, sometimes you find a long history (normally it keeps 20 items at a time) in it and by mistake you may replace with some incorrect word. So I was looking for the solution to clear the Find & Replace window history.

Note : This solution is not recommended for live servers, you can use it in your development and testing machines.

Lets proceed step by step :
Step 1:
First lets go to our Find and Replace window and view the history records. Given below is the Find and Replace window history.

find_and_replace1.1

find_and_replace1.2

Step 2:
Close the SSMS and go to Start >> Run >> type Regedit

Step 3:
Now you need to locate the given below location in registry, where SQL Server keeps its Find and Replace history but it varies with different versions of SQL Server.

For SQL Server 2005 :
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\Find

For SQL Server 2008 :
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\Find

For SQL Server 2012 :
HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\Find

Step 4:
Here, you can find keys from Find till Find19 and Replace till Replace19. Now you need to delete these keys to remove it from Find and Replace window.

find_and_replace1.3

find_and_replace1.4

Step 5:
Now that you deleted the keys, lets open SSMS and go to view Find and Replace window history. It is cleared now.
find_and_replace1.5

Read Full Post »

SQL SERVER 2012 – AUTO RECOVERY SCRIPTS

Posted in SQL SERVER, Tips, tagged , , , on February 18, 2013| 3 Comments »

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 »

SQL SERVER – Trace Query in SQL Server Profiler

Posted in Functions, SQL SERVER, Tips, tagged , , , on February 14, 2013| 2 Comments »

SQL Server profiler is a very handy tool when it comes to tracing queries and trouble shooting etc. But if you run the sql server profiler with default settings, it will show you all the tracing going on in the SQL Server.Then you need to further filter it to achieve whatever you are exactly looking for with different filters.
Today, I will share a shortcut that can help you to trace query that you are doing with your session. Because mostly SQL developers / QA are interested to debug their own activities (in their own sessions) via SQL Server Profiler.
Note : This feature is available in SQL SERVER 2008 and above.

Lets do it step by step :

Step 1 :
Open a new query window and right click on it.

Step 2 :
Click on the Trace Query in SQL Server Profiler or press Ctrl+Alt+P.

sql server profiler1.1

Step 3 :
It will open SQL Server Profiler.

Step 4 :
Now, whatever you will perform in this query window it will show you in this profiler. But if you open a new query window it will not trace it in this profiler. This is very handy for short term tracing or trouble shooting.

sql server profiler1.2

Reason
The reason behind this is if you open the property window (File menu \ properties …) of this sql server query profiler, and go to events selection and the click on column filters button, you can see that it automatically filters your sessions. So whatever you do in your session only that will be traced.

sql server profiler1.3

sql server profiler1.4

Will post how filters work in SQL Server profiler in my upcoming post.

Read Full Post »

SQL Server 2012 – Cycle Clipboard Ring

Posted in SQL SERVER, Tips, tagged , , , on February 9, 2013| 1 Comment »

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 »

« Newer Posts - Older Posts »