sp_describe_first_result_set , To get metadata of the query

Sometime application developer are not completely aware of the result set provided by the query.
They have to run the query to see result data type or refer table structure to decide data type and other related info,now there is system store proc which will give this basic and very useful info of the query

Sequence Part 1:- New Feature in Denali

Sequences are new feature in SQL Server Denali
Those who are aware with Oracle/Postgres are aware with sequence. However better late than never :-)
MS has added sequence in Deanli

One more way to get primary key details of table

One more way to get primary key details of table

EXEC sp_pkeys

Execute with Result Sets new option in Denali

We all are aware of Execute. Most common use is execute a procedure.
In Denali a new option is added define Metadata for result set using WITH RESULT SETS options.
Lets look into it

TRY_CONVERT:- New function in Denali


Tries to convert data type to a specified data type and if fails returns Null


TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

data_type:- data_type in which we want to convert data
expression:- expression which will be converted to speicified data_type
style:-specified style, A integer number in which data will be converted. 
Its same as we specified in convert

THROW new function in Denali


It raise an exception and will transfer the code to CATCH block,while using TRY CATCH block
If not used in TRY CATCH then batch will be aborted.


THROW [ { error_number | @local_variable },
        { message | @local_variable },
    { state | @local_variable }
] [ ; ]

New DATETIME functions in Denali

New DateTime  Function in Denali

    This will return last day for the month from the date at specified offset
    EOMONTH ( start_date [, month_to_add ] )

Lag:-New function in sql server denali


We all have read about LEAD in previous blog . LAG is working opposite of LEAD where we can go backword in dataset.

We can get value from previous subsequent row with given offset value without using a self join or CTE

FIRST_VALUE:-New function in sql server denali


A new function SQL Server Denali to return first value from ordered set

    OVER ( < partition_by_clause > order_by_clause < rows_range_clause > )

Lead:-New function in sql server denali


We can get value from next subsequent row with given offset value without using a self join or CTE
We can use this function to compare  value with other row in the Table for analysis purpose

choose :- New function in sql server denali


Retruns value at specified index from list of values

IIF :- New function in sql server denali

We all are aware of IIF, but its not available in previous version of SQL Server
Now its available in  Denali.

Concat :- New function in sql server denali

SQL Server Denali has many new programming function
We will look at it one by one


Returns a string that is concaconcatenating  of two or more string values

Debug query in SSMS

As we all are aware with debugging and have used it while testing our queries or troubleshooting .  Now in  SQL 2008 its possible to debug our queries in SSMS.

SQL Server 2008 R2 Analysis Services operations guide

Microsoft has releases Analysis Service 2008 R2 operations guide. 
This can be quite useful to understand new feature and optimizations in 2008 R2 edition. 

Object Search in SSMS

As a  our database grows objects in the database also increases. During development we have to time to time get name of different objects  , for this we query system tables , but we can get this information from

Activity Monitor SQL Server

Activity Monitor

As a sql server user we all are aware of Activity monitor.
But in SQL Server 2008 its enhanced with more features

Script SQL Server Jobs

                As a part of backup routine or server migration we have to script jobs and then move them to other server. We all are used to SSIS for moving jobs from one server to other server
Another option is to

SQL Server 2008 R2 Developers Training Kit

For all those who are new to SQL Server BI or New to SQL Server 2008 R2 , Microsoft has launched  a training kit  SQL Server 2008 R2 Developers Training Kit. Its a great resource to used to SQL Server  2008 R2. It contains a rich set of presentations, demos, hands-on labs and videos that are perfect for self-paced learning or for conducting your own training.

We can download it from 


What's In the Training Kit
The training kit is divided into four sections:
  • Getting Started (for Web and BI developers who are new to SQL Server)
  • SQL Server 2008 (for experienced SQL Server developers who want to understand what's new in 2008)
  • SQL Server 2008 R2 (for experienced SQL Server developers who want to understand what's new in 2008 R2)
  • Office 2010 (for experienced BI developers who want to understand what's new in 2008 R2 and Office 2010)
Here is a breakdown of each section and its associated content:
  1. Getting Started
    Web Developers: 1 Hands-On Lab, 2 Videos
    BI Developers: 32 Presentations, 27 Demos, 12 Hands-On Labs, 59 Videos
  2. SQL Server 2008
    Overview: 1 Presentation, 1 Video
    Date and Time Data Types: 1 Presentation, 1 Demo
    Spatial Data Types: 1 Presentation, 2 Demos, 2 Hands-On Labs
    FILESTREAM Blob Storage: 1 Presentation, 2 Demos
    Transact-SQL Improvements: 1 Presentation, 4 Demos
    .NET CLR Integration: 1 Presentation, 2 Demos, 1 Hands-On Lab
    Reporting Services: 1 Presentation, 1 Demo
    AdventureWorks Racing All-Up SQL Server 2008 Demo: 1 Presentation, 1 Demo
  3. SQL Server 2008 R2
    Overview: 2 Presentations, 4 Hands-On Labs, 8 Videos
    Data-tier Application Framework: 3 Presentations, 5 Demos, 2 Hands-On Labs, 9 Videos
    StreamInsight: 4 Presentations, 7 Demos, 3 Hands-On Labs, 17 Videos
    Reporting Services: 7 Prsentations, 5 Demos, 5 Hands-On Labs, 12 Videos
  4. Office 2010
    Excel 2010 and PowerPivot: 4 Presentations, 4 Demos, 3 Hands-On Labs, 8 Videos
    SharePoint 2010, PerformancePoint Services and PowerPivot: 6 Presentations, 6 Demos, 3 Hands-On Labs, 12 Videos

How to monitor replication subscription errors

 One of my client has a big setup Server for SQL Server. He also has a lot of transactional replication and monitoring its is a really problem for him. Frequently he has problem of out of sync replications or failed replication

DMVs for Memory

Today we will look into DMVs related to memory

First we will look into sys.dm_os_sys_memory which will give us memory related basic informatio for the system
This will give us detail for physical memory, available memory , toatl page file and available page file and
high/low memory status. If  memory status is low then it means system is facing memory pressure.

SELECT total_physical_memory_kb / ( 1024.0 * 1024 )     total_physical_memory_gb,
       available_physical_memory_kb / ( 1024.0 * 1024 ) available_physical_memory_gb,
       total_page_file_kb / ( 1024.0 * 1024 )           total_page_file_gb,
       available_page_file_kb / ( 1024.0 * 1024 )       available_page_file_gb,
FROM   sys.dm_os_sys_memory

This dmv is supprted on SQL 2008 and later versions
it provides some  more detailed information about memory in sql server
All details are in kb

SELECT physical_memory_in_use_kb,
FROM   sys.dm_os_process_memory

This will give us physical memory , locked page allocation (should be 0 if AWE is not enabled) ,
total , reserved, committed and available virtual address space, page fault count , memory utilization percentage and process physical memory low if the server is facing memory pressure

xp_readerrorlog parameter detail

Recently we had  hardware and performance issues on SQL Server,with our client , so while monitoring the system we also looked at error log. We used xp_readerrorlog procedure.
This is really useful procedure and I feel I should share some information for this undocumented store procedures

use procedure as input in dataflow task

If we use procedure as  source in data flow task , this generally dont work in SSIS
so we have to pass
SET nocount OFF
SET fmtonly OFF

 before running the procedure

So sql command will be like this

SET nocount OFF
SET fmtonly OFF

EXEC Proc1

cheers :-)

Server details using DMV

Sometimes we need basic server  details , we can get this information from below DMV

FROM   sys.dm_os_sys_info

It provides valuable miscellaneous  hardware information which includes  number of logical cpu and hyperthread ratio for servers. Also provides information for physical memory , virtual memory  , buffer pool , sql server start time . In one of my previous article I have discussed how to get SQL server start time from tempdb
But from here we can get more accurate detail about sql server start time .

SELECT cpu_count                                         logical_cpu,
       cpu_count / hyperthread_ratio                     physcial_cpu,
       physical_memory_in_bytes / ( 1024 * 1024 * 1024 ) memory_in_gb,
       virtual_memory_in_bytes / ( 1024 * 1024 * 1024 )  virtual_memory_in_gb,
       bpool_committed                                   buffer_pool_committed,
       bpool_commit_target                               buffer_pool_commit_target,
       bpool_visible                                     buffer_pool_visible,
FROM   sys.dm_os_sys_info

Popular Posts