SQL SERVER – Weekly Series – Memory Lane – #031
- 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
Find Table without Clustered Index – Find Table with no Primary Key
Clustered index is very important concept for any table. They impact the performance very heavily. Here is a quick script to find tables without a clustered index.
Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types
Question: “Is VARCHAR (MAX) big enough to store the TEXT field?”
Answer: “Yes, VARCHAR(MAX) is big enough to accommodate TEXT field. TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in a future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommended to use new data types which are VARHCAR (MAX), NVARCHAR (MAX) and VARBINARY (MAX).”
Limiting Result Sets by Using TABLESAMPLE – Examples
Introduced in SQL Server 2005, TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.
User Defined Functions (UDF) Limitations
UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF can not do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF). However, this blog post is a good read to know what are the limitations of UDF.
Change Database Compatible Level – Backward Compatibility
For a long time SQL Server stayed on the compatibility level of 80 which is of SQL Server 2000. However, as soon as SQL Server 2005 introduced the issue of compatibility was quite a major issue. Since that time MS has been releasing the versions at every 2-3 years, changing compatibility is a ever popular topic. In this blog post, we learn how we can do the same using T-SQL. We can also do the same using SSMS and here is the blog post for the same: Change Database Compatible Level – Backward Compatibility – Part 2 – Management Studio.
Constraint on VARCHAR(MAX) Field To Limit It Certain Length
How can I limit the VARCHAR(MAX) field with maximum length of 12500 characters only. His Question was valid as our application was allowed 12500 characters. First of all – this requirement is bit strange but if someone wants to do the same, they can do it as described in this blog post.
2008
UNPIVOT Table Example
Understanding UNPIVOT can be very complicated at times. In this blog post, I have attempted to explain the same concept in very simple words.
Create Default Constraint Over Table Column
A simple straight to script blog post – I still use this blog quite many times for my own reference.
UDF – Get the Day of the Week Function
It took me 4 iteration to find this very simple function which can immediately get the day of the week in a single line.
2009
Find Hostname and Current Logged In User Name
There are two tricks listed in this blog post where users can find out the hostname and current logged user name immediately and very easily.
Interesting Observation of Logon Trigger On All Servers
When I was doing a project, I made an interesting observation of executing a logon trigger multiple times. It was absolutely unexpected for me! As I was logging only once, naturally, I was expecting the entry only once. However, it did it multiple times on different threads – indeed an eccentric phenomenon at first sight!
Difference Between Candidate Keys and Primary Key
One needs to be very careful in selecting the Primary Key as an incorrect selection can adversely impact the database architect and future normalization. For a Candidate Key to qualify as a Primary Key, it should be Non-NULL and unique in any domain. I have observed quite often that Primary Keys are seldom changed. I would like to have your feedback on not changing a Primary Key.
Create Multiple Filegroup For Single Database
Why should one create multiple file group for any database and what are the advantages of the same. In this blog post, I explain the same in detail.
List All Objects Created on All Filegroups in Database
In this blog post we discuss the essential question – “How can I find which object belongs to which filegroup. Is there any way to know this?”
2010
DATE and TIME in SQL Server 2008
When DATE is converted to DATETIME it adds the of midnight. When TIME is converted to DATETIME it adds the date of 1900 and it is something one wants to consider if you are going to run scripts from SQL Server 2008 to earlier version with CONVERT.
Disabled Index and Update Statistics
If you do not need a nonclustered index, I suggest you to drop it as keeping them disabled is an overhead on your system. This is because every time the statistics are updated for system all the statistics for disabled indexes are also updated.
Precision of SMALLDATETIME – A 1 Minute Precision
The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.
2011
Getting Columns Headers without Result Data – SET FMTONLY ON
SET FMTONLY ON returns only metadata to the client. It can be used to test the format of the response without actually running the query. When this setting is ON the resultset only have headers of the results but no data.
Copy Database from Instance to Another Instance – Copy Paste in SQL Server
SQL Server has a feature which copy database from one database to another database and it can be automated as well using SSIS. Make sure you have SQL Server Agent Turned on as this feature will create a job.
Puzzle – SELECT * vs SELECT COUNT(*)
If you have ever wondered SELECT * gives error when executed alone but SELECT COUNT(*) does not. Why? in that case, you should read this blog post.
Creating All New Database with Full Recovery Model
This blog post is very based on very interesting story where the user wants to do something by default for every single new database created. Model database is a secret weapon which should be used very carefully and with proper evalution. If used carefully this can be a very much beneficiary when we need a newly created database behave in certain fashion.
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
Can anyone remember their final day of schooling? This is probably a silly question because – of course you can! Many people mark this as the most exciting, happiest day of their life. It marks the end of testing, the end of following rules set by teachers, and the beginning of finally being able to earn money and work in your chosen field.
Read five part series on developer training subject
Developer Training - Importance and Significance - Part 1
Developer Training – Employee Morals and Ethics – Part 2
Developer Training – Difficult Questions and Alternative Perspective - Part 3
Developer Training – Various Options for Developer Training – Part 4
Developer Training – A Conclusive Summary- Part 5
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
