Feeds:
Posts
Comments

Archive for April, 2013

“How to implement LIMIT in SQL Server?” (How to pick up certain rows after certain rows in SQL SERVER), I came across this question many times in multiple forums and the common solution is, to use common table expression and ROW_NUMBER. However, this is an old approach. A new approach has been introduced in SQL Server 2012 using OFFSET ROWS FETCH NEXT ROWS.

Let me create a sample script to explain both approaches.

USE AdventureWorks2012;
GO
Select
FirstName
,Jobtitle
From [HumanResources].[vEmployee]
Order By FirstName,Jobtitle
--OUTPUT

limitrows1.1

Lets discuss both approaches with examples :

Old Approach:
In this approach, we will pick 10 rows after row number 10 (meaning row number 11 till 20) from [HumanResources].[vEmployee]. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012;
GO
;With CTE_Employee_List As
(
    Select ROW_NUMBER() Over
   (Order By[FirstName],[JobTitle]) As [Row Number],
	FirstName
	,Jobtitle
    From [HumanResources].[vEmployee]
)
Select FirstName,Jobtitle
From CTE_Employee_List
Where [Row Number] Between 11 And 20;
GO
--OUTPUT

limitrows1.2-1

New Approach:
In the new approach too, we will select 10 rows after row number 10 (meaning row number 11 till 20) from [HumanResources].[vEmployee] and the script goes as follows :

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012;
GO
Select
FirstName
,Jobtitle
From [HumanResources].[vEmployee]
Order By FirstName,Jobtitle
Offset 10 Rows
Fetch Next 50 Rows Only;
--OUTPUT

limitrows1.4

As we can see, the output is exactly same but a lot of other parameters are different that can impact the performance.
Given below is the summary of both approaches.

S.No

Type

Old Approach

New Approach

1

Common Table Expression

Yes (required)

N/A

2

ROW_NUMBER

Yes (required)

N/A

3

CPU time

15 ms

15 ms

4

Elapsed time

1387 ms

375 ms

5

Query Cost (relative to the batch)

53%

47%

Conclusion :
If you are using SQL Server 2012, I would recommend to use the new approach. It not only boosts the performance but reduce the line of code as well.

Read Full Post »

sp_MsForEachTable is one of my favorite undocumented stored procedures. Even though it is undocumented (support not available) it is very handy stored procedure. I remember giving one script using sp_MsForEachTable to my team to built all indexes via a single line of code. Given below is the script.

--Given below script is not compatible with Columnstore Index
USE AdventureWorks2012;
GO
EXEC sp_MSforeachtable @command1="print '?'"
, @command2="SET QUOTED_IDENTIFIER ON;ALTER INDEX ALL ON ?
REBUILD WITH (FILLFACTOR=80)"

But this script broke down today while running on SQL Server 2012 with an error.

Let’s discuss this error in detail:
Message Number: 35327

Severity : 16

Error Message: ALTER INDEX REBUILD statement failed because specifying FILLFACTOR is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying FILLFACTOR.

Error Generation:
Let me create a columnstore index on a table to demonstrate this error.

USE AdventureWorks2012;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX
[IX_SalesOrderDetail_ColumnStore]
ON [Sales].[SalesOrderDetail]
([ProductID],
[OrderQty],
[UnitPrice]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

Once columnstore index is created, lets execute the given below script.

USE AdventureWorks2012;
GO
EXEC sp_MSforeachtable @command1="print '?'"
, @command2="SET QUOTED_IDENTIFIER ON;ALTER INDEX ALL ON ?
REBUILD WITH (FILLFACTOR=80)"
--OUTPUT

Msg 35327, Level 16, State 1, Line 1
ALTER INDEX REBUILD statement failed because specifying FILLFACTOR is
not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying FILLFACTOR.

errormsg35327.1.1

Ooopps…… I am unable to execute it properly.

Resolution:

The reason behind this error is that once the above script tried to build columnstore index using fill factor, it generated error because it cannot build columnstore index using fill factor.

Two different methods to fix this error are :

  • Using Cursor
  • Using sp_MSforeachtable

Using Cursor

This method is recommended because there is no undocumented command involved in this solution and it uses cursor to build each index. Here is the solution.

Using sp_MSforeachtable

This method uses shortest possible solution. I modified above script and now it is compatible with columnstore index. It can build columnstore index without fill factor but the rest of the indexes will be filled with fill factor. Given below is the script.

USE AdventureWorks2012;
GO
EXECUTE sp_msForEachTable ' Print ''?'';SET QUOTED_IDENTIFIER ON
;IF EXISTS (Select * from sys.indexes
Where object_id = object_id(''?'') And type=6)
ALTER INDEX ALL ON ? REBUILD ELSE
ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=80)'
--OUTPUT

errormsg35327.1.2

Conclusion :
Remember, whenever you build columnstore index either all indexes together or discretely, never build columnstore index with fill factor.

Read Full Post »

In my previous article, I wrote about  How to search a string value in all columns in the table and in all tables in a single database. Today we will search a string value in all columns in the table and in all tables in all databases.

Lets proceed step by step :

Step 1:
Prerequisite of Step 1 & Step 2 from previous article.

Step 2 :
Create the given below stored procedure to search the string in all columns in the table and in all tables in all databases.

CREATE PROCEDURE Find_Record_Across_Databases_Proc
@string VARCHAR(Max)
AS

DECLARE @sqlString varchar(Max)
DECLARE @Database_Name sysname

--Declare Cursor
DECLARE Database_Cursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT [name] FROM sys.databases
WHERE NAME NOT IN ('master','tempdb','model','msdb') ORDER BY NAME

OPEN Database_Cursor

FETCH NEXT FROM Database_Cursor INTO @Database_Name
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC UDP_Find_Record_Across_Tables
@Database_Name, NULL, NULL ,@string

FETCH NEXT FROM Database_Cursor INTO @Database_Name
END

CLOSE Database_Cursor
DEALLOCATE Database_Cursor

SELECT * FROM tempdb.dbo.result
GO

Example:
Once you created the above stored procedure. After that lets execute it to search a string in all columns of all tables and in all databases. Given below is a sample.

EXEC Find_Record_Across_Databases_Proc 'senior'

searchtables1.5

Read Full Post »

I really appreciate active participation of my blog readers. Yesterday, I received an inquiry from ‘Abhilash’ one of my blog readers, how to search a string value in all columns in the table and in all tables in a database.

Given below is the solution that will search a string value in all columns in the table and in all tables in a database.

Lets proceed it step by step.
Step 1:
Create given below table in tempdb database to consolidate all search result.

Use tempdb
GO
Create Table Result
(
[Sno] int identity(1,1),
[Database Name] sysname,
[Schema Name] sysname,
[Table Name] sysname,
[Column Name] sysname,
[Record Name] varchar(Max)
)

Step 2:
Create the given below stored procedure to search the string in all columns in the table and in all tables in a database.

--Create the store procedure to search a string across the tables.
Use AdventureWorks2012
GO
Create PROCEDURE Find_Record_Across_Tables_Proc
@Database sysname,
@Schema sysname,
@Table sysname,
@String VARCHAR(Max)

AS

DECLARE @SqlString varchar(Max)
DECLARE @Table_Schema sysname
DECLARE @Table_Name sysname
DECLARE @Column_Name sysname

--Declare Cursor
SET @SqlString = 'DECLARE String_cursor CURSOR FOR
Select TABLE_SCHEMA, TABLE_NAME ,COLUMN_NAME from
' + @Database +'.INFORMATION_SCHEMA.COLUMNS
Where DATA_TYPE IN (''text'',''ntext'',''varchar''
,''nvarchar'',''char'',''nchar'')'

--Filter schema name
IF @schema IS NOT NULL
Begin
SET @SqlString = @SqlString + ' And TABLE_SCHEMA=''' + @Schema + ''''
End
--Filter table name
IF @table IS NOT NULL
Begin
SET @SqlString = @SqlString + ' And TABLE_NAME=''' + @table + ''''
End

Print @SqlString
EXEC (@SqlString)

OPEN String_cursor

FETCH NEXT FROM String_cursor
INTO @Table_Schema, @Table_Name, @Column_Name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlString = 'IF EXISTS(SELECT ' + QUOTENAME(@Column_Name)
+ ' FROM ' + @Database + '.' + QUOTENAME(@Table_Schema)
+ '.' + QUOTENAME(@Table_Name)
+ ' WHERE ' + QUOTENAME(@Column_Name)
+ ' Like ''%' + @string + '%'')
Insert into tempdb.dbo.result
([Database Name],[Schema Name]
,[Table Name],[Column Name],[Record Name])
SELECT ''' + QUOTENAME(@Database) + ''','''
+ QUOTENAME(@Table_Schema) + ''','''
+ QUOTENAME(@Table_Name) + ''',''''
+ ''' + QUOTENAME(@Column_Name)
+ ''',' + QUOTENAME(@Column_Name)
+ ' FROM ' + @Database + '.'
+ QUOTENAME(@Table_Schema)
+ '.' + QUOTENAME(@Table_Name)
+ ' WHERE ' + QUOTENAME(@Column_Name)
+ ' Like ''%' + @string + '%'''
Print @SqlString
EXEC (@SqlString)

FETCH NEXT FROM String_cursor
INTO @Table_Schema, @Table_Name, @Column_Name

END
CLOSE String_cursor
DEALLOCATE String_cursor
GO

Step 3:
In the step 3, you can execute the above stored procedure with different parameters.
Given below are few examples of above stored procedure.

Example 1:
To search a string in all columns of all tables in a single database.

Use AdventureWorks2012
GO
Delete from tempdb.dbo.result
GO
EXEC Find_Record_Across_Tables_Proc
'AdventureWorks2012', NULL, NULL ,'Senior'
GO
Select * from tempdb.dbo.result
GO

search_string_in_all_tables1.0

Example 2:
To search a string in all columns of a single tables and in a single database.

Use AdventureWorks2012
GO
Delete from tempdb.dbo.result
GO
EXEC Find_Record_Across_Tables_Proc
'AdventureWorks2012', NULL, 'Employee' ,'Senior'
GO
Select * from tempdb.dbo.result
GO

search_string_in_all_tables1.2

Example 3:
To search a string in all columns of a single schema in a single table and in a single database.

Use AdventureWorks2012
GO
Delete from tempdb.dbo.result
GO
EXEC Find_Record_Across_Tables_Proc
'AdventureWorks2012', 'HumanResources', 'Employee' ,'Senior'
GO
Select * from tempdb.dbo.result
GO

search_string_in_all_tables1.1

Read Full Post »

If you are planning to convert varchar to float you should know that these two data types are not compatible with each other. In the earlier versions of SQL Server you had to use CASE, ISNUMERIC & CONVERT to convert varchar to float but in SQL Server 2012, you can do it with just one function TRY_CONVERT. Let me create a sample to explain it.

Use tempdb
GO
Create Table tbl_test
(
[Numbers] varchar(50)
)
GO
Insert into tbl_test values(12345.6789)
Insert into tbl_test values('AB12345')
Insert into tbl_test values(12.1)
Insert into tbl_test values(11.2345678)
GO

Given below are two different methods to convert varchar to float but these two methods are compatible with different versions of SQL Server.
Method 1 (For earlier versions of SQL Server):

--This script is compatible with SQL Server 2005 and above.
Use tempdb
GO
Select [Numbers],
(Case When Isnumeric([Numbers])=1 Then
Convert(float,[Numbers]) else NULL end) As [Varchar to Float]
from tbl_test
--OUTPUT

varchartofloat1.1
Method 2 (For SQL Server 2012):

--This script is compatible with SQL Server 2012 and above.
Use tempdb
GO
Select Try_convert(float,[Numbers]) as [Varchar to float]
from tbl_test
--OUTPUT

varchartofloat1.2 Cleaning :

use tempdb
GO
drop table tbl_test

Read Full Post »

How to get the Month Name from Date ? Have been questioned numerous times and we do have its solution via DATENAME function. However, today we will achieve the same solution using FORMAT function, introduced in SQL Server 2012 as well. Given below are the two methods that help in identifying the Month Name from Date in SQL Server 2005 & above as well as SQL Server 2012 & above.

Method 1 :
Script supported in SQL Server 2005 and above :

 --This script will work in SQL Server 2005 and above
Declare @Datetime as datetime
Set @Datetime =getdate()

SELECT DATENAME(month, @Datetime) AS [Month Name]
--OR
SELECT DATENAME(mm, @Datetime) AS [Month Name]
--OR
SELECT DATENAME(m, @Datetime) AS [Month Name]
--OUTPUT

get month name from date1.1

Method 2 :
Script supported in SQL Server 2012 and above :

 --This script will work in SQL Server 2012 and above
Declare @Datetime as datetime
Set @Datetime =getdate()
Select FORMAT(@Datetime,'MMMM') AS [Month Name]
--OUTPUT

Get month name from date-1
Have you come across any other method ? Do share.

Read Full Post »

A few days ago I was working on indexes on a development server.  Accidentally I dropped few indexes. Fortunately, since I normally take the database backup, I restored the backup and it recovered all indexes perfectly. What if I didn’t have the backup and I did it on production server, it would be just  BOOOOOM ;). However, I looked for a solution on the internet how to recover the dropped index without backup. But could not get the proper solution. So finally I decided to develop a solution which I would be sharing with you.

Note : It is recommended to take database backup on regular basis and recover any disaster from backup.

Lets create & drop one nonclustered index on a table to demonstrate it.

USE AdventureWorks2012
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_Sample]
ON [Purchasing].[PurchaseOrderDetail]
(
[OrderQty],
[ReceivedQty],
[RejectedQty],
[ProductID]
)
GO
DROP INDEX [IX_PurchaseOrderDetail_Sample]
ON [Purchasing].[PurchaseOrderDetail]

Lets recover it step by step :

Step 1:
In this step, you need to create the given below function in the database from where you need to do the recovery.

USE AdventureWorks2012
GO
CREATE FUNCTION [dbo].[UDF_Get_Index_Column_Name]
(@Object_id bigint,@Column_id bigint)
RETURNS varchar(Max)
AS
Begin
          Declare @Column_Name as varchar(Max)
	  Select @Column_Name=[name] from sys.columns
          where object_id =@Object_id And column_id =@Column_id
	  Return @Column_Name
End
GO

Step 2:
In this step, you need to create the given below stored procedure in the database from where you need to do the recovery.

CREATE PROCEDURE Recover_Dropped_Index_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS

;With CTE AS (Select
Row_number() Over(Partition By A.[Transaction ID]
Order By [Transaction ID]) as [SNO]
,A.[Transaction ID]
,'CREATE ' +
(Case When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =0
Then 'Heap'
When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =1
Then 'Clustered'
When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =2
Then 'Nonclustered'
When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =3
Then 'XML'
When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =4
Then 'Spatial'
When Convert(int,Substring(A.[RowLog Contents 0],22,1)) =6
Then 'Nonclustered columnstore'
end)
+ ' INDEX ' + Quotename(
Replace(Substring(
A.[RowLog Contents 0],47,LEN(A.[RowLog Contents 0])),0x00,0x))
+' ON ' + Sch.name +'.'
+ Object_Name(
Convert(varbinary,Reverse(Substring(A.[RowLog Contents 0],5,4))))
as [Command1]

, [dbo].[UDF_Get_Index_Column_Name] (
  Convert(bigint,
  Convert(varbinary,Reverse(Substring(A.[RowLog Contents 0],5,4))))
, Convert(bigint
, Convert(varbinary,Substring(A.[RowLog Contents 0],20,2))))
  as [Column Name]

, Convert(int,Substring(A.[RowLog Contents 0],22,1)) as [Type]

, SUM(1) OVER(Partition By A.[Transaction ID]) as [Total Rows]

From sys.fn_dblog(NULL, NULL) As A
Inner Join sys.tables As tbl
On Object_Name(Convert
(varbinary,Reverse(Substring(A.[RowLog Contents 0],5,4))))=tbl.name
Inner Join sys.schemas As Sch On tbl.schema_id =sch.schema_id
Where A.AllocUnitName In ('sys.sysiscols.clst','sys.sysidxstats.clst')
And Context In ('LCX_MARK_AS_GHOST', 'LCX_HEAP')
And Operation in ('LOP_DELETE_ROWS')
And A.[TRANSACTION ID] In (SELECT DISTINCT [TRANSACTION ID]
FROM  sys.fn_dblog(NULL, NULL)
Where Context IN ('LCX_NULL') And Operation In ('LOP_BEGIN_XACT')
And [Transaction Name] Like '%DROP INDEX%'
And  CONVERT(NVARCHAR(11),[Begin Time]) Between @Date_From And @Date_To)
)
Select (Case When [type]=0 And [SNo] =2 And [SNO]<>[Total rows]
Then '(' + [Column Name] +','
When [type]=0 And [SNo] =2 And [SNO]=[Total rows]
Then '(' + [Column Name] +')'
When [type]=0 And [SNo] >2 And [SNO]<>[Total rows]
Then  [Column Name] + ','
When [type]=0 And [SNo] >2 And [SNO]=[Total rows]
Then  [Column Name] + ')'
else Command1 end) AS [Text] from CTE
GO

Step 3 :
In this step, you need to execute the above stored procedure to recover the dropped index. Given below is the syntax.
You must supply the correct recovery dates to the stored procedure to recover proper records.

USE AdventureWorks2012
GO
EXEC [Recover_Dropped_Index_Proc] '2013/04/01','9999/12/31'
--OUTPUT

recover_dropped_index1.1

Step 4 :
Just copy the above result set and paste in the query window and execute it using correct database and you will get your indexes back in your database.

Read Full Post »

Older Posts »