System Stored Procedures are useful in performing administrative and informational activities in SQL Server. Here’s a bunch of System Stored Procedures that are used on a frequent basis (in no particular order):
System Stored Procedure
Description
sp_help Reports information about a database object, a user-defined data type, or a data type
sp_helpdb Reports information about a specified database or all databases
sp_helptext Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure
sp_helpfile Returns the physical names and attributes of files associated with the current database. Use this stored procedure to determine the names of files to attach to or detach from the server
sp_spaceused Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database
sp_who Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session
sp_lock Reports information about locks. This stored procedure will be removed in a future version of Microsoft SQL Server. Use the sys.dm_tran_locks dynamic management view instead.
sp_configure Displays or changes global configuration settings for the current server
sp_tables Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause, except synonym objects.
sp_columns Returns column information for the specified tables or views that can be queried in the current environment
sp_depends Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure. References to objects outside the current database are not reported
These were some System Stored Procedures in SQL Server that come in very handy. If you have been using any other system stored procedures (not listed here) on a frequent basis, feel free to share them via the comments section.
Wednesday, 22 December 2010
Find Number of Weeks in a Month using SQL Serv
I have been asked this question plenty of times – How do I calculate the number of weeks in a month. The answer to this query depends on how do you define a week. For simplicity purposes, I will take a week from Sunday to Saturday as depicted in the calendar
So November has 5 weeks!
Let us write the query to calculate the number of weeks in each month of this year. This query was originally written by Michael Jones and I have modified it to suit the requirement
DECLARE @Yr SMALLINT
SET @Yr = 2009
;WITH NumWeeks
AS
(
SELECT Number + 1 as mth,
DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,0))/7 AS fst,
DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,30))/7 AS lst
FROM master..spt_values
WHERE Type = 'P' and Number < 12
)
SELECT DateName(mm,DATEADD(mm,mth,-1)) as [MonthName],
lst - fst + 1 AS [NumberOfWeeks]
FROM NumWeeks;
OUTPUT
So November has 5 weeks!
Let us write the query to calculate the number of weeks in each month of this year. This query was originally written by Michael Jones and I have modified it to suit the requirement
DECLARE @Yr SMALLINT
SET @Yr = 2009
;WITH NumWeeks
AS
(
SELECT Number + 1 as mth,
DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,0))/7 AS fst,
DATEDIFF(day,-1,DATEADD(month,((@Yr-1900)*12)+ Number,30))/7 AS lst
FROM master..spt_values
WHERE Type = 'P' and Number < 12
)
SELECT DateName(mm,DATEADD(mm,mth,-1)) as [MonthName],
lst - fst + 1 AS [NumberOfWeeks]
FROM NumWeeks;
OUTPUT
SQL Server 2008: Merge Statement
SQL Server 2008 introduced the Merge statement which is commonly also called as Upsert (Update / Insert). It can be used as Insert if the row does not exists and update, if it exists. It can be used as ETL process while fetching data. This feature is useful for developers. It is not required to give any particular join (inner, left outer etc).
Let’s see some advantages for using Merge statement.
With Merge statement the ability to insert a row if it does not exist or to update if it exists, is provided with a single statement. With previous version of SQL it was required to create separate statements for insert, update or delete to be done. With Merge statement, a source table (or a query) is allowed to join with target table (or updatable view) based on a criteria. Depending upon the match of the criteria specified update, insert or delete can be used. There are 3 options in the condition WHEN MATCHED, WHEN TARGET NOT MATCHED, WHEN SOURCE NOT MATCHED.
The syntax of Merge statement is as follows :
MERGE source
USING
(SELECT * FROM TARGET)
ON criteria
WHEN MATCHED
THEN UPDATE/DELETE
WHEN TARGET NOT MATCHED
THEN INSERT
WHEN SOURCE NOT MATCHED
THEN UPDATE/DELETE
Let’s see some actual code. It is created using Feb CTP of SQL Server 2008 (CTP 6).
CREATE TABLE source
(ID int NOT NULL,
FullName nvarchar(35))
CREATE TABLE target
(ID int NOT NULL,
FullName nvarchar(35))
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(2,'Aishwarya Bachchan')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
With following statement only the update is done if there is match :
MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
WHEN MATCHED
THEN UPDATE SET src.FullName=t.FullName;
SELECT * FROM source
SELECT * FROM [target]
The following will be the actual values in both the tables
Now let’s try insert as well as update with Marge statement. Let’s delete previous records from the tables and continue with fresh data :
DELETE source
DELETE [target]
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')
SELECT * FROM source
SELECT * FROM [target]
The select query result will look as follows :
After using Merge with Insert and Delete
MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
WHEN MATCHED
THEN UPDATE SET src.FullName=t.FullName
WHEN TARGET NOT MATCHED
THEN INSERT VALUES (t.ID, t.FullName);
SELECT * FROM source
SELECT * FROM [target]
The result of the Merge statement will give following output :
Now let’s try all three conditions and see the result. Let’s start fresh with the records again :
DELETE source
DELETE [target]
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')
SELECT * FROM source
SELECT * FROM [target]
The result will be as follows :
Let’s use Merge and see the result
We no longer see record for ID =3 as it gets deleted with this merge statement.
Summary
Do not forget to give ; (semi colon) at the end of Merge statement. You will end up in parser error otherwise. If multiple rows match in target for criteria following error is given.
Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
This is advantage of Merge over Update with join. Merge is deterministic whereas Update with join will update any row without any error.
Let’s see some advantages for using Merge statement.
With Merge statement the ability to insert a row if it does not exist or to update if it exists, is provided with a single statement. With previous version of SQL it was required to create separate statements for insert, update or delete to be done. With Merge statement, a source table (or a query) is allowed to join with target table (or updatable view) based on a criteria. Depending upon the match of the criteria specified update, insert or delete can be used. There are 3 options in the condition WHEN MATCHED, WHEN TARGET NOT MATCHED, WHEN SOURCE NOT MATCHED.
The syntax of Merge statement is as follows :
MERGE source
USING
(SELECT * FROM TARGET)
ON criteria
WHEN MATCHED
THEN UPDATE/DELETE
WHEN TARGET NOT MATCHED
THEN INSERT
WHEN SOURCE NOT MATCHED
THEN UPDATE/DELETE
Let’s see some actual code. It is created using Feb CTP of SQL Server 2008 (CTP 6).
CREATE TABLE source
(ID int NOT NULL,
FullName nvarchar(35))
CREATE TABLE target
(ID int NOT NULL,
FullName nvarchar(35))
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(2,'Aishwarya Bachchan')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
With following statement only the update is done if there is match :
MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
WHEN MATCHED
THEN UPDATE SET src.FullName=t.FullName;
SELECT * FROM source
SELECT * FROM [target]
The following will be the actual values in both the tables
Now let’s try insert as well as update with Marge statement. Let’s delete previous records from the tables and continue with fresh data :
DELETE source
DELETE [target]
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')
SELECT * FROM source
SELECT * FROM [target]
The select query result will look as follows :
After using Merge with Insert and Delete
MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
WHEN MATCHED
THEN UPDATE SET src.FullName=t.FullName
WHEN TARGET NOT MATCHED
THEN INSERT VALUES (t.ID, t.FullName);
SELECT * FROM source
SELECT * FROM [target]
The result of the Merge statement will give following output :
Now let’s try all three conditions and see the result. Let’s start fresh with the records again :
DELETE source
DELETE [target]
INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')
INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')
SELECT * FROM source
SELECT * FROM [target]
The result will be as follows :
Let’s use Merge and see the result
We no longer see record for ID =3 as it gets deleted with this merge statement.
Summary
Do not forget to give ; (semi colon) at the end of Merge statement. You will end up in parser error otherwise. If multiple rows match in target for criteria following error is given.
Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
This is advantage of Merge over Update with join. Merge is deterministic whereas Update with join will update any row without any error.
Find First and Last Day of the Current Quarter in SQL Server
I was recently working on a requirement where the user wanted a report with data from the First day to the Last Day of the current Quarter
Here's a simple way to find the Find First and Last Day of the current quarter in SQL Server 2005/2008
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) as FirstDayOfQuarter
SELECT DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1) as LastDayOfQuarter
OUTPUT
FirstDayOfQuarter LastDayOfQuarter
2009-04-01 00:00:00.000 2009-06-30 00:00:00
Here's a simple way to find the Find First and Last Day of the current quarter in SQL Server 2005/2008
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) as FirstDayOfQuarter
SELECT DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1) as LastDayOfQuarter
OUTPUT
FirstDayOfQuarter LastDayOfQuarter
2009-04-01 00:00:00.000 2009-06-30 00:00:00
A Simple Family Tree Query Using Recursive CTE’s in SQL Server 2005/2008
I was recently working with one of my colleagues Steve on a Family Tree query. His software collected information about Relations of a person and then displayed it in a TreeView. We started discussing about Recursive Common Table Expressions(CTE) and how well CTE’s fit into such requirements. I will give you an example of how we used Recursive CTE’s to get the Parent and Generation Level of each person in the Family Tree. One of the tables in his software looked similar to the following -
Note: A Family Tree usually contains many more columns than the one shown here. I have reduced the columns of the original table for understanding purposes. We are also considering only one of the Parents (Father or Mother) of each person in this query
DECLARE @TT TABLE
(
ID int,
Relation varchar(25),
Name varchar(25),
ParentID int
)
INSERT @TT
SELECT 1,' Great GrandFather' , 'Thomas Bishop', null UNION ALL
SELECT 2,'Grand Mom', 'Elian Thomas Wilson' , 1 UNION ALL
SELECT 3, 'Dad', 'James Wilson',2 UNION ALL
SELECT 4, 'Uncle', 'Michael Wilson', 2 UNION ALL
SELECT 5, 'Aunt', 'Nancy Manor', 2 UNION ALL
SELECT 6, 'Grand Uncle', 'Michael Bishop', 1 UNION ALL
SELECT 7, 'Brother', 'David James Wilson',3 UNION ALL
SELECT 8, 'Sister', 'Michelle Clark', 3 UNION ALL
SELECT 9, 'Brother', 'Robert James Wilson', 3 UNION ALL
SELECT 10, 'Me', 'Steve James Wilson', 3
Here’s the query to find the Parent and Generation Level using Recursive CTE’s
;WITH FamilyTree
AS
(
SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 0 AS Generation
FROM @TT
WHERE ParentID IS NULL
UNION ALL
SELECT Fam.*,FamilyTree.Name AS ParentName, Generation + 1
FROM @TT AS Fam
INNER JOIN FamilyTree
ON Fam.ParentID = FamilyTree.ID
)
SELECT * FROM FamilyTree
Note: A Family Tree usually contains many more columns than the one shown here. I have reduced the columns of the original table for understanding purposes. We are also considering only one of the Parents (Father or Mother) of each person in this query
DECLARE @TT TABLE
(
ID int,
Relation varchar(25),
Name varchar(25),
ParentID int
)
INSERT @TT
SELECT 1,' Great GrandFather' , 'Thomas Bishop', null UNION ALL
SELECT 2,'Grand Mom', 'Elian Thomas Wilson' , 1 UNION ALL
SELECT 3, 'Dad', 'James Wilson',2 UNION ALL
SELECT 4, 'Uncle', 'Michael Wilson', 2 UNION ALL
SELECT 5, 'Aunt', 'Nancy Manor', 2 UNION ALL
SELECT 6, 'Grand Uncle', 'Michael Bishop', 1 UNION ALL
SELECT 7, 'Brother', 'David James Wilson',3 UNION ALL
SELECT 8, 'Sister', 'Michelle Clark', 3 UNION ALL
SELECT 9, 'Brother', 'Robert James Wilson', 3 UNION ALL
SELECT 10, 'Me', 'Steve James Wilson', 3
Here’s the query to find the Parent and Generation Level using Recursive CTE’s
;WITH FamilyTree
AS
(
SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 0 AS Generation
FROM @TT
WHERE ParentID IS NULL
UNION ALL
SELECT Fam.*,FamilyTree.Name AS ParentName, Generation + 1
FROM @TT AS Fam
INNER JOIN FamilyTree
ON Fam.ParentID = FamilyTree.ID
)
SELECT * FROM FamilyTree
Rename a Column in a Table in a SQL Server Database
This is a very simple script I am sharing today. A sqlservercurry.com user mailed me and asked me the simplest way to rename a column. Well here’s the query I have been using all along
USE Social;
GO
EXEC sp_rename 'dbo.Person.PersonName', 'PersonNm', 'COLUMN';
GO
This query renames the Column ‘PersonName’ in the Person table to ‘PersonNm’. Be careful while you do so, as you may affect Stored Procedures that use the column. Infact, when you run the query shown above, a caution gets displayed in your SQL Server Management Studio warning you of the change.
Caution: Changing any part of an object name could break scripts and stored procedures.
USE Social;
GO
EXEC sp_rename 'dbo.Person.PersonName', 'PersonNm', 'COLUMN';
GO
This query renames the Column ‘PersonName’ in the Person table to ‘PersonNm’. Be careful while you do so, as you may affect Stored Procedures that use the column. Infact, when you run the query shown above, a caution gets displayed in your SQL Server Management Studio warning you of the change.
Caution: Changing any part of an object name could break scripts and stored procedures.
Generate Insert Statements For a SQL Server Table
Microsoft SQL Server Database Publishing Wizard is a great tool to generate script for both schema and data for a database. However what if you were to write a script that could do that for you.
Here’s a real cool script written by johnnycrash that generates Insert Statements for a SQL Server Table.
For demonstration purposes, I am using the Culture table of the AdventureWorks database.
USE AdventureWorks
GO
DECLARE @Columns VARCHAR(max);
SET @Columns = '[CultureID], [Name], [ModifiedDate]'
DECLARE @Table VARCHAR(max);
SET @Table = 'Production.Culture'
DECLARE @SQL VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '',
''INSERT INTO ' + @Table + '(' + @Columns + ')'')
+ CHAR(13) + CHAR(10)
+ ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Columns, ',', ' + '', '' + '),
'[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''')
+' FROM ' + @Table
+ ' PRINT @S'
EXEC (@SQL)
Here’s a real cool script written by johnnycrash that generates Insert Statements for a SQL Server Table.
For demonstration purposes, I am using the Culture table of the AdventureWorks database.
USE AdventureWorks
GO
DECLARE @Columns VARCHAR(max);
SET @Columns = '[CultureID], [Name], [ModifiedDate]'
DECLARE @Table VARCHAR(max);
SET @Table = 'Production.Culture'
DECLARE @SQL VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '',
''INSERT INTO ' + @Table + '(' + @Columns + ')'')
+ CHAR(13) + CHAR(10)
+ ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Columns, ',', ' + '', '' + '),
'[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''')
+' FROM ' + @Table
+ ' PRINT @S'
EXEC (@SQL)
Site to follow
http://www.sqlservercurry.com/search/label/Analysis%20Services%20SSAS?updated-max=2009-08-15T04:55:00-07:00&max-results=20----SSAS
http://www.sqlservercurry.com/search/label/Transact%20SQL%20T-SQL?updated-max=2010-01-08T04:55:00-08:00&max-results=20---SQL Server2008
http://www.sqlservercurry.com/search/label/Reporting%20Services%20SSRS----SSRS
http://www.sqlservercurry.com/search/label/Transact%20SQL%20T-SQL?updated-max=2010-01-08T04:55:00-08:00&max-results=20---SQL Server2008
http://www.sqlservercurry.com/search/label/Reporting%20Services%20SSRS----SSRS
Different ways for executing SQL Server Integration Package
With Business Intelligence Development Studio (BIDs) we get a template for creating SQL Server Integration Services (SSIS) Project, which in turn creates individual packages. Each package has an extension .dtsx (Data Transformation Services with XML) which is a unit of deployment and execution. There are several ways in which the package can be executed. Here are some of those
1. Run the package from BIDs itself.
With this option, we have the advantage of working with debug mode for control flow tasks and Data Viewer for Data Flow Tasks.
The disadvantage is that we will not have Visual Studio on deployment server.
2. Run the package with Command line utility DTEXECUI
This means Data Transfer EXecution User Interface
User Interface is provided with the help of which certain properties like setting command line parameters, specifying configuration files, setting values for variables at run time etc
3. Run the package wit Command line utility DTEXEC
As the name suggests, this option does not have an user interface but certain switches that can be used with the command
Depending upon where the package is stored, we can use different switches for file deployment or SQL server deployment
Users can specify properties values with /Set
4. Run the package after deploying it on the server either in a File System or on a SQL server database (MSDB)
This option will provide a similar interface like DTEXECUI
5. Run the package as a SQL Server Agent job
Create a step for SQL server agent job to execute a package and depending upon the schedule, the package will be executed automatically
6. Write a code with Visual Studio with C# or VB.NET which will execute package from the application. In this option, we will need to first set references to System.SQLServer.ManagedDTS so as to load existing package in the application by using Application.LoadPackage() method and then execute it by using the Package.Execute() method.
C#
using Microsoft.SqlServer.Dts.Runtime;
Package pkg = new Package();
Application app = new Application();
//load and execute package
pkg = app.LoadPackage(@"/.dtsx”, null);
pkg.Execute();
VB.NET
Imports Microsoft.SqlServer.Dts.Runtime
Package pkg= new Packagr()
Application app= new Application()
'load and package
pkg = app.LoadPackage(“/.dtsx”,null)
pkg.Execute()
1. Run the package from BIDs itself.
With this option, we have the advantage of working with debug mode for control flow tasks and Data Viewer for Data Flow Tasks.
The disadvantage is that we will not have Visual Studio on deployment server.
2. Run the package with Command line utility DTEXECUI
This means Data Transfer EXecution User Interface
User Interface is provided with the help of which certain properties like setting command line parameters, specifying configuration files, setting values for variables at run time etc
3. Run the package wit Command line utility DTEXEC
As the name suggests, this option does not have an user interface but certain switches that can be used with the command
Depending upon where the package is stored, we can use different switches for file deployment or SQL server deployment
Users can specify properties values with /Set
4. Run the package after deploying it on the server either in a File System or on a SQL server database (MSDB)
This option will provide a similar interface like DTEXECUI
5. Run the package as a SQL Server Agent job
Create a step for SQL server agent job to execute a package and depending upon the schedule, the package will be executed automatically
6. Write a code with Visual Studio with C# or VB.NET which will execute package from the application. In this option, we will need to first set references to System.SQLServer.ManagedDTS so as to load existing package in the application by using Application.LoadPackage() method and then execute it by using the Package.Execute() method.
C#
using Microsoft.SqlServer.Dts.Runtime;
Package pkg = new Package();
Application app = new Application();
//load and execute package
pkg = app.LoadPackage(@"
pkg.Execute();
VB.NET
Imports Microsoft.SqlServer.Dts.Runtime
Package pkg= new Packagr()
Application app= new Application()
'load and package
pkg = app.LoadPackage(“
pkg.Execute()
Find Primary Key of a SQL Server Table
Here’s a quick way to find the primary key of a SQL Server 2005/2008 Table using TABLE_CONSTRAINTS
USE Northwind
go
SELECT ISKC.TABLE_SCHEMA, ISKC.TABLE_NAME, ISKC.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS ISTC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ISKC
ON ISTC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
ISTC.CONSTRAINT_NAME = ISKC.CONSTRAINT_NAME
WHERE ISKC.TABLE_NAME = 'Employees'
ORDER BY ISKC.TABLE_NAME, ISKC.ORDINAL_POSITION
USE Northwind
go
SELECT ISKC.TABLE_SCHEMA, ISKC.TABLE_NAME, ISKC.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS ISTC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ISKC
ON ISTC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
ISTC.CONSTRAINT_NAME = ISKC.CONSTRAINT_NAME
WHERE ISKC.TABLE_NAME = 'Employees'
ORDER BY ISKC.TABLE_NAME, ISKC.ORDINAL_POSITION
Tuesday, 21 December 2010
Auto Generate AlphaNumeric ID’s in a SQL Server Table
I was recently working on a query to auto generate a Column ID based on an IDENTITY column. I used computed columns to achieve this. If you too have a similar requirement, then here’s the query.
DECLARE @TT TABLE (
CandID as 'C-' + RIGHT('000' + CONVERT(varchar, CourseID),5),
CourseID int IDENTITY(1,1),
ReferrerName varchar(10)
)
INSERT INTO @TT VALUES ('Dhiraj')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Dhiraj')
SELECT * FROM @TT
OUTPUT
Observe how the values of the CandID column are autogenerated based on the values if the CourseID column.
DECLARE @TT TABLE (
CandID as 'C-' + RIGHT('000' + CONVERT(varchar, CourseID),5),
CourseID int IDENTITY(1,1),
ReferrerName varchar(10)
)
INSERT INTO @TT VALUES ('Dhiraj')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Kelly')
INSERT INTO @TT VALUES ('Jack')
INSERT INTO @TT VALUES ('Dhiraj')
SELECT * FROM @TT
OUTPUT
Observe how the values of the CandID column are autogenerated based on the values if the CourseID column.
List all the Weekends of the Current Year using SQL Server
Sometime back, I had posted to Find the WeekEnds in this Quarter or Year using SQL Server 2005/2008. An anonymous user wrote back asking if there was a way to determine the first and last day of the current year and rewrite the same query. Well here it is:
DECLARE @StrtDate datetime
DECLARE @EndDate datetime
SELECT @StrtDate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
SELECT @EndDate = DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))
;WITH CTE (weekends)
AS
(
SELECT @StrtDate
UNION ALL
SELECT DATEADD(d,1,weekends)
FROM CTE
WHERE weekends < @EndDate
)
SELECT weekends from CTE
WHERE DATENAME(dw,weekends)
In ('Saturday', 'Sunday')
DECLARE @StrtDate datetime
DECLARE @EndDate datetime
SELECT @StrtDate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
SELECT @EndDate = DATEADD(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))
;WITH CTE (weekends)
AS
(
SELECT @StrtDate
UNION ALL
SELECT DATEADD(d,1,weekends)
FROM CTE
WHERE weekends < @EndDate
)
SELECT weekends from CTE
WHERE DATENAME(dw,weekends)
In ('Saturday', 'Sunday')
Find Maximum Value in each Row – SQL Server
Here’s a simple and efficient way to find the maximum value in each row using SQL Server UNPIVOT
DECLARE @t table (id int PRIMARY KEY, col1 int, col2 int, col3 int)
-- SAMPLE DATA
INSERT INTO @t SELECT 1, 45, 2, 14
INSERT INTO @t SELECT 2, 8, 1, 12
INSERT INTO @t SELECT 3, 21, 20, 8
INSERT INTO @t SELECT 4, 8, 5, 2
INSERT INTO @t SELECT 5, 23, 49, 7
INSERT INTO @t SELECT 6, 19, 7, 5
INSERT INTO @t SELECT 7, 7, 7, 2
INSERT INTO @t SELECT 8, 14, 17, 2
-- QUERY
SELECT id, MAX(col) AS maxValue
FROM
(
SELECT id, col FROM @t
UNPIVOT
(col FOR ListofColumns IN (col1,col2,col3))
AS unpivott) AS p
GROUP BY id
DECLARE @t table (id int PRIMARY KEY, col1 int, col2 int, col3 int)
-- SAMPLE DATA
INSERT INTO @t SELECT 1, 45, 2, 14
INSERT INTO @t SELECT 2, 8, 1, 12
INSERT INTO @t SELECT 3, 21, 20, 8
INSERT INTO @t SELECT 4, 8, 5, 2
INSERT INTO @t SELECT 5, 23, 49, 7
INSERT INTO @t SELECT 6, 19, 7, 5
INSERT INTO @t SELECT 7, 7, 7, 2
INSERT INTO @t SELECT 8, 14, 17, 2
-- QUERY
SELECT id, MAX(col) AS maxValue
FROM
(
SELECT id, col FROM @t
UNPIVOT
(col FOR ListofColumns IN (col1,col2,col3))
AS unpivott) AS p
GROUP BY id
NVARCHAR(MAX) VS NTEXT in SQL Server
Here are some differences between nvarchar(max)/nvarchar and ntext in SQL Server
Nvarchar(max)/Nvarchar()
Ntext
Nvarchar(max) is supported only from version 2005 onwards Ntext is available in prior versions too
You can specify the length in advance for nvarchar datatype i.e. nvarchar(100), nvarchar(4000) You can't specify the length for Ntext datatype
All types of string functions such as left, right, len, etc. can be used for Nvarchar data type Only limited functions like substring, datalength are used for Ntext datatype
Nvarchar datatype can be used in local variables Ntext can't be used in local variables
Nvarchar datatype is available from version 2005 onwards and will be supported for further releases Ntext will be removed from future release of SQL Server and will be substituted by nvarchar(max) datatype
In one of the upcoming posts, I will tell you how to convert an Ntext column to Nvarchar(max) and some points to consider while doing so.
Nvarchar(max)/Nvarchar()
Ntext
Nvarchar(max) is supported only from version 2005 onwards Ntext is available in prior versions too
You can specify the length in advance for nvarchar datatype i.e. nvarchar(100), nvarchar(4000) You can't specify the length for Ntext datatype
All types of string functions such as left, right, len, etc. can be used for Nvarchar data type Only limited functions like substring, datalength are used for Ntext datatype
Nvarchar datatype can be used in local variables Ntext can't be used in local variables
Nvarchar datatype is available from version 2005 onwards and will be supported for further releases Ntext will be removed from future release of SQL Server and will be substituted by nvarchar(max) datatype
In one of the upcoming posts, I will tell you how to convert an Ntext column to Nvarchar(max) and some points to consider while doing so.
Temp Table VS Table Variable in SQL Server
Here are some differences between Temp Table and Table Variable in SQL Server
Temp Table Table Variable
Temp table is valid for a session.
For eg: when you run the following code
create table #temp(i int)
insert into #temp select 345
Go
create table #temp(i int)
insert into #temp select 345
Go
you will get an error Table variable has a statement-level scope. ie as soon as you execute the statement the scope is lost
For eg: when you run the following code
declare @t table(i int)
insert into @t select 45
GO
declare @t table(i int)
insert into @t select 45
GO
you will not get an error
It is possible to alter the temp table to add columns, idexes,etc It is not possible to alter a table variable
It is possible to truncate a temp table It is not possible to truncate a table variable
SELECT INTO method can be used for temp table
SELECT * INTO #temp from your_table
SELECT INTO method cannot be used for table variable. You get error for the following
SELECT * INTO @t from your_table
Temp table can be useful when you have a large amount of data For small set of data, table variables can be useful
Temp Table Table Variable
Temp table is valid for a session.
For eg: when you run the following code
create table #temp(i int)
insert into #temp select 345
Go
create table #temp(i int)
insert into #temp select 345
Go
you will get an error Table variable has a statement-level scope. ie as soon as you execute the statement the scope is lost
For eg: when you run the following code
declare @t table(i int)
insert into @t select 45
GO
declare @t table(i int)
insert into @t select 45
GO
you will not get an error
It is possible to alter the temp table to add columns, idexes,etc It is not possible to alter a table variable
It is possible to truncate a temp table It is not possible to truncate a table variable
SELECT INTO method can be used for temp table
SELECT * INTO #temp from your_table
SELECT INTO method cannot be used for table variable. You get error for the following
SELECT * INTO @t from your_table
Temp table can be useful when you have a large amount of data For small set of data, table variables can be useful
When to use STUFF instead of REPLACE – SQL Server
The STUFF function in SQL Server ‘inserts’ a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
REPLACE on the other hand replaces all occurrences of a specified string value with another string value.
The STUFF function is useful when you are not sure of the characters to replace, but you do know the position of the character. I saw an excellent implementation of this function over here by GMastros and Even. Here’s the code with a practical usage of STUFF:
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression = '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = STUFF(@String, PatIndex(@MatchExpression,
@String), 1, '')
RETURN @String
END
SELECT dbo.fn_StripCharacters('a1$%s#22d&39#f4$', '^a-z')
as OnlyAlphabets
OUTPUT
REPLACE on the other hand replaces all occurrences of a specified string value with another string value.
The STUFF function is useful when you are not sure of the characters to replace, but you do know the position of the character. I saw an excellent implementation of this function over here by GMastros and Even. Here’s the code with a practical usage of STUFF:
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression = '%['+@MatchExpression+']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = STUFF(@String, PatIndex(@MatchExpression,
@String), 1, '')
RETURN @String
END
SELECT dbo.fn_StripCharacters('a1$%s#22d&39#f4$', '^a-z')
as OnlyAlphabets
OUTPUT
Make a T-SQL Query Sleep for a certain amount of time
At times, in test scenarios, you may want to emulate that a transaction or a T-SQL query takes ‘x’ amount of time. Well that is possible (although not with precision) in SQL Server (2005/2008) using the WAITFOR command.
Here’s an example of delaying query execution till a specified time interval has elapsed – let us say 10 seconds
USE Northwind
WAITFOR DELAY '00:00:10';
SELECT LASTNAME, FIRSTNAME FROM Employees;
As you can see, we used the WAITFOR statement with a DELAY option to delay code execution for 10 seconds.
There are a couple of things you should note about the WAITFOR statement
- WAITFOR does not accept date values, only time values are allowed
- In some scenarios, the delay specified by you may not exactly the same as the delay caused by WAITFOR
Here’s an example of delaying query execution till a specified time interval has elapsed – let us say 10 seconds
USE Northwind
WAITFOR DELAY '00:00:10';
SELECT LASTNAME, FIRSTNAME FROM Employees;
As you can see, we used the WAITFOR statement with a DELAY option to delay code execution for 10 seconds.
There are a couple of things you should note about the WAITFOR statement
- WAITFOR does not accept date values, only time values are allowed
- In some scenarios, the delay specified by you may not exactly the same as the delay caused by WAITFOR
Duplicate a Column in SQL Server
Here’s a very simple but useful post that shows how to duplicate an existing column in SQL Server.
Assuming we have the following table structure with data:
CREATE TABLE #TmpTable(ColA varchar(10))
INSERT INTO #TmpTable SELECT 'ValueOne'
INSERT INTO #TmpTable SELECT 'ValueTwo'
INSERT INTO #TmpTable SELECT 'ValueThree'
INSERT INTO #TmpTable SELECT 'ValueFour'
INSERT INTO #TmpTable SELECT 'ValueFive'
INSERT INTO #TmpTable SELECT 'ValueSix'
Now to duplicate ColA, use the following code:
ALTER TABLE #TmpTable
ADD ColB varchar(10)
GO
UPDATE #TmpTable
SET ColB = ColA
GO
SELECT * FROM #TmpTable
As you can see, we have added a new column to the Table and used a simple Update statement to duplicate ColA. If the column already exists, you can avoid the ALTER TABLE.
OUTPUT
Assuming we have the following table structure with data:
CREATE TABLE #TmpTable(ColA varchar(10))
INSERT INTO #TmpTable SELECT 'ValueOne'
INSERT INTO #TmpTable SELECT 'ValueTwo'
INSERT INTO #TmpTable SELECT 'ValueThree'
INSERT INTO #TmpTable SELECT 'ValueFour'
INSERT INTO #TmpTable SELECT 'ValueFive'
INSERT INTO #TmpTable SELECT 'ValueSix'
Now to duplicate ColA, use the following code:
ALTER TABLE #TmpTable
ADD ColB varchar(10)
GO
UPDATE #TmpTable
SET ColB = ColA
GO
SELECT * FROM #TmpTable
As you can see, we have added a new column to the Table and used a simple Update statement to duplicate ColA. If the column already exists, you can avoid the ALTER TABLE.
OUTPUT
Tuesday, 14 December 2010
How do I setup the breakpoints in SSIS?
In the BIDS, navigate to the control flow interface. Right click on the object where you want to set the breakpoint and select the 'Edit Breakpoints...' option. This will display the screen shot listed next section.
What are the conditions that I can set for a breakpoint in SSIS?
Breakpoints have 10 unique conditions when they can be invoked. The breakpoints can also be used in combination. One typical example is using both the OnPreExecute and OnPostExecute events to determine the status of the variables as the process begins and ends.
What other breakpoint parameters can be configured?
For each of the breakpoints, the Hit Count Type and Hit Count can be configured.
The Hit Count Type values are:
* Always
* Hit count greater than or equal to
* Hit count multiple
* Hit count equals
The Hit Count value is an integer greater than 1.
The image below shows an example of these options, but is only an example not a probable configuration.
Next Steps
What are the conditions that I can set for a breakpoint in SSIS?
Breakpoints have 10 unique conditions when they can be invoked. The breakpoints can also be used in combination. One typical example is using both the OnPreExecute and OnPostExecute events to determine the status of the variables as the process begins and ends.
What other breakpoint parameters can be configured?
For each of the breakpoints, the Hit Count Type and Hit Count can be configured.
The Hit Count Type values are:
* Always
* Hit count greater than or equal to
* Hit count multiple
* Hit count equals
The Hit Count value is an integer greater than 1.
The image below shows an example of these options, but is only an example not a probable configuration.
Next Steps
Subscribe to:
Posts (Atom)