SQL Server

TRY_CONVERT:- New function in Denali

2011-09-10T10:44:00.002+05:30

TRY_CONVERT

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

Syntax

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

Lets see example to understand it

If conversion is possible it will be return converted value else null

SELECT Try_convert (int, '1'),

Try_convert (int, 'a1')

If conversion is not allowed it will return error

SELECT Try_convert(image, 123)

use of style

SELECT Try_convert (Varchar(50), Getdate(), 9)

THROW new function in Denali

2011-09-10T10:24:00.002+05:30

THROW

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.

SYNTAX

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

Error_number :- must be greater than 50000 , can be any one. Can be a variable also

Message :- A custom message , dont need to be a printf like raiserror. Can be a variable also

State:- A value between 0 and 255. Indicates state of a statement associated. I am not sure how to use it till now. :-)

1. If not used in try catch then batch will be aborted
2 If used in try then parameters should be passed
3 No need of parameter if used in CATCH
4.In TRY block the previous satement before catch must end with ;

A better idea can be achieved with Example

CREATE TABLE t

(

id INT PRIMARY KEY,

data VARCHAR(4)

)

We will raise an error by inserting string lengh more than specified length in table

BEGIN TRY

INSERT INTO t

VALUES ( 1,

'data')

INSERT INTO t -- THIS WILL FAIL

VALUES (2,

'data1234')

END TRY

BEGIN CATECH

SELECT Error_message()

END CATCH

Now we will raise same error by using throw statement

BEGIN TRY

INSERT INTO t -- THIS WILL FAIL

VALUES ( 3,

'data1234')

END TRY

BEGIN CATCH

THROW

END CATCH

Now we want to issue throw in TRY block to generate custom error. Code after throw will be aborted

BEGIN TRY

INSERT INTO t

VALUES ( 3,

'data');

THROW 242432,'Why are you inserting data ?',1

INSERT INTO t -- THIS WILL ABORTED

VALUES ( 4,

'data');

END TRY

BEGIN CATCH

THROW

END CATCH

SELECT * FROM T

New DATETIME functions in Denali

2011-09-09T21:47:00.001+05:30

New DateTime Function in Denali

1. EOMONTH
    This will return last day for the month from the date at specified offset

    syntax

    EOMONTH ( start_date [, month_to_add ] )


    StartDate :- Date from which we want to get last day of month at offset

    Offset:- Specifying number of months to add. Default is 0


    Example

SELECT Eomonth(Getdate()) last_day_of_month

    with offset


    select eomonth (getdate(), 1) last_day_next_month, eomonth(getdate(),-1) last_day_prev_month



2.DATEFROMPARTS

Returns day from specified value of year, month, day

DATEFROMPARTS ( year, month, day )

Example

SELECT Datefromparts (2011, 09, 01)

3.TIMEFROMPARTS

Returns a time from specified time paratmeters

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Precision shows part of second
if its 7 its shows nano second , if its 3 it shows millisecond

Fraction is depending on precision.its length should be in range of precision

SELECT Timefromparts (10, 10, 10, 0, 0) AS time

With specifying fraction

SELECT TIMEFROMPARTS ( 10, 10, 10, 1, 7 ) AS Result

Lag:-New function in sql server denali

2011-09-07T10:29:00.004+05:30

LAG

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

We can use this function to compare value with other row in the Table for analysis purpose

Syntax

LAG ( scalar_expression ,offset , default )

OVER ( Parition By,Order By)

Scalar Expression :- Any scalar expression allowed in SQL Server

Offset:- Number of rows forward from the current row

Default:- Default value if no row find at given Offset location from current row

Example

Lets see we want to compare order quantity of each workorder with its previous workorder

SELECT Lag(orderqty, 1, 0) OVER (ORDER BY workorderid) nextorderqty,

orderqty,

FROM production.workorder

Whate we can see here is that first row does not have any pervious subsequent row. so it will display defualt 0

Use of Partition by function

Now we want to compare order quantity but in each ProductID

SELECT Lag(orderqty, 1, 0) OVER (partition BY productid ORDER BY workorderid) nextorderqty,

orderqty,

FROM production.workorder

Here first order in each ProductID will have default value 0 because it doesnt have next record in the same ProductID

FIRST_VALUE:-New function in sql server denali

2011-08-31T12:22:00.000+05:30

FIRST_VALUE

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

FIRST_VALUE (
OVER ( < partition_by_clause > order_by_clause < rows_range_clause > )

scalar_expression :- A valid scalar expression
over clause:- partition by to partition dataset based on given function if not then all rows are in a single group, order by to specify order . rows_range_clause is new argument where we can set start and end point in the rows

Example

I want to get first orderqty from the result set

SELECT First_value (orderqty) OVER (ORDER BY workorderid) firstorderqty,

FROM production.workorder

Now we want to get first orderqty for each ProductId order by workorderid

SELECT First_value (orderqty) OVER (partition BY productid ORDER BY workorderid) firstorderqty,

FROM production.workorder

Lead:-New function in sql server denali

2011-08-30T17:17:00.002+05:30

LEAD

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

Syntax

LEAD ( scalar_expression ,offset , default )
OVER ( Parition By,Order By)

Scalar Expression :- Any scalar expression allowed in SQL Server
Offset:- Number of rows forward from the current row
Default:- Default value if no row find at given Offset location from current row

Example

Lets see we want to compare order quantity of each workorder with its next workorder

SELECT Lead(orderqty, 1, 0) OVER (ORDER BY workorderid) nextorderqty,

orderqty,

FROM production.workorder

Use of Partition by function

Now we want to compare order quantity but in each ProductID

SELECT Lead(orderqty, 1, 0) OVER (partition BY productid ORDER BY workorderid) nextorderqty,

orderqty,

FROM production.workorder

Here last order in each ProductID will have default value 0 because it doesnt have next record in the same ProductID

choose :- New function in sql server denali

2011-08-30T14:48:00.000+05:30

CHOOSE

Retruns value at specified index from list of values

lets see it

SELECT Choose (1, 'a', 'b', 'c', 'd')

If no value presents at specified index than it returns null

SELECT Choose (7, 'a', 'b', 'c', 'd')

IIF :- New function in sql server denali

2011-08-29T16:49:00.000+05:30

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

Syntax

IIF ( boolean expression, true value , false value )

Example

If the table is created before 2011-01-01 we want to identify its as old tables else new tables

Lets see how we can do it using IIF

SELECT Iif (create_date < '2011-01-01', 'New Table', 'Old Table'),

FROM sys.objects

Concat :- New function in sql server denali

2011-08-29T16:39:00.003+05:30

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

Concat

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

Concat (, ,...

SELECT Concat ('First', ' ', 'Second', ' ', 'Three')

WE want tablename and columname with seperate by .

Here is the query for it

SELECT Concat (Object_name(object_id), '.', name),

FROM sys.columns

Debug query in SSMS

2011-07-07T21:13:00.000+05:30

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.

Its simple and easy. Lets see how to do it.

In toolbar we have toolbar of Debug , if not we can open it as below

Now we will run below query in SSMS

DECLARE @I INT

SET @I = 1

WHILE @I <= 100

BEGIN

PRINT @I

SET @I = @I + 1

END

Now start debugging as shown below

Open watch window as show below

Now create breakpoints in the query, by clicking at starting of the row.

Type parameter @i in the Name field of watch window. Monitor the value of @i in the watch window.

Ok fine. Now I want to jump the value . I want to set its value to some new number
See below how to modify it. Modify it and start debugging.
Is it simple :-)

SQL Server 2008 R2 Analysis Services operations guide

2011-06-11T17:22:00.000+05:30

Microsoft has releases Analysis Service 2008 R2 operations guide.

This can be quite useful to understand new feature and optimizations in 2008 R2 edition.

We can download it from

http://msdn.microsoft.com/en-us/library/hh226085.aspx

This white paper describes how operations engineers can test, monitor, capacity plan, and troubleshoot Microsoft SQL Server Analysis Services OLAP solutions in SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.

Object Search in SSMS

2011-06-11T16:52:00.001+05:30

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
SSMS object explorer details , quite easily. In object explorer details there is search window at top where we can find objects and we can use wild cards also here :-)

Activity Monitor SQL Server

2011-06-11T16:44:00.000+05:30

Activity Monitor

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

Lets see how to use it

How to open activity monitor

Below image is self explanatory for how to open activity monitor

It shows 4 graphs
which has 4 performance counters
1.%processor time (% processor time used by sql server)
2.Waiting Tasks
3.Data File I/O
4.Batch Requests per second

Now we can get this info from SSMS instead of going to performance counters

Processes
It shows all current processes running on the server and its related details
We can also sort them and filter it.
We can see its providing rich information from our perspective

Resource Wait
It gives idea when operations has to wait for resources. It can give a idea to troubleshoot resource bottleneck. High wait indicate some bottleneck for that operations ,

Data File I/O
If some data file heavily affected by disk I/O we can get this info from here

Recent Expensive queries
It shows all queries running on the server. Which we can use to get idea which queries are taking high resources or expensive queries

Script SQL Server Jobs

2011-06-11T16:23:00.000+05:30

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
script all jobs. Then replace necessary login and server name in the script and execute them at the target server

Lets see how to do it

In SSMS in go to jobs. Open object explorer details window at the right side of SSMS
Then select jobs which we want to script right click it and script it. So simple !!!

SQL Server 2008 R2 Developers Training Kit

2011-06-10T16:21:00.001+05:30

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

http://www.microsoft.com/download/en/details.aspx?id=16281

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:

Getting Started
Web Developers: 1 Hands-On Lab, 2 Videos
BI Developers: 32 Presentations, 27 Demos, 12 Hands-On Labs, 59 Videos
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
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
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

2011-03-31T10:41:00.003+05:30

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
Also sometimes if devs stop synchronizing replication and forget to start it. He need an alert for this. During this we setup number of processes to monitor this. I would like to share one of store proc for this which I think will be useful for all.

sp_replmonitorhelpsubscription

This helps too much to monitor subscriptions

From BOL

sp_replmonitorhelpsubscription @publisher = ] 'publisher'
    [ , [ @publisher_db = ] 'publisher_db' ]
    [ , [ @publication = ] 'publication' ]
    [ , [ @publication_type = ] publication_type ]
    [ , [ @mode = ] mode ]
    [ , [ @topnum = ] topnum ]
    [ , [ @exclude_anonymous = ] exclude_anonymous ]
    [ , [ @refreshpolicy = ] refreshpolicy ]

@publisher Name of @publisher or default is null then it will display for all publishers that use that distributor

@publisher_db Name of publisher Datatbase or is null then all publisher Datatbase

@publication = Name of publication or default is NULL

@publication_type

0                Transactional publication.
1                Snapshot publication.
2                Merge publication.
NULL (default) Replication tries to determine the publication type.

@mode This is most important parameter which serves our objective

0 (default)        Returns all subscriptions.
1                Returns only subscriptions with errors.
This will give all subscription with errors
2                Returns only subscriptions that have generated threshold metric warnings.
This will give all subscription which about to cross the threshold metrics .
3                Returns only subscriptions that either have errors or have generated threshold metric warnings.
This will give both 1 & 2
4                Returns the top 25 worst performing subscriptions.
5                Returns the top 50 worst performing subscriptions.
6                Returns only subscriptions that are currently being synchronized.
7                Returns only subscriptions that are not currently being synchronized
If we forgot to start synchronizing then this will help us to find this, with last synchronization time

Lets see one example I Have modified the server name /db name .

EXEC distribution..Sp_replmonitorhelpsubscription

@publisher =NULL -- for all publisher

@publication_type =0 -- for trnasactional replications

@mode =3 -- All which have errors or threshold limit warning

Now I will stop synchronization for one subscription and we will get it

SELECT Getdate()

EXEC distribution..Sp_replmonitorhelpsubscription

@publisher =NULL -- for all publisher

@publication_type =0 -- for trnasactional replications

@mode =7 -- All where synchronization has stopped

You can look into following procedures also , which are not so useful for me as of now but can give good information

sp_replmonitorhelppublisher
sp_replmonitorhelppublicationthresholds
sp_replmonitorhelppublication

SQL Server Downloads

2011-03-28T17:55:00.001+05:30

As we are using SQL Server , we have to do number of download related to SQL Server
I have tried to gather all details together for us.

SQL Server 2008 R2

SQL Server 2008 R2 Books Online

SQL Server 2008 Evaluation edition

SQL Server 2008 Books Online

SQL Server 2008 upgrade Advisor

SQL Server 2008 SP1

SQL Server 2008 SP2