Understanding replication with Implementation of Snapshot Replication in SQL Server 2012 (Part 1)

Understanding of replication with Implementation of Snapshot Replication in SQL Server 2012 (Part 1)

As per MSDN, replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

There are four types of replication, excluding subtype, available in SQL Server, such as:

  • Merge Replication
  • Snapshot Replication
  • Transaction Replication
  • Pee-to-peer replication

We will cover Snapshot Replication in this article; remaining type of replication will be available in follow up blog post.

Before we start configuring replication, it is mandatory to understand some of the terminology we will use in replication.

Publisher: Publisher is a server which hosts the database which acts as a source of data to distribute among other databases.

Distributor: Distributor is a server which hosts the database which acts as a mediator between publisher and subscriber. It receives the data from publisher database and sends it to subscriber database. You can set distributor server same as publisher server but it is HIGHLY recommended that you keep distributer server/database in separate server so that publisher server doesn’t be heavily loaded.

Subscriber: Subscriber is the server which hosts the databases where publisher/distributer sends the data to.

You can set replication between any server(s) which can be accessible to each other via network, VPN, dial up or anything else. In production environment, I would prefer to have three different servers (if budget permits) one host publisher, second host distributor and third host subscriber but here for this article, I have installed two different instance of SQL Server 2012. You can even host two different virtual machines in your computer to test this article.

My first instance is WIN-9H6QATRKY81\SQL2K12DEV and I will refer it as ServerA in this article.

My second instance is WIN-9H6QATRKY81\SQL2K12DEVTest and I will refer it as ServerB in this article.

Snapshot Replication:

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers. This is one of the very simple replication and easy to setup/maintain. In simple words, Snapshot replication creates snapshot of publisher database and send it to subscriber via distributor.

You should select Snapshot replication if your database has infrequent changes, your database is small in size or your database is having very large volume of data change in short duration. If these are not the case for you, you have to choose another replication not snapshot.

Before you actually proceed towards replication, you have to check few things.

  • List table, view, Stored procedure and any other SQL Server objects which you want to replicate
  • You have to have one shared folder which can be accessible from any of the servers which are part of replication in any capacity (Publisher, Distributer, and Subscriber). My shared folder is at \\win-9H6QATRKY81\ReplicationShare.

Now let us implement snapshot replication on AdventureWorks2012 database. If you don’t, already, have this database, kindly download it from here or use your own database.

1.) Connect to you SQL Server 2012 which you want as a publisher and right click “Replication” then click on “Configure Distribution” option. I connected to “ServerA

2.) Select the distributor server. If you want the current SQL Server 2012 instance to be act as distributor server, left the default selection as it is. That is what I did here for this article. If you wish, you can change selection via radio button and click on “Add” button to select another SQL server 2012 instance to work as distributor.

3.)    Give the path of snapshot folder where the snapshot will be created. This path should be accessible from all servers which are part of replication.  In my case, it is \\win-9H6QATRKY81\ReplicationShare.

4.)  You have to create or select distributor database. I have created new database with “Adv2012Distributor” name.

5.)    Select check box “Configure Distributor” and click on Finish.

though screen capture is not showing script check box selected above but if you would have selected it, it would have generated script to add distributor. script would look something like this:

[sourcecode language=”sql”]

/****** Scripting replication configuration. Script Date: 10/21/2012 10:19:49 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 10/21/2012 10:19:49 AM ******/
use master
exec sp_adddistributor @distributor = N’WIN-9H6QATRKY81\SQL2K12DEV’, @password = N”
GO
exec sp_adddistributiondb @database = N’Adv2012Distributor’, @data_folder = N’E:\Data’, @log_folder = N’E:\Data’, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

use [Adv2012Distributor]
if (not exists (select * from sysobjects where name = ‘UIProperties’ and type = ‘U ‘))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty(‘SnapshotFolder’, ‘user’, ‘dbo’, ‘table’, ‘UIProperties’, null, null)))
EXEC sp_updateextendedproperty N’SnapshotFolder’, N’\\win-9H6QATRKY81\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’
else
EXEC sp_addextendedproperty N’SnapshotFolder’, N’\\win-9H6QATRKY81\ReplicationShare’, ‘user’, dbo, ‘table’, ‘UIProperties’
GO

exec sp_adddistpublisher @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distribution_db = N’Adv2012Distributor’, @security_mode = 1, @working_directory = N’\\win-9H6QATRKY81\ReplicationShare’, @trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’
GO
[/sourcecode]

6.)Let us confirm whether our distributor database is created or not in ServerA. You can find it under “System Databases” Group. Refer following screen capture

7.)Once you are done with “Distributor” configuration, you should proceed towards “Publication”.  Connect to the server which you want to make as publisher, in my case, it is ServerA. Follow the given screen capture to know the option to click

8.) You will have to select the publisher database, in our case, it is AdventureWorks2012

9.)    Now this is the time to choose which replication you wanted create. At the point, we will create “Snapshot Replication”

10.) Now, you have to select table, view, stored procedure, indexed view and/or user defined functions. You can select all if you want. As of now, I have selected only two tables.

11.) Now if you don’t wish to replicate some data based on filter, you can have filter by click on “Add” button but I didn’t choose any filter and clicked on “Next” button in following screen or wizard.

12.) we are instructing that snapshot of publisher database should be created at the moment by first check box. scheduling is VERY IMPORTANT for replication as whatever change you will make in data in your publisher database, it will take effect in subscriber database(s) once the schedule time reach. Schedule should be very from case to case, it shouldn’t be too low otherwise before your snapshot applies to subscriber, your second run of snapshot will be reached. As against, it shouldn’t be too high that subscriber doesn’t have point-in time data. You can click on “Change” button and run it at every 10 minutes for now.

13.) this step is very important. You have to give proper login credential so that replication doesn’t meet with any permission issue. I would suggest you give windows authentication there in form of domainName\Login and Password.

14.) select both the check box given in following screen.

15.) give the proper path so that you will have .SQL file of publication script.

16.) Now give publication name here. one publisher database may have few different publication.

Once you will click on “Finish” button in above given screen, your publisher will be created and you will have publication script as follow:

[sourcecode language=”sql”]
use [AdventureWorks2012]
exec sp_replicationdboption @dbname = N’AdventureWorks2012′, @optname = N’publish’, @value = N’true’
GO
— Adding the snapshot publication
use [AdventureWorks2012]
exec sp_addpublication @publication = N’AdvertureWorks2012AddressObjects’, @description = N’Snapshot publication of database ”AdventureWorks2012” from Publisher ”WIN-9H6QATRKY81\SQL2K12DEV”.’, @sync_method = N’native’, @retention = 0, @allow_push = N’true’, @allow_pull = N’true’, @allow_anonymous = N’true’, @enabled_for_internet = N’false’, @snapshot_in_defaultfolder = N’true’, @compress_snapshot = N’false’, @ftp_port = 21, @ftp_login = N’anonymous’, @allow_subscription_copy = N’false’, @add_to_active_directory = N’false’, @repl_freq = N’snapshot’, @status = N’active’, @independent_agent = N’true’, @immediate_sync = N’true’, @allow_sync_tran = N’false’, @autogen_sync_procs = N’false’, @allow_queued_tran = N’false’, @allow_dts = N’false’, @replicate_ddl = 1
GO

exec sp_addpublication_snapshot @publication = N’AdvertureWorks2012AddressObjects’, @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [AdventureWorks2012]
exec sp_addarticle @publication = N’AdvertureWorks2012AddressObjects’, @article = N’Address’, @source_owner = N’Person’, @source_object = N’Address’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509D, @identityrangemanagementoption = N’manual’, @destination_table = N’Address’, @destination_owner = N’Person’, @vertical_partition = N’false’
GO

use [AdventureWorks2012]
exec sp_addarticle @publication = N’AdvertureWorks2012AddressObjects’, @article = N’AddressType’, @source_owner = N’Person’, @source_object = N’AddressType’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509D, @identityrangemanagementoption = N’manual’, @destination_table = N’AddressType’, @destination_owner = N’Person’, @vertical_partition = N’false’
GO
[/sourcecode]

17.) Now, we will create subscription. please connect with the SQL Server 2012 which you want to be subscription. In my case, it is ServerB. follow the steps given in following screen capture.

18.) For creating subscriber, you have to connect with publisher server.

19.) after connecting to publisher server, select the publication we have created.

20.) I don’t want to run SQL Server Agent job of replication at Publisher server because publisher server suppose to have load from various sources so I would go for PULL subscription so that replication job will run from ServerB and fetch the data from ServerA.

21.) Select or Create subscription database. since this is first time we are setting this subcription, we have no chance to have already created database so we will create new DB with name “AdventureWorks2012Subs”.

22.) This steps is VERY IMPORTANT, You must give proper login credential here. I would suggest to make sure that credential you are giving here, is having proper access of distributor and subscriber server. You can give two separate login credential here. I would like to go for windows authentication here too. Highly recommend, don’t go with “Impersonate Process account” as shown in following screen capture.

23.) select “Run Continuously” for “Agent Schedule” so that it keeps running at the interval we have selected.

24.) now select “Immediately” for “Initialize When” drop down list so that your configuration of snapshot agent takes effect immediately. if your credential provided in above screen is not proper, replication will not be able to move data between server.

25.) select both the check box same as we did two times before.

26.) give the proper path to save .SQL file for your Subscription script for future use.

Subscription scrip would look something like this:

[sourcecode language=”sql”]—————–BEGIN: Script to be run at Publisher ‘WIN-9H6QATRKY81\SQL2K12DEV’—————–
use [AdventureWorks2012]
exec sp_addsubscription @publication = N’AdvertureWorks2012AddressObjects’, @subscriber = N’WIN-9H6QATRKY81\SQL2K12DEVTEST’, @destination_db = N’AdventureWorks2012Subs’, @sync_type = N’Automatic’, @subscription_type = N’pull’, @update_mode = N’read only’
GO
—————–END: Script to be run at Publisher ‘WIN-9H6QATRKY81\SQL2K12DEV’—————–

—————–BEGIN: Script to be run at Subscriber ‘WIN-9H6QATRKY81\SQL2K12DEVTEST’—————–
use [AdventureWorks2012Subs]
exec sp_addpullsubscription @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @publication = N’AdvertureWorks2012AddressObjects’, @publisher_db = N’AdventureWorks2012′, @independent_agent = N’True’, @subscription_type = N’pull’, @description = N”, @update_mode = N’read only’, @immediate_sync = 1

exec sp_addpullsubscription_agent @publisher = N’WIN-9H6QATRKY81\SQL2K12DEV’, @publisher_db = N’AdventureWorks2012′, @publication = N’AdvertureWorks2012AddressObjects’, @distributor = N’WIN-9H6QATRKY81\SQL2K12DEV’, @distributor_security_mode = 1, @distributor_login = N”, @distributor_password = null, @enabled_for_syncmgr = N’False’, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20121021, @active_end_date = 99991231, @alt_snapshot_folder = N”, @working_directory = N”, @use_ftp = N’False’, @job_login = null, @job_password = null, @publication_type = 0
GO
—————–END: Script to be run at Subscriber ‘WIN-9H6QATRKY81\SQL2K12DEVTEST’—————–
[/sourcecode]

Finally we have successfully implemented Snapshot replication. You can confirm whether replication is working or not by insert/update few records in “Person.Address” table which was one of our two articles (table) we have added in this replication.

Please not that insert/update you have made, will not be immediately replicated. It will be replicated when you

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

LEAD and LAG Analytic functions in SQL Server 2012

LEAD and LAG Analytic functions in SQL Server 2012

LEAD and LAG functions were highly awaited and demanded function in SQL Server and finally it is now introduced in SQL Server 2012. I really like some of new functions introduced in SQL Server 2012 which I have listed in my earlier blog post. If you wish, you can access those articles from the links given below:

New Datetime Functions in SQL Server 2012 (Click Here)

New PARSE, TRY_PARSE & TRY_CONVERT conversion functions in SQL Server 2012 (Click Here)

New LAST_VALUE and FIRST_VALUE Analytic function in SQL Server 2012 (Click Here)

CUME_DIST and PERCENT_RANK new analytic functions in SQL Server 2012 (Click Here)

Well, now moving on!!!

Do you remember when you wanted to get previous and next value of particular column for particular row? I do remember it and I know I have used self-join, CTE, cursor , loop and many different type of logical solution but now our life become little easier with two new analytic functions in SQL Server which are known as LEAD & LAG.

LEAD: This function accesses the data from next row without any help of self-join or CTE.

LAG: This function accesses the data from previous row without any help of self-join or CTE.

Though, definition of these functions is short but use of these functions is really big and save you from developing customized logic many time. Let us understand this by small example here.

[sourcecode language=”sql”]
IF OBJECT_ID(‘tempdb..#AnalyticFunctionCheck’) IS NOT NULL
DROP TABLE #AnalyticFunctionCheck

CREATE TABLE #AnalyticFunctionCheck
 (
 ID INT IDENTITY(1,1)
 ,ClientID VARCHAR(10)
 ,TestName VARCHAR(20)
 ,TotalSample INT
 )

INSERT INTO #AnalyticFunctionCheck
SELECT ‘CHEM02′,’VOCMS GROUP 1’,4 UNION ALL
SELECT ‘CHEM02′,’SVOC GROUP 1’,6 UNION ALL
SELECT ‘CHEM02′,’SVOC STARS’,12 UNION ALL
SELECT ‘CHEM02′,’PESTICIDE’,4 UNION ALL
SELECT ‘SHAW01′,’PESTICIDE’,10 UNION ALL
SELECT ‘SHAW01′,’SVOC STARS’,3 UNION ALL
SELECT ‘SHAW01′,’SVOC GROUP 1’,9 UNION ALL
SELECT ‘EPAW01′,’SVOC GROUP 1’,14

SELECT
*
FROM
#AnalyticFunctionCheck

SELECT
*
,LAG(ID, 1) OVER (ORDER BY ID) AS PreviousQuota
,LEAD(ID, 1) OVER (ORDER BY ID) AS PreviousQuota
FROM
#AnalyticFunctionCheck

SELECT
*
,LAG(ID, 2) OVER (ORDER BY ID) AS PreviousQuota
,LEAD(ID, 2) OVER (ORDER BY ID) AS PreviousQuota
FROM
#AnalyticFunctionCheck
[/sourcecode]

After executing above given 3 queries, you will find result set like this:

First result set is simple “SELECT *” query on the temp table we created.

Second result set had offset “1” (refer LAG(ID, 1)). 1 is a default offset and it provides previous row if it is LAG function and next row if it is LEAD function.

Third result set returns ID of 2nd previous/Next row as we have provided offset “2” (refer LAG(ID, 2)) based on the function LEAD/LAG provided.

If function will not find previous/next value, it will return NULL by default.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

CUME_DIST and PERCENT_RANK new analytical functions in SQL Server 2012

CUME_DIST and PERCENT_RANK new analytical functions in SQL Server 2012

analytical_function

Regular reader of this blog already new that we have already seen new conversion functions and date time functions introduced in SQL Server 2012. Today we are going to look at some of the new analytical function in SQL Server 2012. Following is the list of functions we are going to cover in this article.

  • CUME_DIST ()
  • PERCENT_RANK ()

Before we look at the example of all these wonderful analytical functions in SQL Server 2012, let us look at the brief definition of these functions.

CUME_DIST : CUME_DIST function returns the cumulative distribution of a value in a group of values. That is, CUME_DIST computes the relative position of a specified value in a group of values. For a row r, assuming ascending ordering, the CUME_DIST of r is the number of rows with values lower than or equal to the value of r, divided by the number of rows evaluated in the partition or query result set. This function gives the percentage of values less than or equal to the current value in the group.

PERCENT_RANK : This function is almost same as CUME_DIST function. The range of values returned by PERCENT_RANK is greater than equal to 0 and less than or equal to 1. The first row in any set has a PERCENT_RANK of 0. This function calculates the percentage of values less than the current value in the group, excluding the highest value, Percent_Rank() for the highest value in a group will always be 1.

After having short definition of both functions, let us examine this fact by following TSQL Script:

[sourcecode language=”sql”]
CREATE TABLE #AnalyticFunctionCheck
(
ID INT IDENTITY(1,1)
,ClientID VARCHAR(10)
,TestName VARCHAR(20)
,TotalSample INT
)
INSERT INTO #AnalyticFunctionCheck
SELECT ‘CHEM02′,’VOCMS GROUP 1’,4 UNION ALL
SELECT ‘CHEM02′,’SVOC GROUP 1’,6 UNION ALL
SELECT ‘CHEM02′,’SVOC STARS’,12 UNION ALL
SELECT ‘CHEM02′,’PESTICIDE’,4 UNION ALL
SELECT ‘SHAW01′,’PESTICIDE’,10 UNION ALL
SELECT ‘SHAW01′,’SVOC STARS’,3 UNION ALL
SELECT ‘SHAW01′,’SVOC GROUP 1’,9 UNION ALL
SELECT ‘EPAW01′,’SVOC GROUP 1’,14

SELECT
*
,CUME_DIST() OVER(PARTITION BY TestName ORDER BY ClientID) AS CumeDist
,PERCENT_RANK() OVER(PARTITION BY TestName ORDER BY ClientID) AS PercentRank
FROM
#AnalyticFunctionCheck
[/sourcecode]

As soon as your run the above give code, you will get result set something like following screen capture:

Now observe we have first group “Pesticide” test. We have two rows in first group so CumeDist starts with “0.5”. This value comes by following calculation:

Row Number 1 (ID=4) : 1st row in group/total number of rows (1/2)=0.5

Row Number 2 (ID=5) : 2nd row in group/total number of rows (2/2)=1

In short, CUME_DIST=Number of the row we are calculating in the group/total number of row in group.

This is how Cume_Dist column is calculated.

Percent_Rank always starts with 0 so first value in the group will always have 0. Remaining values will be calculated as formula. See we have three rows (3, 4 and 5) in “Group 2”. First value is coming 0 as it is the first value as per our Order By clause. Second and third value calculated as per following formula.

Row Number 3 (ID=2) : (1st row in group-1.0)/(total number of rows-1.0) = (1-1.0)/(3-1) =0

Row Number 4 (ID=8) : (2ND row in group-1.0)/(total number of rows-1.0) = (2-1.0)/(3-1) =0.50

Row Number 5 (ID=7) : (3rd row in group-1.0)/(total number of rows-1.0) = (3-1.0)/(3-1) =1.0

In short, PERCENT_RANK=(Number of the row we are calculating in the group-1.0)/(total number of row in group-1).

Both of these are really wonderful and useful function. I am happy that it is included in SQL Server 2012.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Capture Schema Change in SQL Server to maintain history

Capture Schema Change in SQL Server to maintain history

Every production database should have strict audit/history policy for schema change so that each and every change can be tracked. SQL Server 2005+ providing trigger at database level which is being called at database level every time it meets criteria.

Here in this article, I am providing with one small TSQL Script of database trigger for DDL commands which activates itself automatically as and when any DDL command will be executed against any schema ( tables, views, stored procedures, triggers, index and more) of any objects in the database where we have create the database level DDL trigger.

We will create one table in database which will capture all schema change in SQL Server after creating that table, we will create DDL database trigger which detects any change made in schema and capture it in the table we have created. You can even set an email alert to authorize person so that whenever any change happens in the schema of table, Stored Procedure, View, Index, Function or any other objects, that person(s) will get an alert in an email along saving the history in table. I have not covered that email part here in this article but as long as you are having all the values which we are going to store in table, you can implement simple email script for your need.

By the way, I have already written one article on DDL trigger few years back but you can consider this article as an improved version, you can visit previous article here.

Let us now create the table which will store the history of schema change in the database.

Note: I have used “AdventureWorks2012” database for this article, you can use the same or else you can have your own database, code won’t have much changes.

[sourcecode language=”sql”]
USE [AdventureWorks2012]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [CaptureSchemaChange](
[EventType] [nvarchar](max) NULL,
[SchemaName] [nvarchar](max) NULL,
[ObjectName] [nvarchar](max) NULL,
[ObjectType] [nvarchar](max) NULL,
[EventDate] [datetime] NULL,
[SystemUser] [varchar](100) NULL,
[CurrentUser] [varchar](100) NULL,
[OriginalUser] [varchar](100) NULL,
[DatabaseName] [varchar](100) NULL,
[CommandText] [nvarchar](max) NULL,
[EventData] xml NULL,
[HostName] [varchar](50) NULL
)
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [CaptureSchemaChange] ADD  DEFAULT (host_name()) FOR [HostName]
GO[/sourcecode]

Once you are ready with table given in above script, we will now create DDL database trigger in SQL Server 2012 database.

[sourcecode language=”sql”]
USE [AdventureWorks2012]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [trgCaptureSchemaChange] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS

SET NOCOUNT ON

DECLARE @EventType NVARCHAR(MAX)
DECLARE @SchemaName NVARCHAR(MAX)
DECLARE @ObjectName NVARCHAR(MAX)
DECLARE @ObjectType NVARCHAR(MAX)
DECLARE @DBName VARCHAR(100)
DECLARE @Message VARCHAR(1000)
DECLARE @TSQL NVARCHAR(MAX)

SELECT
@EventType = EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]’,’NVARCHAR(MAX)’)
,@SchemaName = EVENTDATA().value(‘(/EVENT_INSTANCE/SchemaName)[1]’,’NVARCHAR(MAX)’)
,@ObjectName = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’,’NVARCHAR(MAX)’)
,@ObjectType = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectType)[1]’,’NVARCHAR(MAX)’)
,@DBName = EVENTDATA().value(‘(/EVENT_INSTANCE/DatabaseName)[1]’,’NVARCHAR(MAX)’)
,@TSQL = EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’NVARCHAR(MAX)’)

IF @SchemaName = ‘ ‘
BEGIN
SELECT
@SchemaName = default_schema_name
FROM
sys.sysusers SysUser
INNER JOIN
sys.database_principals Pri
ON
SysUser.uid = Pri.principal_id
WHERE
SysUser.name = CURRENT_USER
END

INSERT INTO [CaptureSchemaChange]
([EventType]
,[SchemaName]
,[ObjectName]
,[ObjectType]
,[EventDate]
,[SystemUser]
,[CurrentUser]
,[OriginalUser]
,[DatabaseName]
,[CommandText]
,[EventData]
)
SELECT
@EventType
, @SchemaName
, @ObjectName
, @ObjectType
, getdate()
, SUSER_SNAME()
, CURRENT_USER
, ORIGINAL_LOGIN()
, @DBName
, @TSQL
, EVENTDATA()
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [trgCaptureSchemaChange] ON DATABASE
GO[/sourcecode]

So, you have now successfully created DDL Database Trigger in SQL Server 2012. You schema is secured now, If anything will be changed, you will get an email, if email alert is set inside the trigger otherwise you can manually check the history in “CaptureSchemaChange” table we have created.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

LAST_VALUE and FIRST_VALUE function in SQL Server 2012

LAST_VALUE and FIRST_VALUE function in SQL Server 2012

After providing introduction to the new Conversion functions and DATE TIME functions in SQL Server, I come up with analytical functions LAST_VALUE and FIRST_VALUE.

Remember ROW_NUMBER, RANK & DENSE_RANK functions in SQL Server 2005 as well as in SQL Server 2008? LAST_VALUE & FIRST_VALUE function shares the similar syntax. After seeing all these functions, I remember those hard days of SQL Server 2000 when we had to write script for this kind of need. Really!!! Technology make us so productive ( and dumb too :) )

LAST_VALUE: As name suggests, this function will return the last value from the ordered set values.

FIRST_VALUE: FIRST_VALUE function is also behaving like its name; it returns the first value from the ordered set values.

Let us see how it actually works with following simple example which only works in SQL Server 2012.

[sourcecode language=”sql”]DECLARE @AppraisalHistory TABLE(

ID INT IDENTITY(1,1),

Name VARCHAR(35),

AppDate DATE,

TotalSalary INT

)

 

INSERT INTO @AppraisalHistory

SELECT ‘Ritesh’,DATEADD(YEAR,-2,GETDATE()),10000 UNION ALL

SELECT ‘Ritesh’,DATEADD(YEAR,-1,GETDATE()),12000 UNION ALL

SELECT ‘Ritesh’,GETDATE(),15000 UNION ALL

SELECT ‘Bihag’,DATEADD(YEAR,-2,GETDATE()),9000 UNION ALL

SELECT ‘Bihag’,DATEADD(YEAR,-1,GETDATE()),10000 UNION ALL

SELECT ‘Bihag’,GETDATE(),16000

SELECT

*,

LAST_VALUE(TotalSalary) OVER (PARTITION BY NAME ORDER BY ID) AS LastSalary ,

FIRST_VALUE(TotalSalary) OVER (PARTITION BY NAME ORDER BY ID) AS FirstSalary

FROM @AppraisalHistory[/sourcecode]

ENJOY new SQL Server with all its power!!!!

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

PARSE, TRY_PARSE and TRY_CONVERT Functions in SQL Server 2012

PARSE, TRY_PARSE and TRY_CONVERT Functions in SQL Server 2012

After giving the understanding of new introduced system stored procedure in SQL Server 2012 “sp_describe_first_result_set”, once again I come up with few new exciting system function of SQL Server 2012.

Let us have a lookup of each function whose name given in the title:

PARSE :

PARSE function is works similarly like COVERT function of SQL Server. You can provide string value in PARSE function to get it converted to string, numeric, datetime as per your need. PARSE function tries its best to convert provided string to your desired datatype but if it won’t able to convert it, PARSE function will return the error. Let us see small demo of PARSE function.

[sourcecode language=”sql”]DECLARE @StringVal VARCHAR(20)
SET @StringVal=’13-OCT-2012 10:00:00′
SELECT PARSE(@StringVal AS DATETIME)
SET @StringVal=’13OCT2012 10:00:00′
SELECT PARSE(@StringVal AS DATETIME)
SET @StringVal=’2012OCT13 10:00:00′
SELECT PARSE(@StringVal AS DATETIME)
[/sourcecode]

We have not provided date-time in proper format in @StringVal variable though PARSE function will convert it successfully. Have a look at screen capture below:

Now let us try something which PARSE function can’t convert so that we shall be greeted with the error message.

[sourcecode language=”sql”]DECLARE @StringVal VARCHAR(20)
SET @StringVal=’32-OCT-2012 10:00:00′
SELECT PARSE(@StringVal AS DATETIME)
[/sourcecode]

This is something PARSE function can’t convert as 32 is a wrong date. We shall meet the error something like this:

Msg 9819, Level 16, State 1, Line 4

Error converting string value ’32-OCT-2012 10:00:00′ into data type datetime using culture ”.

Confirm this from given screen capture.

For more details about PARSE function and possible conversion along with different parameter, check MSDN.

TRY_PARSE :

TRY_PARSE function works exactly same as PARSE function the only difference is, if PARSE function can’t convert the value, it will throw an error whereas TRY_PARSE function will return the value NULL rather than giving error.

[sourcecode language=”sql”]DECLARE @StringVal VARCHAR(20)
SET @StringVal=’13-OCT-2012 10:00:00′
SELECT TRY_PARSE(@StringVal AS DATETIME)
SET @StringVal=’13OCT2012 10:00:00′
SELECT TRY_PARSE(@StringVal AS DATETIME)
SET @StringVal=’2012OCT13 10:00:00′
SELECT TRY_PARSE(@StringVal AS DATETIME)
[/sourcecode]

We shall get the same result as we have received in PARSE function because all values could be converted to datetime format. Confirm the same from following screen capture

Let us try something which can’t be parsed so that we shall see whether errors coming or NULL with TRY_PARSE function.

[sourcecode language=”sql”]DECLARE @StringVal VARCHAR(20)
SET @StringVal=’32-OCT-2012 10:00:00′
SELECT TRY_PARSE(@StringVal AS DATETIME) [/sourcecode]

Again, its 32 in date so it can’t be parsed but TRY_PARSE function won’t give us error rather it will display NULL, confirm the same from following screen capture:

For more details about TRY_PARSE function and possible conversion along with different parameter, check MSDN

TRY_CONVERT

TRY_CONVERT is completely same as CONVERT function in SQL Server which we used to use since long. The only difference is TRY_CONVERT returns the NULL if conversion fails.

[sourcecode language=”sql”]DECLARE @StringVal VARCHAR(20)
SET @StringVal=’13-OCT-2012 10:00:00′
SELECT TRY_CONVERT(DATETIME,@StringVal)
SET @StringVal=’13OCT2012 10:00:00′
SELECT TRY_CONVERT(DATETIME,@StringVal)
SET @StringVal=’2012OCT13 10:00:00′
SELECT TRY_CONVERT(DATETIME,@StringVal)  [/sourcecode]

All values will be converted and result with remain same as we had with PARSE and TRY_PARSE functions. Confirm the same with following screen shot.

Now let us again try to convert something which is not possible, we shall not receive any error but we will receive the NULL in that case again.

[sourcecode language=”sql”]DECLARE @StringVal VARCHAR(20)
SET @StringVal=’32-OCT-2012 10:00:00′
SELECT TRY_CONVERT(DATETIME,@StringVal) [/sourcecode]

We shall have NULL by above conversion, confirm with following screen capture

Enjoy SQL Server 2012

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles.

sp_describe_first_result_set, new system stored procedure in SQL Server 2012

sp_describe_first_result_set, new system stored procedure in SQL Server 2012

I like SQL Server very much from the SQL Server 7.0. I used to follow each and every version of SQL Server very closely from its beta version. Microsoft always endeavors to make life easier and productive for developer and that is the spirit I like much.

I was busy in writing DBA related articles recently but now I think I have to give some fundamental updates about newer version of SQL Server to my blog reader and see, today I am here with one of the new system stored procedure “sp_describe_first_result_set” which is available only in SQL Server 2012 at the moment.

Before we further discuss “sp_describe_first_result_set”, let us create one dummy table with some sample rows so that we can use it in our example.

[sourcecode language=”sql”]CREATE TABLE tblResultSetTest

(

ID INT IDENTITY(1,1)

,BloggerName Varchar(25)

,BlogName VARCHAR(100)

,EmailAddress VARCHAR(100)

)

GO

INSERT INTO tblResultSetTest

SELECT ‘Ritesh Shah’,’Extreme-Advice.com’,’R.Shah@extreme-advice.com’ UNION ALL

SELECT ‘Pinal Dave’,’SQLAuthority.com’,’P.Dave@SQLAuthority.com’ UNION ALL

SELECT ‘Bihag Thaker’,’SQLArt.com’,’B.Thaker@SQLArt.com’

GO

CREATE VIEW ViewTblResultSetTest

AS

SELECT

ID as BlogID

,BloggerName as Name

,BlogName as Blog

,EmailAddress as Email

FROM

tblResultSetTest

GO [/sourcecode]

So, now we have one table “tblResultSetTest” with sample row and one view “viewTblResultSetTest” based on “tblResultsetTest” table. What would you do if I ask you to see metadata (Schema) of the table/view? Obviously, first thing would come in your mind is “SP_Help” system stored procedure which is one of the favorite system SP of any SQL Developer/DBA. Right?

You would do something like this:

[sourcecode language=”sql”]sp_help ViewTblResultSetTest [/sourcecode]

Which can give you result set like this:

.

Now, here twist comes, Microsoft has provided new system stored procedure “sp_describe_first_result_set” with much more functionality. Let us try it out by passing TSQL statement to this System stored Procedure, rather than table/view name we used to give in “SP_Help”

[sourcecode language=”sql”]EXEC sp_describe_first_result_set

N’SELECT * FROM ViewTblResultSetTest’, NULL, 0

GO [/sourcecode]

Last argument in above system stored procedure is “0” which is Browse_Information_Mode. “0” denotes that you will not be provided any details about table/view. You will only get column name which are part of our TSQL Statement (View) along with its datatype and few other view’s column related information. You can see the result in below given screen capture.

.

Now, let us execute same stored procedure with different Browse_Information_Mode.

[sourcecode language=”sql”]EXEC sp_describe_first_result_set

N’SELECT * FROM ViewTblResultSetTest’, NULL, 1

GO [/sourcecode]

Last argument in above system stored procedure is “1” which is Browse_Information_Mode. “1” provides you view’s column name and datatype as we have already received in “0” Browse_Information_Mode along with its original database name, schema name, original column name in table. You can see the result in below given screen capture.

.

Now, let us execute same stored procedure with different Browse_Information_Mode.

[sourcecode language=”sql”]EXEC sp_describe_first_result_set

N’SELECT * FROM ViewTblResultSetTest’, NULL, 2

GO [/sourcecode]

Last argument in above system stored procedure is “2” which is Browse_Information_Mode. “2” provides you view’s column name and datatype as we have already received in “0” Browse_Information_Mode along with its original database name but it will not provide original schema name and original column name of table, instead it will give you view name and column information from view itself. You can see the result in below given screen capture.

.

Isn’t it interesting??!!

Enjoy New SQL Server

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles.

SQL Server 2012 Performance Tuning Cookbook

We are leaving in the world of data now a day. RDBMS systems make it easy to manage data properly with validations and integrity even it needs expertise to keep database operation smooth so that you can get performance benefit. Everybody hates to wait for receiving information. No matter, whether they are waiting for response from internet, database, report or anything else.
It is a duty of database personnel in the company to make users of the database or software happy by not keeping them waiting for the information they require. In a business world, sometime, late information can create disaster for the company. This is the time when Performance Tuning in database comes into the picture.
SQL Server 2012 Performance Tuning Cookbook
Basically I have started my career as a Developer 10 years back and I used to work mostly with .NET technology for web & windows application. Later on I have shifted my interest towards database technology to maintain performance of my applications as even today, many companies do not afford expert database developer or administrator so they expect that .NET developer manage the show in that situation.
While working with database and performance tuning, I learned a lot by self learning or from the online community. Finally I decided to share my knowledge with the community in proper format. This is the wish which lead me towards writing a performance tuning book name “SQL Server 2012 Performance Tuning Cookbook” which will be available from June 2012..
After thinking to write something about performance tuning, me and my co-author Bihag Thaker had a long discussion about the architecture of the book and we finally decided to divide the book in three main topic.
Performance Monitoring: This supposes to be the first step when you think to tune anything in the database or in SQL Server. Without monitoring, you will not be able find the bottleneck which is creating hazard for performance.
Performance Tuning: Once you find the bottleneck, it is time to overcome that. You have to find different ways to eliminate that bottleneck and make performance faster. We are having many different chapters which are discussing this topic.
Performance Management: “Prevention is better than cure” this is what I believe so it is better to know some common mistakes which may lead to your system towards slow performance. If you know these, you can manage it even before it starts creating an issue for you.
I have always received overwhelming response and welcome from the online community in blog and from the forums I am active in. I am expecting the same response from the community for my book.
Happy Reading!!!
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah