SQL SERVER – Weekly Series – Memory Lane – #032
- by Pinal Dave
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.
2007
Complete Series of Database Coding Standards and Guidelines
SQL SERVER Database Coding Standards and Guidelines – Introduction
SQL SERVER – Database Coding Standards and Guidelines – Part 1
SQL SERVER – Database Coding Standards and Guidelines – Part 2
SQL SERVER Database Coding Standards and Guidelines Complete List Download
Explanation and Example – SELF JOIN
When all of the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to Employee information, where the table may have both an Employee’s ID number for each record and also a field that displays the ID number of an Employee’s supervisor or manager. To retrieve the data tables are required to relate/join to itself.
Insert Multiple Records Using One Insert Statement – Use of UNION ALL
This is very interesting question I have received from new developer. How can I insert multiple values in table using only one insert? Now this is interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.
Function to Display Current Week Date and Day – Weekly Calendar
Straight blog post with script to find current week date and day based on the parameters passed in the function.
2008
In my beginning years, I have almost same confusion as many of the developer had in their earlier years. Here are two of the interesting question which I have attempted to answer in my early year. Even if you are experienced developer may be you will still like to read following two questions:
Order Of Column In Index
Order of Conditions in WHERE Clauses
Example of DISTINCT in Aggregate Functions
Have you ever used DISTINCT with the Aggregation Function? Here is a simple example about how users can do it.
Create a Comma Delimited List Using SELECT Clause From Table Column
Straight to script example where I explained how to do something easy and quickly.
Compound Assignment Operators
SQL SERVER 2008 has introduced new concept of Compound Assignment Operators. Compound Assignment Operators are available in many other programming languages for quite some time. Compound Assignment Operators is operator where variables are operated upon and assigned on the same line.
PIVOT and UNPIVOT Table Examples
Here is a very interesting question – the answer to the question can be YES or NO both.
“If we PIVOT any table and UNPIVOT that table do we get our original table?”
Read the blog post to get the explanation of the question above.
2009
What is Interim Table – Simple Definition of Interim Table
The interim table is a table that is generated by joining two tables and not the final result table. In other words, when two tables are joined they create an interim table as resultset but the resultset is not final yet. It may be possible that more tables are about to join on the interim table, and more operations are still to be applied on that table (e.g. Order By, Having etc). Besides, it may be possible that there is no interim table; sometimes final table is what is generated when the query is run.
2010
Stored Procedure and Transactions
If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors. Well, that does not happen in this case, which proves that Stored Procedure does not only provide just the transactional feature to a batch of T-SQL.
Generate Database Script for SQL Azure
When talking about SQL Azure the most common complaint I hear is that the script generated from stand-along SQL Server database is not compatible with SQL Azure. This was true for some time for sure but not any more. If you have SQL Server 2008 R2 installed you can follow the guideline below to generate a script which is compatible with SQL Azure.
Convert IN to EXISTS – Performance Talk
It is NOT necessary that every time when IN is replaced by EXISTS it gives better performance. However, in our case listed above it does for sure give better performance. You can read about this subject in the associated blog post.
Subquery or Join – Various Options – SQL Server Engine Knows the Best
Every single time whenever there is a performance tuning exercise, I hear the conversation from developer where some prefer subquery and some prefer join. In this two part blog post, I explain the same in the detail with examples.
Part 1 | Part 2
Merge Operations – Insert, Update, Delete in Single Execution
MERGE is a new feature that provides an efficient way to do multiple DML operations. In earlier versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions; however, at present, by using the MERGE statement, we can include the logic of such data changes in one statement that even checks when the data is matched and then just update it, and similarly, when the data is unmatched, it is inserted.
2011
Puzzle – Statistics are not updated but are Created Once
Here is the quick scenario about my setup.
Create Table
Insert 1000 Records
Check the Statistics
Now insert 10 times more 10,000 indexes
Check the Statistics – it will be NOT updated – WHY?
Question to You – When to use Function and When to use Stored Procedure
Personally, I believe that they are both different things - they cannot be compared. I can say, it will be like comparing apples and oranges. Each has its own unique use. However, they can be used interchangeably at many times and in real life (i.e., production environment). I have personally seen both of these being used interchangeably many times. This is the precise reason for asking this question.
2012
In year 2012 I had two interesting series ran on the blog. If there is no fun in learning, the learning becomes a burden. For the same reason, I had decided to build a three part quiz around SEQUENCE. The quiz was to identify the next value of the sequence. I encourage all of you to take part in this fun quiz.
Guess the Next Value – Puzzle 1
Guess the Next Value – Puzzle 2
Guess the Next Value – Puzzle 3
Guess the Next Value – Puzzle 4
Simple Example to Configure Resource Governor – Introduction to Resource Governor
Resource Governor is a feature which can manage SQL Server Workload and System Resource Consumption. We can limit the amount of CPU and memory consumption by limiting /governing /throttling on the SQL Server. If there are different workloads running on SQL Server and each of the workload needs different resources or when workloads are competing for resources with each other and affecting the performance of the whole server resource governor is a very important task.
Tricks to Replace SELECT * with Column Names – SQL in Sixty Seconds #017 – Video
Retrieves unnecessary columns and increases network traffic
When a new columns are added views needs to be refreshed manually
Leads to usage of sub-optimal execution plan
Uses clustered index in most of the cases instead of using optimal index
It is difficult to debug
SQL SERVER – Load Generator – Free Tool From CodePlex
The best part of this SQL Server Load Generator is that users can run multiple simultaneous queries again SQL Server using different login account and different application name. The interface of the tool is extremely easy to use and very intuitive as well.
A Puzzle – Swap Value of Column Without Case Statement
Let us assume there is a single column in the table called Gender. The challenge is to write a single update statement which will flip or swap the value in the column. For example if the value in the gender column is ‘male’ swap it with ‘female’ and if the value is ‘female’ swap it with ‘male’.
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
