All Articles - SQL Authority with Pinal Dave

SQL SERVER – Introduction to Heap Structure – What is Heap?

March 30, 2008

Sometime simple questions are very interesting. A day ago, jr. developer asked me question : What is Heap? In SQL Server 2005 data is stored within tables. Data within a table is grouped together into allocation unites based on their column data types, what it means is one kind of…

SQL SERVER – 2005 – List All Column With Identity Key In Specific Database

March 29, 2008

Pinal Dave

SQL Tips and Tricks

SQL Identity, SQL Index, SQL Scripts, SQL Server, SQL System Table

Question I received in Email : How to list all the columns in the database which are used as identity key in my database?

All Articles dm_exec_query_optimizer_info-600x400

SQL SERVER – Introduction to sys.dm_exec_query_optimizer_info

March 28, 2008

Pinal Dave

SQL Tips and Tricks

SQL DMV, SQL Scripts, SQL Server, SQL System Table

Many times when I am just bored I surf Book On Line for SQL Server 2005. Almost all the time I find something new which makes me believe that I have lot to learn and there are so many things I am not aware of. Today I found system catalog view sys.dm_exec_query_optimizer_info. I just enjoyed reading about it and now I will share this with you.

SQL SERVER – 2005 – Find Index Fragmentation Details – Slow Index Performance

March 27, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts, SQL System Table

Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. After I reorganized index it worked perfectly fine. Here is the quick script I…

SQLAuthority News – Few Links About SQLAuthority

March 26, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks, SQLAuthority News

Database, DBA, Software Development, SQL Jobs

I have listed few important links of SQLAuthority.com, I still receive some repeated questions. I do my best to respond to all of my readers, however, most of the time I am sending them link to one of my previously written article. Many times most of the answers can be…

SQL SERVER – Simple Puzzle Using Union and Union All – Answer

March 25, 2008

Pinal Dave

SQL, SQL Puzzle, SQL Server, SQL Tips and Tricks

Database, SQL Joins, SQL Scripts

Yesterday I posted a puzzle SQL SERVER – Simple Puzzle Using Union and Union All, today we will see the answer of this. Following image explains the answer of puzzle. You can read the explanation of why this is answer read my previous article SQL SERVER – Union vs. Union…

SQL SERVER – Simple Puzzle Using Union and Union All

March 24, 2008

Pinal Dave

SQL, SQL Puzzle, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Utility

I often get request to write puzzles using SQL Server. Today, I am presenting one very simple but very interesting puzzle. What will be the output of following two SQL Scripts. First try to answer without running this two script in Query Editor. Script 1 SELECT 1 UNION ALL (SELECT…

SQL SERVER – 2005 – Mechanisms to Ensure Integrity and Consistency of Databases – Locking and Row Versioning

March 23, 2008

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Database, SQL Lock

Today I was going through Book On Line while researching something, I come across one interesting small article about two mechanisms to ensure integrity and consistency of databases – 1) Locking 2) Row Versioning Let us see their definition from Book Online Itself. Locking Each transaction requests locks of different…

SQL SERVER – 2005 – Find Highest / Most Used Stored Procedure

March 22, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure, SQL System Table

How many times we all DBA’s might have wonder which stored procedure is executing most in the database? I have wondered it often and I have written following small script which gives me answer to my above questions. I am also retrieving few additional data along with the highest used…

SQL SERVER – Introduction to Live Lock – What is Live Lock?

March 21, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Software Development, SQL Lock

Some questions are very interesting to answer. I just received following question in Email. What is Live Lock? A Live lock is one, where a request for exclusive lock is denied continuously because a series of overlapping shared locks keeps on interfering each other and to adapt from each other…