SQL Server Portal

SQL SERVER 2012 – Breaking Changes – sys.dm_os_memory_cache_counters

April 22, 2013 by Muhammad Imran

sys.dm_os_memory_cache_counters is one of the dynmaic management views shipped with SQL Server 2005. The major benefits of this dynamic management view is to provide the list of summary stating how cache is allocated against each request.

Given below is a simple query that will give the details about cache.

--This query is compatibale with SQL Server 2005 and 2008 only.
Select [name],[type],SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb]
from  sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

A few days ago I tried using the same query in SQL Server 2012 to check out the summary of cache but the above query did not work. Given below is the error detail.

Select [name],[type]
,SUM([single_pages_kb]) As [single_pages_kb]
,SUM([multi_pages_kb]) As [multi_pages_kb]
from  sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

Msg 207, Level 16, State 1, Line 2
Invalid column name ‘single_pages_kb’.
Msg 207, Level 16, State 1, Line 3
Invalid column name ‘multi_pages_kb’.

Oooops……………
It generated an error. The first impression I got was that these two fields have been eliminated from sys.dm_os_memory_cache_counters in SQL Server 2012 ?

But the answer is No, these field are not eliminated but renamed. Given below are the details.

S.No

Previous Column Name

New Column Name

single_pages_kb

pages_kb

multi_pages_kb

pages_in_use_kb

Lets rename the column in the above query and execute it again in SQL Server 2012.

--This query is compatible with SQL Server 2012 and above.
Select [name],[type]
,SUM([pages_kb]) As [pages_kb]
,SUM([pages_in_use_kb]) As [pages_in_use_kb]
from sys.dm_os_memory_cache_counters
Group By [name],[type]
Order By [name], [type]
--OUTPUT

Conclusion :
In SQL Server 2012, sys.dm_os_memory_cache_counters has renamed the two columns single_pages_kb, multi_pages_kb to pages_kb, pages_in_use_kb respectively. Remember to implement this renamed column change effects to avoid such errors.

Reference : MSDN

Posted in Dynamic Management Views, SQL SERVER | Tagged raresql, SQL, SQL Server, SQL SERVER 2012 - Breaking Changes - sys.dm_os_memory_cache_counters, sys.dm_os_memory_cache_counters | Leave a Comment »

SQL SERVER – How to convert float to varchar

April 21, 2013 by Muhammad Imran

Sometimes, you feel very handicapped when you need to convert some complex data types but due to compatibility, the built-in conversion functions such as Cast, Convert, Try_Cast, Try_Convert cannot aid conversion.

How to convert float to varchar is indeed an old topic. In this article, we will solve this issue via STR function and FORMAT function introduced in SQL Server 2012.

Given below are the two methods to convert float to varchar.

Method 1:
In this method, we will use STR function to convert float to varchar and thereafter LTRIM to trim the extra spaces left in it.

--This script is compatible with SQL Server 2005 and above.
Declare @varchar as varchar(50)
Declare @float as float

Set @float =1234567.12345678
Select @float as [Float]

Set @varchar =LTRIM(STR(@float,50,8))
Select @varchar as [Float converted to varchar]
--OUTPUT

Method 2:
In the above method, you can notice that we used two different methods (STR, LTRIM) to convert float to varchar and to trim extra spaces. In this method we will use ONLY FORMAT function to convert float to varchar.

--This script is compatible with SQL Server 2012 and above.
Declare @varchar as varchar(50)
Declare @float as float

Set @float =1234567.12345678
Select @float as [Float]

Set @varchar =Format(@float,'#.#############')
Select @varchar as [Float converted to varchar]
--OUTPUT

Posted in Solutions, SQL SERVER | Tagged raresql, SQL, SQL Server, SQL SERVER - How to convert float to varchar | 2 Comments »

SQL SERVER – How to find SQL Server port number

April 20, 2013 by Muhammad Imran

“How to find the SQL Server port number ?” has been an old topic being discussed in multiple forums. The common solution provided is using xp_instance_regread. It is one of the useful undocumented stored procedures when you need to read registry settings. In this article, I will give you two solutions. First one using xp_instance_regread and second, using sys.dm_server_registry, a new dynamic management view shipped in SQL Server 2008 R2

Method 1 : Using xp_instance_regread.
In this method we will use xp_instance_regread to find the port number.

--This script will work on SQL Server 2005 and above
DECLARE @HkeyLocal nvarchar(18)
Declare @Instance varchar(100)
DECLARE @MSSqlServerRegPath nvarchar(200)
DECLARE @PortNumber nvarchar(100)

SET @Instance ='MSSQL11.MSSQLSERVER'
SET @HkeyLocal=N'HKEY_LOCAL_MACHINE'
SET @MSSqlServerRegPath=N'SOFTWARE\Microsoft\\Microsoft SQL Server\'
+ @Instance + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'

Print @MSSqlServerRegPath
EXEC xp_instance_regread @HkeyLocal
, @MSSqlServerRegPath
, N'TcpPort'
, @PortNumber OUTPUT
SELECT @PortNumber as [Port Number]
--OUTPUT

Port Number
————
1433

(1 row(s) affected)

Method 2 : Using using sys.dm_server_registry.
In this method we will use sys.dm_server_registry to find the port number.

--This script will work on SQL Server 2012 and above
Use master
GO
Select value_data as [Port Number]
From sys.dm_server_registry
Where registry_key like '%IPALL%' and
value_name ='TcpPort'

Port Number
————
1433

(1 row(s) affected)

Conclusion :
If you compare both methods, you will feel the difference, because the code is much optimized in the second method. Also, you can utilize the result set of second method further without using any temporary table but on the other hand you need temporary table to utilize the result set further.

Posted in Solutions, SQL SERVER | Tagged raresql, SQL, SQL Server, SQL SERVER - How to find SQL Server port number, sys.dm_server_registry, xp_instance_regread | Leave a Comment »

SQL SERVER – Get day of week in SQL 2005/2008/2012

April 18, 2013 by Muhammad Imran

How to get the day of the week ? I have been asked this question several 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 day of a week in SQL Server 2005 & SQL Server 2012 respectively.
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(dw , @Datetime) as [Weekday Name]
--OR
Select DATENAME(weekday, @Datetime) as [Weekday Name]
--OUTPUT

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,'dddd') as [Weekday Name]
--OUTPUT

Have you come across any other method ? Do share.

Posted in Solutions, SQL SERVER | Tagged Datename Function, Day of the Week, Format Function, raresql, SQL, SQL Server, SQL Server 2012, week day name | Leave a Comment »

SQL SERVER 2012 – Fix – Error :11514 – The metadata could not be determined because statement in procedure contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

April 17, 2013 by Muhammad Imran

Sometimes, its essential to use dynamic SQL when there are different queries for different scenarios, to build the query dynamically. The error we will discuss in this article is related to dynamic SQL and sp_describe_first_result_set , a new system stored procedure shipped with SQL Server 2012. This error message is available in SQL Server 2012 and above.

Let’s discuss this in detail:
Message Number: 11514

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ in procedure ‘%.*ls’ contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Error Generation:
Let me create a sample to demonstrate this error.

Use tempdb
Go
Create Procedure UDP_Resultset
As
Declare @FirstLetter varchar(3)
Declare @SecondLetter varchar(3)

Set @FirstLetter ='''A'''
Set @SecondLetter ='''B'''

Declare @String varchar(Max)

Set @String ='Select ' + @FirstLetter +' As [First Letter]'
EXEC (@String)

Set @String ='Select ' + @SecondLetter +' As [Second Letter]'
EXEC (@String)
GO
EXEC UDP_Resultset
--OUTPUT

You can see that the above script executed successfully. Now I would like to view the metadata of the above stored procedure. Given below is the script to view the metadata using sp_describe_first_result_set

EXEC Sp_describe_first_result_set @tsql =N'EXEC UDP_Resultset'
--OUTPUT

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘EXEC (@String)’ in procedure ‘UDP_Resultset’ contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Ooopps…… I am unable to execute it.

Resolution:
The reason behind this error is, we have dynamic SQL inside the SQL stored procedure and whenever we need to view the metadata of stored procedure having dynamic SQL, we must use the result set clause along with stored procedure.
Given below is the correct script.

Sp_describe_first_result_set @tsql =
N'EXEC UDP_Resultset WITH RESULT SETS (([1st Letter] varchar(50))
,([2nd Letter] varchar(50)));'
GO
--OUTPUT

Conclusion :
Remember, whenever you use dynamic SQL stored procedure and would like to view the metadata of that stored procedure using sp_describe_first_result_set, always use result set clause.

Posted in Errors, SQL SERVER | Tagged Msg 11514 Level 16 State 1, raresql, SQL, SQL Server, SQL SERVER 2012 - Fix - Error :11514 - The metadata could not be determined because statement in procedure contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the r | 1 Comment »

SQL SERVER 2012 – FORCESEEK Table Hint – Enhancement

April 16, 2013 by Muhammad Imran

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

Posted in SQL SERVER, Tips | Tagged raresql, SQL, SQL Query, SQL Server, SQL SERVER 2012 - FORCESEEK Table Hint - Enhancement | 2 Comments »

SQL SERVER – Undocumented function – fn_dblog

April 15, 2013 by Muhammad Imran

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

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(('&lt;a&gt;0x'+replace(@String,@delimiter,'&lt;/a&gt;&lt;a&gt;0x')+'&lt;/a&gt;')
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(&quot;[Column]&quot;),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

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.

Posted in Functions, SQL SERVER, Tips | Tagged raresql, SQL, SQL Server, SQL SERVER - Undocumented function - fn_dblog | 10 Comments »

SQL SERVER – How to find uncontained objects in the database

April 14, 2013 by Muhammad Imran

Today, I was working on contained database and came across with an issue (will post this issue in my upcoming post), where I had to find a list of uncontained objects (As per MSDN, uncontained objects are basically those objects that cross the database boundaries in a contained database.)

Also, as per MSDN, given below are the objects that usually cross the boundaries of the database:

Unknown containment behavior (dynamic SQL or deferred name resolution)
DBCC command
System stored procedure
System scalar function
System table valued function
System built-in function

But it is quiet difficult, if you search for any of the above objects in the database one by one. SQL Server resolves this issue by releasing a dynamic management view (sys.dm_db_uncontained_entities) that will give you all uncontained objects of any database, in one shot.

Let me create an uncontained objects in a contained database.

--Create a stored procdure by using sys.allobjects
USE AdventureWorks2012
GO
CREATE PROC test_Procedure
AS
SELECT * FROM sys.all_objects
GO

Once you created the above stored procedure, just execute the given below script it will give you the name of above procedure along with other uncontained objects.

Use AdventureWorks2012
GO
SELECT O.name
,O.type_desc
,UCE.class_desc
,UCE.statement_type
,UCE.feature_name
,UCE.feature_type_name
FROM sys.dm_db_uncontained_entities AS UCE
LEFT JOIN sys.objects AS O
ON UCE.major_id = O.object_id
--OUTPUT

Note : If the above query return Nil(or emplty) it means that you donot have any uncontained objects.

Posted in Solutions, SQL SERVER | Tagged raresql, SQL, SQL Server, SQL SERVER - How to find uncontained objects in the database, sys.dm_db_uncontained_entities | Leave a Comment »

SQL SERVER – Get the first letter of each word in a String (Column)

April 12, 2013 by Muhammad Imran

String manipulation in a different format is one of my favorite activities. Today, I came across a question in a forum, how to get the first letter of each word in a column. Obviously, my primary search was the internet, where I found few solutions, but all of them were loop based. Hence developed a solution without loop.

Give below is the script :

--Create a Sample
Create Table Student
(
[Student ID] int Identity(1,1),
[Student Name] varchar(50)
)
Go
Insert into Student Values ('Steve Masters Bob')
Insert into Student Values ('David Ortiz')
Insert into Student Values ('Michael Sean Ray')
Insert into Student Values ('Steven SElikoff')
Insert into Student Values ('Carole POLAND')
Insert into Student Values ('Bjorn Rettig')
Insert into Student Values ('Michiko OSAda')
Insert into Student Values ('CarOL Philips')
Insert into Student Values ('Merav Netz')
GO

--Create the procedure to get the first letter of each word.
Create Function dbo.[UDF_FIRST_LETTER_FROM_WORD]
(
@String Varchar(Max) -- Variable for string
)
RETURNS Varchar(Max)
BEGIN
Declare @Xml Xml
Declare @firstletter Varchar(Max)
Declare @delimiter Varchar(5)

SET @delimiter=' '
SET @Xml = cast(('<a>'+replace(@String,@delimiter,'</a><a>')+'</a>') AS XML)

;With CTE AS (SELECT A.value('.', 'varchar(max)') as [Column]
FROM @Xml.nodes('a') AS FN(a) )
SELECT @firstletter =Stuff((SELECT '' + LEFT([Column],1)
FROM CTE
FOR XML PATH('') ),1,0,'')

RETURN (@firstletter)
END
GO

Example 1 :
Given below script will get the first letter of each word from a column of a table.

SELECT [Student Name],
dbo.[UDF_FIRST_LETTER_FROM_WORD] ([Student Name]) as [First Letters]
FROM Student
GO

Example 2 :
Given below script will get the first letter of each word from a string.

Declare @String as varchar(100)
Set @String ='My Best Friend'
SELECT @String as [String]
, dbo.[UDF_FIRST_LETTER_FROM_WORD] (@String) as [First Letters]

Posted in Solutions, SQL SERVER | Tagged raresql, SQL, SQL Server, SQL SERVER - Get the first letter of each word in a String (Column) | 6 Comments »

SQL SERVER 2012 – Fix – Error : 35343 – CREATE INDEX statement failed. Column has a data type that cannot participate in a columnstore index. Omit column.

April 11, 2013 by Muhammad Imran

Today when I was working on Columnstore index and I came across this error, CREATE INDEX statement failed. Column ‘%.*ls’has a data type that cannot participate in a columnstore index. Omit column ‘%.*ls’. This is one of the new error messages come in SQL Server 2012. This error message is related to Columnstore index a new database feature shipped with SQL Server 2012.

Let me explain this with a simple example.
Message Number: 35343

Severity : 15

Error Message: CREATE INDEX statement failed. Column ‘%.*ls’has a data type that cannot participate in a columnstore index. Omit column ‘%.*ls’.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error:

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

Msg 35343, Level 16, State 1, Line 1
CREATE INDEX statement failed. Column ‘rowguid’has a data type that cannot participate in a columnstore index. Omit column ‘rowguid’.

Ooopps…… I am unable to create columnstore index.

Resolution:
To resolve this error, I need to find the datatypes which are not supported by Columnstore index . As per MSDN, given below are the datatypes .

binary and varbinary
ntext , text, and image
varchar(max) and nvarchar(max)
uniqueidentifier
rowversion (and timestamp)
sql_variant
decimal (and numeric) with precision greater than 18 digits
datetimeoffset with scale greater than 2
CLR types (hierarchyid and spatial types)
xml

Now, just change your script (to create the columnstore index) and remove all non supported datatype. In this example, I will remove [Rowguid] column because its datatype is uniqueidentifier.

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

Command(s) completed successfully.

Conclusion :

Remember, whenever you create the columnstore index, you must not include any column which has datatype that is not supported by columnstore index.

Posted in Errors, SQL SERVER | Tagged CREATE INDEX statement failed. Column has a data type that cannot participate in a columnstore index. Omit column, Msg 35343 Level 16 State 1, raresql, SQL, SQL Server, SQL Server 2012 | Leave a Comment »

« Newer Posts - Older Posts »

SQL Server Portal

Real world SQL scenarios & its unique & optimized solutions

SQL SERVER 2012 – Breaking Changes – sys.dm_os_memory_cache_counters

SQL SERVER – How to convert float to varchar

SQL SERVER – How to find SQL Server port number

SQL SERVER – Get day of week in SQL 2005/2008/2012

SQL SERVER 2012 – Fix – Error :11514 – The metadata could not be determined because statement in procedure contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

SQL SERVER 2012 – FORCESEEK Table Hint – Enhancement

SQL SERVER – Undocumented function – fn_dblog

SQL SERVER – How to find uncontained objects in the database

SQL SERVER – Get the first letter of each word in a String (Column)

SQL SERVER 2012 – Fix – Error : 35343 – CREATE INDEX statement failed. Column has a data type that cannot participate in a columnstore index. Omit column.

Recent Posts

Archives

Categories

Blog Stats

Follow Blog via Email

Find us on Facebook

Disclaimer