SQL SERVER – Weekly Series – Memory Lane – #026

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.


SQL Server Interview Questions and Answers Complete List Download
SQL Server interview questions and answers is very crucial for any beginners. Some use this as a reference for future and some use it for refreshing the technology. Well, anyway, this is one of the most popular download on this blog.

@@DATEFIRST and SET DATEFIRST Relations and Usage
The master database’s syslanguages table has a DateFirst column that defines the first day of the week for a particular language. SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as the first day of the week using DATEFIRST.

Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index
In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key columns to the leaf level of the non-clustered index. Non-key columns, can help to create cover indexes.By including non-key columns, you can create non-clustered indexes that cover more queries. The Database Engine does not consider non-key columns when calculating the number of index key columns or index key size.

Query to Find Seed Values, Increment Values and Current Identity Column value of the table
Script in the blog will return all the tables which has identity column. It will also return the Seed Values, Increment Values and Current Identity Column value of the table.

TRIM() Function – UDF TRIM()
A very interesting blog post how we can trim any column value in database.

Take Off Line or Detach Database
Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds.

Difference Between Unique Index vs Unique Constraint
There is no difference between Unique Index and Unique Constraint. Even though their syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys.

SELECT vs. SET Performance Comparison
SET is the ANSI standard for variable assignment, SELECT is not. SET can only assign one variable at a time, SELECT can make multiple assignments at once – that gives SELECT slight speed advantage over SET.

Query to Retrieve the Nth Maximum Value
A very popular script on my blog. I am sure you have faced the similar situation in future.


Better Performance – LEFT JOIN or NOT IN?
First of all answer this question : Which method of T-SQL is better for performance LEFT JOIN or NOT IN when writing a query? The answer is : It depends! It all depends on what kind of data is and what kind query it is etc. In that case just for fun guess one option LEFT JOIN or NOT IN.

Optimization Rules of Thumb – Best Practices
There are few rules for optimizing slow running query. Let us look at them one by one to see how it can help. I started with first six suggestions and later on I asked users to come up with the seventh suggestion. One of the readers actually came up with an entire blog post based on my earlier article. Read that here: Optimization Rules of Thumb – Best Practices – Reader’s Article


Starting the SQL Journey – How Did I Get Started With SQL?
This is one of the most interesting blog post, I keep on reading it again and again. I started my career from Las Vegas and currently I am in Bangalore. My journey has been extremely long and it is almost 7 years old journey. I strongly suggest that everyone who is interested to know how I get here and what I have been doing, please read this blog post about me.



Create Primary Key with Specific Name when Creating Table
Often primary keys are created with a default name and it is a good idea that we create primary keys with specific name so it helps readability and user can directly know lots of information if he/she is familiar with the naming convention.

Update Statistics are Sampled By Default
Question: Are the statistics sampled by default?
Answer: Yes. The sampling rate can be specified by the user and it can be anywhere between a very low value to 100%.

Attach mdf file without ldf file in Database
If you have only MDF file of the database it is absolutely possible to restore it without LDF file as well. Read this interesting story where I explain how we can do it.


Prevent Constraint to Allow NULL
Here is a quick script which describes how to create a constraint which allows NULL.

Using Decode in SQL Server
There is no DECODE function in SQL Server, one has to use a CASE statement to simulate this one.

Very simple straight to the script blog post.


Working with FileTables in SQL Server 2012
This is a very interesting subject and I have written a three part blog series on this subject. I recommend everyone to read each of these three parts to understand the subject. Part 1 | Part 2 | Part 3

Do you want to learn SQL Server? Here are three excellent links about this subject where we have taken five different articles from Joes 2 Pros book series.

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s