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.
Convert Text to Numbers (Integer) – CAST and CONVERT
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.
List All Stored Procedure Modified in Last N Days
If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If a stored procedure was created but never modified afterwards modified date and create a date for that stored procedure are same.
Validate Field For DATE datatype using function ISDATE()
We always checked DATETIME field for incorrect data type. One of the user input date as 30/2/2007. The date was sucessfully inserted in the temp table but while inserting from temp table to final table it crashed with error. We had now task to validate incorrect date value before we insert in final table. Jr. Developer asked me how can he do that? We check for incorrect data type (varchar, int, NULL) but this is incorrect date value. Regular expression works fine with them because of mm/dd/yyyy format.
Find Space Used For Any Particular Table
It is very simple to find out the space used by any table in the database.
Two Convenient Features Inline Assignment – Inline Operations
Here is the script which does both – Inline Assignment and Inline Operation
DECLARE @idx INT = 0 SET @idx+=1 SELECT @idx
Introduction to SPARSE Columns
SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).
SP_CONFIGURE – Displays or Changes Global Configuration Settings
If advanced settings are not enabled at configuration level SQL Server will not let user change the advanced features on server. Authorized user can turn on or turn off advance settings.
Standby Servers and Types of Standby Servers
Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server.
BLOB – Pointer to Image, Image in Database, FILESTREAM Storage
When it comes to storing images in database there are two common methods. I had previously blogged about the same subject on my visit to Toronto. With SQL Server 2008, we have a new method of FILESTREAM storage. However, the answer on when to use FILESTREAM and when to use other methods is still vague in community.
Upper Case Shortcut SQL Server Management Studio
I select the word and hit CTRL+SHIFT+U and it SSMS immediately changes the case of the selected word. Similar way if one want to convert cases to lower case, another short cut CTRL+SHIFT+L is also available.
The Self Join – Inner Join and Outer Join
Self Join has always been a noteworthy case. It is interesting to ask questions about self join in a room full of developers. I often ask – if there are three kinds of joins, i.e.- Inner Join, Outer Join and Cross Join; what type of join is Self Join? The usual answer is that it is an Inner Join. However, the reality is very different.
Parallelism – Row per Processor – Row per Thread – Thread 0
If you look carefully in the Properties window or XML Plan, there is “Thread 0″. What does this “Thread 0” indicate? Well find out from the blog post.
How do I Learn and How do I Teach
The blog post has raised three very interesting questions. How do you learn? How do you teach? What are you learning or teaching? Let me try to answer the same.
CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis
Shortcut key is CTRL+SHIFT+]. This key can be very useful when dealing with multiple subqueries, CTE or query with multiple parentheses. When exercised this shortcut key it selects T-SQL code between two parentheses.
Monday Morning Puzzle – Query Returns Results Sometimes but Not Always
I am beginner with SQL Server. I have one query, it sometime returns a result and sometime it does not return me the result. Where should I start looking for a solution and what kind of information I should send to you so you can help me with solving. I have no clue, please guide me.
Effect of Case Sensitive Collation on Resultset
Collation is a very interesting concept but I quite often see it is heavily neglected. I have seen developer and DBA looking for a workaround to fix collation error rather than understanding if the side effect of the workaround.
Switch Between Two Parenthesis using Shortcut CTRL+]
Earlier this week I wrote a blog post about CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis, I received quite a lot of positive feedback from readers. If you are a regular reader of the blog post, you must be aware that I appreciate the learning shared by readers.
Reference: Pinal Dave (http://blog.sqlauthority.com)