SQL SERVER – What is the query used in sp_cursorfetch and FETCH API_CURSOR?

In a recent debugging exercise for performance, one of my friend recently posted a question to me:

“When I view activity monitor, under the recent expensive queries and under query column, there I see a “fetch api_cursor0000000000000003”. What is this? How can I know what was running behind this query?

Obviously, the hint from that question was the keywords “Fetch and Cursor”. So I knew what the starting point would be. How many times you have been into situation where you want to track a query but when you capture profiler or use conventional methods of sys.sysprocesses or DBCC INPUTBUFFER you would see something like this:

Cursor 01 SQL SERVER   What is the query used in sp cursorfetch and FETCH API CURSOR?

The same thing in profiler we would look like below:

Cursor 02 SQL SERVER   What is the query used in sp cursorfetch and FETCH API CURSOR?

EXEC sp_cursorfetch 180150003,16,8,1

OR

FETCH API_CURSOR0000000000000001

Note: the number in the query might differ in your environment though. Here is a VBScript code to simulate the problem.

Dim strConnection
Dim MyConnection
Dim MyRecordSet
Dim strSQL
strConnection = “Provider=SQLOLEDB;Data Source=.\SQL2014;Initial Catalog=Master;Integrated Security=SSPI;”
Set MyConnection = CreateObject(“ADODB.Connection”)
MyConnection.Open strConnection
Set MyRecordSet = CreateObject(“ADODB.recordset”)
strSQL = “SELECT TOP 3 * FROM sysobjects”
MyRecordSet.Cursorlocation = 2
MyRecordSet.Open strSQL, MyConnection, 3,3
MyRecordSet.MoveFirst
WHILE NOT MyRecordSet.EOF
MsgBox(MyRecordSet(“name”).value)
MyRecordSet.MoveNext
WEND
MyRecordSet.Close
Set MyRecordSet = Nothing

To reproduce the issue, you can save above code in a file and keep extension .vbs. Then you can run from command prompt. You may need to change value of Source in the connection string to match your environment. Once we run the VB Script, you would get a popup with the table name. If profiler is capture from the beginning, we should see sp_cursoropen which can tell the query. If query is already running then we can use below query

SELECT creation_time,
cursor_id,
c.session_id,
c.properties,
c.creation_time,
c.is_open,
SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, (
(
CASE c.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE c.statement_end_offset
END - c.statement_start_offset) / 2) + 1) AS statement_text
FROM   sys.Dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
CROSS apply sys.Dm_exec_sql_text(c.sql_handle) AS st
GO

Here is the sample execution and as we can see, we can get query text which has opened cursor.

 Cursor 03  SQL SERVER   What is the query used in sp cursorfetch and FETCH API CURSOR?

Note: The cursor_id is same as what we saw in sp_cursorfetch . Additionally, if we capture complete profile, we can see the below output:

Cursor 04 SQL SERVER   What is the query used in sp cursorfetch and FETCH API CURSOR?

Moral of the story: If you are seeing sp_cursorfetch or FETCH API_CURSOR0000000000000001 then either capture complete profiler, since the starting of the query OR use sys.Dm_exec_cursors to get exact query.

Do let me know if you every faced this problem in your environments before. How did you debug the same?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor

I just wrote down following script very quickly for one of the project which I am working on. The requirement of the project was that every index existed in database should be rebuilt with fillfactor of  80. One common question I receive why fillfactor 80, answer is I just think having it 80 will do the job.Fillfactor determines how much percentage of the space on each leaf-level page are filled with data.

The space which is left empty on leaf-level page is not at end of the page but the empty space is reserved between rows of data. This ensures that rearrangement of the data does not happen every time new data rows are inserted.

Following is incorrect image of leaf-level page of fillfactor.

fillfactor1 SQL SERVER   2008   2005   Rebuild Every Index of All Tables of Database   Rebuild Index with FillFactor

Following is correct image of leaf-level page of fillfactor.

fillfactor2 SQL SERVER   2008   2005   Rebuild Every Index of All Tables of Database   Rebuild Index with FillFactor

Let us see the T-SQL script which will rebuild each index of all tables of any particular database. Following script will work with SQL Server 2005 (SP2) and SQL Server 2008. It is simple cursor going over each table and rebuilding every index of database.

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET
@fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET
@sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE
TableCursor
DEALLOCATE TableCursor
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Simple Use of Cursor to Print All Stored Procedures of Database

SQLAuthority Blog reader YordanGeorgiev has submitted very interesting SP, which uses cursor to generate text of all the Stored Procedure of current Database. This task can be done many ways, however, this is also interesting method.

USE AdventureWorks
GO
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET
@getprocName = CURSOR FOR
SELECT
s.name
FROM sysobjects s
WHERE type = 'P'
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC
sp_HelpText @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE
@getprocName
DEALLOCATE @getprocName
GO

Just give this script a try and it will print text of all the SP in your database. If you are using Grid View for Result Pan I suggest to change it to Text View (CTRL+T) to read the text easily.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Guidelines and Coding Standards Complete List Download

SQL SERVER – Guidelines and Coding Standards complete List Download

Coding standards and guidelines are very important for any developer on the path of successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where they should not prevent best practices for coding. They are basically the guidelines that one should follow for better understanding.

The concept behind implementing coding standards and guidelines, is that the consistency and uniformity in programming so that if multiple people are working on the same code, it becomes easier to communicate, share with or understand each other’s work.

With the goal of promoting good coding standards and guidelines I have created document which can guide developers.

SQL SERVER – Guidelines and Coding Standards Part – 1

SQL SERVER – Guidelines and Coding Standards Part – 2

SQL SERVER – Guidelines and Coding Standards complete List Download

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Download SQL Server 2008 Interview Questions and Answers Complete List

UPDATE: This article series has been updated with new interview questions and answers series.

Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server is very large subject and not everything is usually asked in interview. In interview what matters the most is conceptual knowledge and learning attitude.

I have listed all the series in this post so that it can be easily downloaded and used. All the questions are collected and listed in one PDF which is here to download. If you have any question or if you want to add to any of the question please send me mail or write a comment.

SQL SERVER – 2008 – Interview Questions and Answers – Part 1

SQL SERVER – 2008 – Interview Questions and Answers – Part 2

SQL SERVER – 2008 – Interview Questions and Answers – Part 3

SQL SERVER – 2008 – Interview Questions and Answers – Part 4

SQL SERVER – 2008 – Interview Questions and Answers – Part 5

SQL SERVER – 2008 – Interview Questions and Answers – Part 6

SQL SERVER – 2008 – Interview Questions and Answers – Part 7

SQL SERVER – 2008 – Interview Questions and Answers – Part 8

Download SQL Server 2008 Interview Questions and Answers Complete List

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Interview Questions and Answers – Part 2

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

SQL SERVER – 2008 – Interview Questions and Answers Complete List Download

1) General Questions of SQL SERVER

What is Cursor?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order:

  • Declare cursor
  • Open cursor
  • Fetch row from the cursor
  • Process fetched row
  • Close cursor
  • Deallocate cursor (Read More Here)

What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width. (Read More Here)

What is Difference between Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

What is sub-query? Explain properties of sub-query?

Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used. (Read More Here)

What are different Types of Join?

Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Inner Join

A join that displays only the rows that have a match in both joined tables is known as inner Join.  This is the default type of join in the Query and View Designer.

Outer Join

A join that includes rows even if they do not have related rows in the joined table is an Outer Join.  You can create three different outer join to specify the unmatched rows to be included:

  • Left Outer Join: In Left Outer Join all rows in the first-named table i.e. “left” table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.

  • Right Outer Join: In Right Outer Join all rows in the second-named table i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

  • Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.

Self Join

This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join. (Read More Here)

What are primary keys and foreign keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.

Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is User Defined Functions? What kind of User-Defined Functions can be created?

User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

Different Kinds of User-Defined Functions created are:

Scalar User-Defined Function

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

Inline Table-Value User-Defined Function

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets. (Read Here For Example)

What is Identity?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

What is DataWarehousing?

  • Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
  • Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
  • Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.
  • Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.

© Copyright 2000-2009 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Readers Contribution to Site – Simple Example of Cursor

eaders are very important to me. Without their active participation this site would not be the community helping web site. I encourage readers participation and request that you help other users with your knowledge.

I recently come across very good communication between two of blog readers. I want to thank you Imran Mohammed for taking time to answer this question as well many other questions. Expert like Imran makes this world better.

Let us read the question from Anthony from here.

All,

I am using Microsoft SQL 2005 and am trying to create a cursor that will take data from several records and concatenate that data into one record.

For example:

I have three attributes (OrderYear, SeqNumber, Item) from a single table (table name: TSGItems) that I am working with…

The data in TSGItems table looks like this:

OrderYear SeqNumber Item

2008 10001 Senior IRS Lien

2008 10001 IRS Lien

2008 10002 Senior IRS Lien

2008 10003 IRS Lien

2008 10003 Senior IRS Lien

2008 10003 Jr. Lien

2008 10004 HOA Lien

In addition to the concatenation, I am trying to manipulate the data as follows:

1) I would like to order the items alphabetically.

2) I would like to put an “and” before the last concatenation when there are more than 1 Item for each Orderyear and SeqNumber.

3) I would like to put in a “,” between each concatenation when there are 3 or more Item for each Orderyear and SeqNumber (except in between the last two concatenation.

4) I would like this data inserted into a temp table called #TSGItems

When I do a select statement on #TSGItems, I would like the data shown as follow:

OrderYear SeqNumber Item

2008 10001 IRS Lien and Senior IRS Lien

2008 10002 Senior IRS Lien

2008 10003 Jr. Lien, IRS Lien, and Senior IRS Lien

2008 10004 HOA Lien

I thank you for taking the time to look at and attempt to solve my challenge.

-Anthony

Let us read the answer from Imran from here.

@Anthony

Let me tell you I am not a developer and I dont have much developing skills… I am sure there must be another good way of writing sql code for what you have asked…

Below is my code, This does exactly what you described in your post.

CREATE TABLE TGSITEMS ( ORDERYEAR DATETIME, SEQNUMBER INT , ITEM VARCHAR(MAX))
INSERT INTO TGSITEMS (ORDERYEAR, SEQNUMBER, ITEM)
SELECT '2008' ,10001 , 'Senior IRS Lien' UNION ALL
SELECT '2008' , 10001 , 'IRS Lien' UNION ALL
SELECT '2008' , 10002 , 'Senior IRS Lien' UNION ALL
SELECT '2008' ,10003, 'IRS Lien' UNION ALL
SELECT '2008' ,10003 , 'Senior IRS Lien' UNION ALL
SELECT '2008' ,10003, 'Jr. Lien' UNION ALL
SELECT '2008' ,10004, 'HOA Lien'

We created a table TGSITEMS and we inserted all the values which you mentioned in your post.

Then I created two more temporary tables #TGSITEMS and #TGSITEMS1… and with the help of While loop and cursor I got your result.

CREATE TABLE #TGSITEMS ( ORDERYEAR DATETIME, SEQNUMBER INT , ITEM VARCHAR(MAX))
GO
CREATE TABLE #TGSITEMS1 ( ID INT IDENTITY, ITEM VARCHAR(MAX))
GO
DECLARE @VAR INT
DECLARE
@CUR CURSOR
DECLARE
@VAR1 INT
DECLARE
@CMD VARCHAR(1000)
DECLARE @CMD1 VARCHAR(1000)
SET @CUR = CURSOR FOR
SELECT DISTINCT
SEQNUMBER
FROM TGSITEMS
OPEN @CUR
FETCH NEXT
FROM @CUR INTO @VAR
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE
#TGSITEMS1
INSERT INTO #TGSITEMS1 (ITEM) SELECT ITEM FROM TGSITEMS WHERE SEQNUMBER = @VAR ORDER BY ITEM
SET @VAR1 = 1
SET @CMD1 = ''
WHILE @VAR1 <= ( SELECT COUNT(*) FROM #TGSITEMS1 )
BEGIN
SELECT
@CMD = ITEM FROM #TGSITEMS1 WHERE ID = @VAR1
IF @VAR1 <(SELECT COUNT(*) FROM #TGSITEMS1) -1
SET @CMD1 = @CMD1 + @CMD +' , '
ELSE IF @VAR1 = (SELECT COUNT(*) FROM #TGSITEMS1 )-1
SET @CMD1 = @CMD1 + @CMD
ELSE IF (SELECT COUNT(*) FROM #TGSITEMS1 ) = 1
SET @CMD1 = @CMD
ELSE
SET
@CMD1 = @CMD1 + ' and ' + @CMD
SET @VAR1 = @VAR1 + 1
END
INSERT INTO
#TGSITEMS SELECT DISTINCT ORDERYEAR , SEQNUMBER, @CMD1 FROM TGSITEMS WHERE SEQNUMBER = @VAR
FETCH NEXT
FROM @CUR INTO @VAR
END
CLOSE
@CUR
DEALLOCATE @CUR
GO
SELECT DATENAME ( YY, ORDERYEAR)ORDERDATE , SEQNUMBER, ITEM FROM #TGSITEMS
GO
TRUNCATE TABLE #TGSITEMS
GO

Let me tell you this is highly performance consuming process… its upto you if you want to use it…

Hope this helps.
Thanks
Imran.

It was very good comment from Imran and here is Anthony’s response to Imran’s answer.

Thank you Imran,
I really appreciate you taking the time to address this challenge. I have been trying to move away from MS Access and am new to the SQL environment. I agree, what I am asking for is very demanding, but I feel reverting to Access is a step backward. I cannot wait to try the coded at work when I return on Monday.

Cheers
-Anthony

I thank you both of you to using this platform for helping solve technical problems.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Is Cursor Database Object or Datatype

It is commonly believed that cursor are Database Objects. I have always given the definition of cursor as SQL Server cursors are database objects used to manipulate data in a set on a row-by-row basis.

Just a few days ago – Imran one of the active reader of blog asked me question if cursor is database object or datatype? My answer to this question is Cursor is Database Object. However, this question is very very interesting. We define cursor same was as datatypes using DECLARE statement and it can be used same way as any other datatypes.

I would like to see all of your opinion about what do you think about cursor? Database Object or Database Datatype.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2

I have recently received email that I should update SQL SERVER – Simple Example of Cursor with example of AdventureWorks database.

Simple Example of Cursor using AdventureWorks Database is listed here.

USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE
@getProductID CURSOR
SET
@getProductID = CURSOR FOR
SELECT
ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
@ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE
@getProductID
DEALLOCATE @getProductID
GO


Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Best Articles on SQLAuthority.com

SQL SERVER – Cursor to Kill All Process in Database

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure

SQL SERVER – Shrinking Truncate Log File – Log Full

SQL SERVER – Simple Example of Cursor

SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

SQL SERVER – T-SQL Script to find the CD key from Registry

SQL SERVER – Delete Duplicate Records – Rows

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

SQL SERVER – Union vs. Union All – Which is better for performance?

SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

SQL SERVER – Index Seek Vs. Index Scan (Table Scan)

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive

SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005

SQL SERVER – Performance Optimization of SQL Query and FileGroups

SQL SERVER – Search Text Field – CHARINDEX vs PATINDEX

SQL SERVER – 2005 Explanation of TRY…CATCH and ERROR Handling

SQL SERVER – Script to find SQL Server on Network

SQL SERVER – Stored Procedures Advantages and Best Advantage

SQL SERVER – CASE Statement/Expression Examples and Explanation

SQL SERVER – Raid Configuration – RAID 10

SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

SQL SERVER – Six Properties of Relational Tables

SQL SERVER – TRIM() Function – UDF TRIM()

SQL SERVER – Difference between Unique Index vs Unique Constraint

SQL SERVER – 2005 Locking Hints and Examples

SQL SERVER – Good, Better and Best Programming Techniques

SQL SERVER – Random Number Generator Script – SQL Query

SQL SERVER – 2005 TOP Improvements/Enhancements

SQL SERVER – 2005/2000 Examples and Explanation for GOTO

SQL SERVER – Explanation SQL Commando GO

SQL SERVER – 2005 – List all the database

SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

SQL SERVER – Disable Index – Enable Index – ALTER Index

SQL SERVER – 2005 – SSMS Change T-SQL Batch Separator

SQL SERVER – SQL Code Formatter Tools

SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN

SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases

SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

SQL SERVER – 2005 Limiting Result Sets by Using TABLESAMPLE – Examples

SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length

SQL SERVER Database Coding Standards and Guidelines Complete List Download

SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice

SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table

SQL SERVER – Easy Sequence of SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY

SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

SQL SERVER – Explanation and Comparison of NULLIF and ISNULL

SQL SERVER – 2005 Row Overflow Data Explanation

SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

SQL SERVER – 2005 Best Practices Analyzer Tutorial – Sample Example

SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

SQL SERVER – Count Duplicate Records – Rows

SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable

SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday

SQL SERVER – One Thing All DBA Must Know

SQL SERVER – 2005 – List Tables in Database Without Primary Key

SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

SQL SERVER – UDF – Validate Integer Function

SQL SERVER – What is SQL? How to pronounce SQL?

SQL SERVER – 2005 – Difference and Similarity Between NEWSEQUENTIALID() and NEWID()

SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop

SQL SERVER – Find Last Day of Any Month – Current Previous Next

SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code

SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database

SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

SQL SERVER – 2005 – Introduction and Explanation to sqlcmd

SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt

SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

SQL SERVER – UDF – Validate Positive Integer Function – Validate Natural Integer Function

SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE

SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

SQL SERVER – Three T-SQL Script to Create Primary Keys on Table

Reference : Pinal Dave (http://blog.SQLAuthority.com)