Find Out The Parameters Of A Stored Procedure in SQL Server 2005/2008

Recently while working on an application that heavily uses Stored Procedures, I had to often open up SQL Server Management Studio/ Visual Studio Server Explorer to physically check the parameters of a Stored Procedure. This became cumbersome at times since the procs were in different databases and I wish there were a query that returned me the Stored Procedure parameters, as soon as I supply it with the stored procedure name.

If you have been looking out for something similar, here’s what I came up with:

SELECT parm.name AS Parameter,        
typ.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters parm ON sp.object_id = parm.object_id
JOIN sys.types typ ON parm.system_type_id = typ.system_type_id
WHERE sp.name = 'aspnet_Membership_GetUsers'

OUTPUT:

I am a happy man now :)

Free EBook: The Art of XSD SQL Server Schema Collections

SQL Server MVP, Jacob Sebastian has written an EBook "The Art of XSD SQL Server Schema Collections" which is freely available at http://www.red-gate.com/specials/Ebooks/XSD_0409.htm



This book aims at teaching you how to create and use XML Schema collections in SQL Server to validate XML documents. The book does not assume prior knowledge of XSD and teaches you stuff right from the basics.

Those interested in giving feedback about the book or stay updated with new additions can check the following links:

Group: http://beyondrelational.com/groups/the_art_of_xsd/default.aspx

Forum: http://beyondrelational.com/groups/the_art_of_xsd/forum/default.aspx

Blog: http://beyondrelational.com/groups/the_art_of_xsd/blog/default.aspx

Creating and Inserting XML data in SQL Server 2005/2008

With the XML data type introduced in SQL Server 2005/2008, storing XML data is quite simple as you no longer need to now store XML in the database as Binary formats.

Let's quickly see how to create a table with the XML data type and store an XML document in that table.

DECLARE @TT TABLE

(

ID int,

Name varchar(30),

Address XML

)

 

INSERT @TT

SELECT 1, 'Jason', CAST
('<Address Street1="342A NW AXE STREET" PIN="544333"/>' as
XML) UNION ALL

SELECT 2, 'Brooke', CAST
('<Address Street1="71H BRISBON" PIN="565533"/>' as XML)
UNION ALL

SELECT 3, 'Dally', CAST
('<Address Street1="R/F/3 MASON CORNER" PIN="699783"/>' as
XML)

 

SELECT * FROM @TT

As shown in the query above, we created the Address column with the XML data type and inserted XML data using CAST function. The CAST function also ensures that the XML document is well formed. If the XML was not well formed, as in this line shown below,

SELECT 3, 'Dally', CAST
('<Address Street1="R/F/3 MASON CORNER" PIN="699783">' as XML)

then an XML Parsing error would be raised - XML parsing: line 1, character 51, unexpected end of input

You can even add XML Schema definitions to SQL Server and use it to validate XML documents. We will see this feature in one of the forthcoming blog posts.

Find out the permission of the Current Connected User on the database using SQL Server 2005/2008

If you want to find out the permissions of the Current Connected Principal User on the database, then here's how to do so:

SELECT permission_name as Allowed

FROM fn_my_permissions('NORTHWIND', 'DATABASE')

The fn_my_permissions system function returns a list of the permissions effectively granted to the principal on a securable, in our case, the database Northwind. So running the query shown above, will display the permission of the current connected principal



Similarly to view permissions of the principal on a specific table, use this query

SELECT permission_name as Allowed

FROM fn_my_permissions('dbo.Employees', 'object')

If you want to view the permissions of a different principal user, use

EXECUTE AS LOGIN = 'USERNAME'
GO

and then execute the query.

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.000

Finding the date last year based on the current week and weekday using SQL Server

Today is the 21st of May, 2009 and it is a Thursday today. Have you wondered what date was Thursday last year in the same week? Here's a very handy query by Hector which displays the last year's date based on the current weekday and current week

SELECT DATEADD(day, (DATEPART(week, GETDATE()) * 7

+ DATEPART(weekday, GETDATE()))

- (DATEPART(week, DATEADD(year, -1, GETDATE())) * 7

+  DATEPART(weekday, DATEADD(year, -1, GETDATE()))), DATEADD(year, -1, GETDATE())) as LastYearDate

RESULT

LastYearDate

2008-05-22 11:14:26.350

Calculate Number of Working Days in SQL Server

I recently bumped into this handy query from CMS which shows how to calculate the number of working days in this year till now. The working days are assumed to be Monday to Friday and does not include public holidays, which may be added by you later depending on the country you live in.

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '2009/1/01'

SET @EndDate = GETDATE()

SELECT  

(DATEDIFF(dd, @StartDate, @EndDate) + 1)

-(DATEDIFF(wk, @StartDate, @EndDate) * 2) 

-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

Assuming the current date is 'May 20, 2009', the number of working days after this query is 100

Free SQL Server 2008 Developer Training Kit

If you have been looking out to develop web applications making use of the rich data types, programming models and new development paradigms in SQL Server 2008, then here's the ultimate training kit for you.

As mentioned on the Microsoft site, the training kit offers the following benefits:

- Learn how to build web applications that exploit the unique features and capabilities of SQL Server 2008.
- Provides a comprehensive set of presentations, demos and hands-on labs
- Designed for web developers who are already familiar with SQL Server application development.
- Easy to download and install and works on free editions of SQL Server 2008 and Visual Studio 2008.

Get more details about the SQL Server 2008 Developer Kit here

AutoGenerate an AlphaNumeric Sequence in SQL Server

I was recently solving a requirement of a client where the client needed a Unique Alphanumeric Sequence Number to be generated using the following business rules:

UniqueID = Code (TT) + Current Datetime + ID

Here's how I generated the UniqueID

DECLARE @TT TABLE

(

ID  int, CircuitName varchar(10),

UniqueID  AS 'TT' + REPLACE(CONVERT(varchar, GETDATE(),101),'/','')

+ REPLACE(CONVERT(varchar, GETDATE(),108),':','') 

+ CAST(ID as varchar(10))

)

 

INSERT @TT

SELECT 1, 'Circuit 1' UNION ALL

SELECT 2, 'Circuit 2' UNION ALL

SELECT 3, 'Circuit 3' UNION ALL

SELECT 4, 'Circuit 4' UNION ALL

SELECT 5, 'Circuit 5' UNION ALL

SELECT 6, 'Circuit 6' UNION ALL

SELECT 7, 'Circuit 7' UNION ALL

SELECT 8, 'Circuit 8' UNION ALL

SELECT 9, 'Circuit 9' UNION ALL

SELECT 10, 'Circuit 10'

 

SELECT * FROM @TT

OUTPUT

ID    CircuitName    UniqueID

1    Circuit 1    TT051220091517481

2    Circuit 2    TT051220091517482

3    Circuit 3    TT051220091517483

4    Circuit 4    TT051220091517484

5    Circuit 5    TT051220091517485

6    Circuit 6    TT051220091517486

7    Circuit 7    TT051220091517487

8    Circuit 8    TT051220091517488

9    Circuit 9    TT051220091517489

10    Circuit 10    TT0512200915174810

Estimating the Size of Your SQL Server Database, Table and Indexes

Have you been thinking of estimating the Size of your Heap, Database, Tables, Clustered index, Nonclustered index and so on. Here are some important articles on Books Online that share information on how to estimate the size of your objects:

Estimating the Size of a Heap

Estimating the Size of a Database

Estimating the Size of a Table

Estimating the Size of a Clustered Index

Estimating the Size of a Nonclustered Index

Using T-SQL to Display Execution Plans in SQL Server

The Query Analyzer window in Sql Server Management Studio (SSMS) makes it easy to view exection plans. You have to either click on the 'Display Estimated Execution Plan' or 'Include Actual Execution Plan' as shown below:



However if you want to see the execution plans using T-SQL, here's how to do so:

SET SHOWPLAN_ALL ON/OFF returns an estimated execution plan with detailed information about how the statements will be executed, without
executing the query

USE Northwind

GO

 

SET SHOWPLAN_ALL ON

GO

-- First query.

SELECT CustomerID, CompanyName, [Address] from Customers

GO

-- Second query.

SELECT CustomerID, CompanyName, [Address] from Customers

WHERE City LIKE '%S%'

 

GO

SET SHOWPLAN_ALL OFF

GO

Similarly you can also use SET SHOWPLAN_TEXT ON which returns a textual estimated execution plan without running the query or SET SHOWPLAN_XML ON which returns an XML-based estimated execution plan without running the query.



SET STATISTICS PROFILE ON/OFF returns a detailed actual execution plan for a each query, after running the query.

SET STATISTICS PROFILE ON

GO

-- First query.

SELECT CustomerID, CompanyName, [Address] from Customers

GO

-- Second query.

SELECT CustomerID, CompanyName, [Address] from Customers

WHERE City LIKE '%S%'

 

GO

SET STATISTICS PROFILE OFF

GO

Similarly you can also use SET STATISTICS IO ON to get information about IO/Disk Activity while executing the statements and SET STATISTICS TIME ON to display the milliseconds required to parse and compile each statement while executing it

SQL Server 2008 Books You Must Have In Your Collection

Here are some SQL Server 2008 books you 'must have' in your collection. I keep referencing most of these books now and then and find them quite useful. The books are categorized for Developers, Administrators and BI Developers/Administrators

SQL Server 2008 T-SQL

Microsoft® SQL Server® 2008 T-SQL Fundamentals (PRO-Developer) by Itzik Ben-gan

Inside Microsoft® SQL Server® 2008: T-SQL Querying
by Itzik Ben-Gan, Lubor Kollar, Dejan Sarka, and Steve Kass

Pro T-SQL 2008 Programmer’s Guide by Michael Coles

SQL Server 2008 Administration

Microsoft SQL Server 2008 Administrator's Pocket Consultant by William R. Stanek

Microsoft SQL Server 2008 Management and Administration by Ross Mistry

SQL Server 2008 Business Intelligence

Microsoft SQL Server 2008 Reporting Services by Brian Larson

Delivering Business Intelligence with Microsoft SQL Server 2008 By Brian Larson

Microsoft® SQL Server® 2008 Analysis Services Step by Step (Step By Step (Microsoft)) by Scott Cameron

Professional Microsoft SQL Server 2008 Integration Services (Wrox Programmer to Programmer) by Brian Knight, Erik Veerman, Grant Dickinson, and Douglas Hinson

SQL Server 2008 Internals

and not forgetting one of the highly recommended SQL Server 2008 book by Kalen..

Microsoft® SQL Server® 2008 Internals (Pro - Developer) by Kalen Delaney

Find Out Tables in a Database Having Identity Columns

The simplest way I use to find out the tables in SQL Server 2005/2008 having Identity Columns is to use the query over here

USE Northwind

GO

SELECT object_name(o.object_id) AS TableName, c.name as IdentityColumn

FROM sys.columns c JOIN sys.objects  o

ON c.object_id = o.object_id

WHERE is_identity = 1

AND o.type IN ('U')

OUTPUT:

TableName    IdentityColumn

Categories    CategoryID

Shippers    ShipperID

Suppliers    SupplierID

Orders        OrderID

Products    ProductID

Employees    EmployeeID

SQL Server 2005 and 2008 Diagnostics Queries and Configuration Checklist

SQL Server MVP, Glenn Berry recently put together a set of Diagnostic Queries to provide a high level overview of how your SQL Server 2005/2008 is configured and to monitor its performance.

Here are the links to these amazing set of queries:

SQL Server 2005 Diagnostic Queries

SQL Server 2008 Diagnostic Queries

SQL Server 2005 Installation and Configuration Checklist

I hope others find it useful too!

Glenn works as a Database Architect at NewsGator Technologies in Denver, CO. He is renowned SQL Server MVP, and has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. Glenn is also an Adjunct Faculty member at University College -University of Denver, where he has been teaching since 2000. Glenn blogs regularly at http://glennberrysqlperformance.spaces.live.com/blog/

Select Random Records From a Table using TableSample in SQL Server 2005/2008

In my previous post, I had explained how to use the NEWID() to Delete Random Records From a Table Using SQL Server 2005/2008

In this post, I will show how to select random records using the TABLESAMPLE clause and use it in different ways. The TABLESAMPLE clause takes a parameter that can be a percent or a sample number representing rows. This clause samples a percentage of
pages randomly. You will get a different result set each time you run a query with the TABLESAMPLE clause. That means that even if you specify to return 50 rows, you will only get an approximate number of rows, that can be less or more than 50 due to the random sampling logic.

Select X% of Records

Returns different set of rows each time the query is executed

USE Northwind

GO

SELECT * FROM Customers

TABLESAMPLE (50 PERCENT);

Select X% of Records with same random sampling multiple times

Specify REPEATABLE with seed. REPEATABLE clause returns same set of rows every time that it is executed

USE Northwind

GO

SELECT * FROM Customers

TABLESAMPLE (50 PERCENT)

REPEATABLE (100)

Specify Number of Records instead of %(Percentage)

USE Northwind

GO

SELECT * FROM Customers

TABLESAMPLE (50 ROWS)

Important Points about TABLESAMPLE:

1. If you have upgraded from SQL 2000 to SQL Server 2005, make sure that the compatibility level of the database is set to at least 90.

2. TABLESAMPLE cannot be used on derived tables, OPENXML, Inline Table-Valued functions, linked server tables or a View.


Read more on TABLESAMPLE over here http://msdn.microsoft.com/en-us/library/ms189108(SQL.90).aspx

Delete Random Records From a Table Using SQL Server 2005/2008

Sometime back I had written a simple post demonstrating How To Return Random Records From A Table.

Recently I came across a cool query by Peter where he displayed how to do it based on a category. I have modified the query to show you how random records can be displayed from a table, partitioned on a category. We will also see how to delete these records.

SAMPLE DATA

DECLARE    @ErrorLog TABLE

(

    ErrorID INT,

    ErrorCategory INT,

    ErrorDate DATETIME

)

 

INSERT    @ErrorLog

SELECT    101, 1, '20080217' UNION ALL

SELECT    102, 1, '20080217' UNION ALL

SELECT    103, 1, '20080217' UNION ALL

SELECT    104, 1, '20080217' UNION ALL

SELECT    105, 1, '20080217' UNION ALL

SELECT    106, 1, '20080217' UNION ALL

SELECT    107, 2, '20080217' UNION ALL

SELECT    108, 2, '20080217' UNION ALL

SELECT    109, 1, '20080217' UNION ALL

SELECT    110, 2, '20080217' UNION ALL

SELECT    111, 2, '20080217' UNION ALL

SELECT    112, 1, '20080217'

If you observe, there are 8 records of ErrorCategory 1 and 4 records of ErrorCategory 2. In order to display 50% random records from both Error Category 1 and 2, use this query

QUERY to Select Random Records based on a category

SELECT *

FROM   

(

    SELECT   

    ErrorID, ErrorCategory,

    ROW_NUMBER() OVER (PARTITION BY ErrorCategory ORDER BY NEWID()) AS recID,

    COUNT(*) OVER (PARTITION BY ErrorCategory) AS maxID

    FROM    @ErrorLog

) AS TruncTbl

WHERE    .50 * maxID >= recID

RESULTS

ErrorID    ErrorCategory    recID    maxID

102        1                1        8

106        1                2        8

104        1                3        8

103        1                4        8

108        2                1        4

107        2                2        4

Similarly, now if you have to delete random records in a table based on a category, here's how to do so

QUERY to Delete Random Records from a table based on a category

DELETE    TruncTbl

FROM   

(

    SELECT   

    ErrorID,

    ROW_NUMBER() OVER (PARTITION BY ErrorCategory ORDER BY NEWID()) AS recID,

    COUNT(*) OVER (PARTITION BY ErrorCategory) AS maxID

    FROM    @ErrorLog

) AS TruncTbl

WHERE    .50 * maxID >= recID

RESULTS

ErrorID    ErrorCategory    ErrorDate

103        1                2008-02-17 00:00:00.000

104        1                2008-02-17 00:00:00.000

108        2                2008-02-17 00:00:00.000

109        1                2008-02-17 00:00:00.000

111        2                2008-02-17 00:00:00.000

112        1                2008-02-17 00:00:00.000

Observe that Both ErrorCategory 1 and 2 had even set of records (8 and 4 each). If there are odd number of records, the results will differ.

Note: SQL Server 2005/2008 also has the TABLESAMPLE clause that can be used to do random sampling. I will explain this feature in my next post