SQL SERVER – Weekly Series – Memory Lane – #007
- by pinaldave
Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.
2006
Find Stored Procedure Related to Table in Database – Search in All Stored Procedure
In 2006 I wrote a small script which will help user find all the Stored Procedures (SP) which are related to one or more specific tables. This was quite a popular script however, in SQL Server 2012 the same can be achieved using new DMV sys.sql-expression_dependencies. I recently blogged about it over Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies.
2007
SQL SERVER – Versions, CodeNames, Year of Release
1993 – SQL Server 4.21 for Windows NT
1995 – SQL Server 6.0, codenamed SQL95
1996 – SQL Server 6.5, codenamed Hydra
1999 – SQL Server 7.0, codenamed Sphinx
1999 – SQL Server 7.0 OLAP, codenamed Plato
2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
2003 – SQL Server 2000 64-bit, codenamed Liberty
2005 – SQL Server 2005, codenamed Yukon (version 9.0)
2008 – SQL Server 2008, codenamed Katmai (version 10.0)
2011 – SQL Server 2008, codenamed Denali (version 11.0)
Search String in Stored Procedure
Searching sting in the stored procedure is one of the most frequent task developer do. They might be searching for a table, view or any other details. I have written a script to do the same in SQL Server 2000 and SQL Server 2005. This is worth bookmarking blog post. There is an alternative way to do the same as well here is the example.
2008
SQL SERVER – Refresh Database Using T-SQL
NO! Some of the questions have a single answer NO! You may want to read the question in the original blog post. I had a great time saying No!
SQL SERVER – Delete Backup History – Cleanup Backup History
SQL Server stores history of all the taken backup forever. History of all the backup is stored in the msdb database. Many times older history is no more required. Following Stored Procedure can be executed with a parameter which takes days of history to keep. In the following example 30 is passed to keep a history of month.
2009
Stored Procedure are Compiled on First Run – SP taking Longer to Run First Time
Is stored procedure pre-compiled? Why the Stored Procedure takes a long time to run for the first time? This is a very common questions often discussed by developers and DBAs. There is an absolutely definite answer but the question has been discussed forever. There is a misconception that stored procedures are pre-compiled. They are not pre-compiled, but compiled only during the first run. For every subsequent runs, it is for sure pre-compiled. Read the entire article for example and demonstration.
Removing Key Lookup – Seek Predicate – Predicate – An Interesting Observation Related to Datatypes
This is one of the most important performance tuning lesson on my blog. I suggest this weekend you spend time reading them and let me know what you think about the concepts which I have demonstrated in the four part series.
Part 1 | Part 2 | Part 3 | Part 4
Seek Predicate is the operation that describes the b-tree portion of the Seek. Predicate is the operation that describes the additional filter using non-key columns. Based on the description, it is very clear that Seek Predicate is better than Predicate as it searches indexes whereas in Predicate, the search is on non-key columns – which implies that the search is on the data in page files itself.
Policy Based Management – Create, Evaluate and Fix Policies
This article will cover the most spectacular feature of SQL Server – Policy-based management and how the configuration of SQL Server with policy-based management architecture can make a powerful difference. Policy based management is loaded with several advantages. It can help you implement various policies for reliable configuration of the system. It also provides additional administration assistance to DBAs and helps them effortlessly manage various tasks of SQL Server across the enterprise.
2010
Recycle Error Log – Create New Log file without Server Restart
Once I observed a DBA to restaring the SQL Server when he needed new error log file. This was funny and sad both at the same time. There is no need to restart the server to create a new log file or recycle the log file. You can run sp_cycle_errorlog and achieve the same result.
Get Database Backup History for a Single Database
Simple but effective script!
Reducing CXPACKET Wait Stats for High Transactional Database
The subject is very complex and I have done my best to simplify the concept. In simpler words, when a parallel operation is created for SQL Query, there are multiple threads for a single query. Each query deals with a different set of the data (or rows). Due to some reasons, one or more of the threads lag behind, creating the CXPACKET Wait Stat. Threads which came first have to wait for the slower thread to finish. The Wait by a specific completed thread is called CXPACKET Wait Stat.
Information Related to DATETIME and DATETIME2
There are quite a lot of confusion with DATETIME and DATETIME2. DATETIME2 is also one of the underutilized datatype of SQL Server. In this blog post I have written a follow up of the my earlier datetime series where I clarify a few of the concepts related to datetime.
Difference Between GETDATE and SYSDATETIME
Difference Between DATETIME and DATETIME2 – WITH GETDATE
Difference Between DATETIME and DATETIME2
2011
Introduction to CUME_DIST – Analytic Functions Introduced in SQL Server 2012
SQL Server 2012 introduces new analytical function CUME_DIST(). This function provides cumulative distribution value. It will be very difficult to explain this in words so I will attempt small example to explain you this function. Instead of creating new table, I will be using AdventureWorks sample database as most of the developer uses that for experiment.
Introduction to FIRST _VALUE and LAST_VALUE – Analytic Functions Introduced in SQL Server 2012
SQL Server 2012 introduces new analytical functions FIRST_VALUE() and LAST_VALUE(). This function returns first and last value from the list. It will be very difficult to explain this in words so I’d like to attempt to explain its function through a brief example. Instead of creating a new table, I will be using the AdventureWorks sample database as most developers use that for experiment purposes.
OVER clause with FIRST _VALUE and LAST_VALUE – Analytic Functions Introduced in SQL Server 2012 – ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
“Don’t you think there is bug in your first example where FIRST_VALUE is remain same but the LAST_VALUE is changing every line. I think the LAST_VALUE should be the highest value in the windows or set of result.”
Puzzle – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY
You can see that row number 2, 3, 4, and 5 has same SalesOrderID = 43667. The FIRST_VALUE is 78 and LAST_VALUE is 77. Now if these function was working on maximum and minimum value they should have given answer as 77 and 80 respectively instead of 78 and 77. Also the value of FIRST_VALUE is greater than LAST_VALUE 77. Why? Explain in detail.
Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012
SQL Server 2012 introduces new analytical function LEAD() and LAG(). This functions accesses data from a subsequent row (for lead) and previous row (for lag) in the same result set without the use of a self-join . It will be very difficult to explain this in words so I will attempt small example to explain you this function. Instead of creating new table, I will be using AdventureWorks sample database as most of the developer uses that for experiment.
A Real Story of Book Getting ‘Out of Stock’ to A 25% Discount Story Available
Our book was out of stock in 48 hours of it was arrived in stock! We got call from the online store with a request for more copies within 12 hours. But we had printed only as many as we had sent them. There were no extra copies. We finally talked to the printer to get more copies. However, due to festivals and holidays the copies could not be shipped to the online retailer for two days. We knew for sure that they were going to be out of the book for 48 hours. This is the story of how we overcame that situation!
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
