Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Index, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQLServer, T SQL, Technology on November 18, 2009 | 7 Comments »
It is very easy to find out some basic details of any table using the following Stored Procedure.
USE AdventureWorks
GO
EXEC sp_spaceused [HumanResources.Shift]
GO
Above query will return following resultset
The above SP provides basic details such as rows, data size in table, and Index size of all the indexes on the table.
If we look at this carefully, a total [...]
Read Full Post »
Posted in Pinal Dave, SQL, SQL Authority, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology, tagged SQL Cache on November 8, 2009 | 5 Comments »
During the PASS summit, one of the attendees asked me the following question.
Why the Stored Procedure takes long time to run for first time?
The reason for the same is because Stored Procedures are compiled when it runs first time. When I answered the same, he replied that Stored Procedures are pre-compiled, and this should not [...]
Read Full Post »
Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQLServer, T SQL, Technology on September 23, 2009 | 9 Comments »
I recently got many emails requesting to write a simple article. I also got a request to explain different ways to insert the values from a stored procedure into a table. Let us quickly look at the conventional way of doing the same.
Please note that this only works with the stored procedure with only one [...]
Read Full Post »
Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Optimization, SQL Performance, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on September 17, 2009 | 5 Comments »
In one of my recent articles, I mentioned the use of Table Valued Function (TVF) instead of Stored Procedure (SP). I received a follow up email asking what type of SP can be converted into a TVF. This is indeed a very interesting question! In fact, not all the SPs qualify to be converted to [...]
Read Full Post »
Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Documentation, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Stored Procedure, SQL Tips and Tricks, SQL Utility, SQL White Papers, T SQL, Technology, tagged SQL Cache, SQL Profiler on September 14, 2009 | 3 Comments »
Last week, I had published details regarding SQL SERVER – Plan Caching in SQL Server 2008 by Greg Low on this blog. Similar to any other white paper, I have read this paper very carefully and enjoyed reading it. One particular topic in the white paper that caught my attention is definition of schema change. [...]
Read Full Post »
When I am not blogging, I am typically working on SQL Server Optimization projects. Time and again, I only have access to SQL Server Management Studio that I can remotely connect to server but do not have access to Operating System, and it works just fine.
At one point in optimization project, I have to decide [...]
Read Full Post »
Posted in Pinal Dave, Readers Contribution, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology, tagged Identity on July 27, 2009 | 28 Comments »
The best part of any blog is when readers ask each other questions. Better still, is when a reader takes the time to provide a detailed response.
A few days ago, one of my readers, Yasmin, asked a very interesting question:
How we can find the list of tables whose identity was missed (not is sequential order) [...]
Read Full Post »
Posted in Pinal Dave, Readers Question, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged undocumented SQL on April 26, 2009 | 7 Comments »
Today we will go over very simple script which will list all the tables for all the database.
sp_msforeachdb ’select ”?” AS db, * from [?].sys.tables’
Update: Based on comments received below I have updated this article. Thank you to all the readers. This is good example where something small like this have good participation from readers.
Reference : Pinal Dave (http://www.SQLAuthority.com)
Read Full Post »
Posted in Best Practices, Pinal Dave, SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table, SQL Tips and Tricks, T SQL, Technology on March 21, 2009 | 9 Comments »
Every batch (T-SQL, SP etc) when ran creates execution plan which is stored in system for re-use. Due to this reason large number of query plans are stored in system. However, there are plenty of plans which are only used once and have never re-used again. One time ran batch plans wastes memory and resources.
SQL [...]
Read Full Post »
Posted in Pinal Dave, Readers Contribution, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Utility, T SQL, Technology on February 19, 2009 | 4 Comments »
Just a day before I received wonderful T-SQL Script from SQL Server Expert Imran Mohammed. What I am really impressed from this script is additional feature of changing recovery mode when SQL Server is enabling and disabling Index. When index are enabled changing SQL Server Recovery mode to simple makes whole operation faster. This stored [...]
Read Full Post »
pinaldave
