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
Good, Better and Best Programming Techniques
Well, here is my note which I prepared to discuss in my earlier meeting. This is not complete and is not in very details. This note contains what I think is best programming technique in SQL. There are lots to add here and many opinion are very generic to SQL and other programming languages.
Explanation SQL SERVER Merge Join
Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.
Random Number Generator Script – SQL Query
One of the most popular blog post where I demonstrate how many different ways one can generate random numbers. I have also built a SQL in Sixty Seconds Video on this subject as well.
[youtube=http://www.youtube.com/watch?v=1d29ka0hHnc]
SET ROWCOUNT – Retrieving or Limiting the First N Records from a SQL Query
SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE statements against remote tables and local, remote partitioned views and when an explicit TOP expression is used in the same statement. This includes statements in which INSERT is followed by a SELECT clause. SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.
Collate – Case Sensitive SQL Query Search
Here is a quick script which does case sensitive SQL Query Search in the database.
Copy Column Headers in Query Analyzers in Result Set
Another very popular request which I transformed into SQL in Sixty Seconds Video.
[youtube=http://www.youtube.com/watch?v=BL5GO-jH3HA]
User Defined Functions (UDF) to Reverse String – UDF_ReverseString
UDF_ReverseString User Defined Functions returns the Reversed String starting from certain positions. First parameters take the string to be reversed. Second parameters take the position from where the string starts reversing.
TOP Improvements/EnhancementsSQL Server 2005 introduces two enhancements to the TOP clause.
1) User can specify an expression as an input to the TOP keyword.
2) User can use TOP in modification statements (INSERT, UPDATE, and DELETE).
Enable Login – Disable Login using ALTER LOGIC – Change name of the ‘SA’
One of the most important but ignored SQL Security Tip. A must read for everyone!
2008
Find Table in Every Database of SQL Server
This is one of the most asked T-SQL Script where user want to know where the table is used in every single database on the server. It is a two part story. Do not miss to read Part 1 and Part 2.
Delete Duplicate Records – Rows – Readers Contribution
A quick script from reader very important article but very less explored by users. Let me know your thoughts about this one.
2009
Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script
SQL Server 2005 and later versions provide encryption as a new feature to protect data against hackers’ attacks. Hackers might be able to penetrate the database or tables, but owing to encryption they would not be able to understand the data or make use of it. Nowadays, it has become imperative to encrypt crucial security-related data while storing in the database as well as during transmission across a network between the client and the server.
Solution to Puzzle – Shortest Code to Perform SSN Validation
Write the shortest T-SQL Code that removes invalid SSN values and returns a result set with only valid SSN values. This blog post was winner of the T-SQL Challenges on the same subject. If will be interesting to see if you can come up with a better optimized solution.
Example of DDL, DML, DCL and TCL Commands
Data Manipulation Language, Data Definition Language, Data Control Language, Transactional Control Language explained in simple words.
Add or Remove Identity Property on Column
How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action? For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table. However, there is an easy way to accomplish this action. It can be done through SSMS.
Roadmap of Microsoft Certifications – SQL Server Certifications
There are several benefits you can gain after clearing certification exams. After passing the first exam, developers earn credential of Microsoft Certified Professional commonly known as MCP. Once credentials are achieved developers get access to an official transcript, logos and certificates, and have their own landing page on Microsoft.com! According to Microsoft’s official site, real MCPs get real benefits of  Reward, Respect and Recognition.
2010
GUID vs INT – Your Opinion
This is an age old problem and I want to compile the list stating the advantages and disadvantages of using GUID and INT as a Primary Key or Clustered Index or Both (the usual case). The epic intense debate is happening on this particular topic till today after 3 years. You can see there is a wealth of information on this blog which one can just learn reading the original blog post as well as comments associated with the blog post.
Disable Clustered Index and Data Insert
Should we disable the clustered index while we insert the large data or we should drop the clustered index while we insert large data. Honestly, the answer is very simple, drop the clustered index. If we disable clustered index the impact of it is much different than we expect. Check out this blog post which describes the same issue with working demo.
Here is one of my old photo of TechEd 2010 – I had great time presenting on the stage in year 2010.
What is AdventureWorks?
This question is always there in my mailbox. Till today, everyday multiple times. Here is the blog post where I describe where one can get AdventureWorks as well I explain how they can install it as well.
[youtube=http://www.youtube.com/watch?v=-NRSvfKlh84]
SHRINKFILE and TRUNCATE Log File in SQL Server 2008
There are few absolute No-No’s in SQL Server environment. There should be no need to shrink and truncate log files in daily routine. Though this blog post was written keeping SQL Server 2008 in mind, this is also valid in SQL Server 2008 R2 and SQL Server 2012. If you are going to shrink your database, I strongly suggest that you read this blog post before it.
2011
Executing Stored Procedure with Result Sets
Q:Â Why are you using User Defined Function instead of Stored Procedure?
A:Â I cannot SELECT from SP, but I can from UDF.
The SQL Server 2012 version has come up with a very interesting feature called WITH RESULT SET.
Read here the follow up article.
CTAS – Create Table As SELECT – What is CTAS?
CTAS stands for ‘Create Table As Select’. This method is used when table was not created earlier and needs to be created when data from one table needs to be inserted into a newly created table from another table. The new table is created with the same data types as that of the selected columns.
Performance Improvement with of Executing Stored Procedure with Result Sets in SQL Server 2012
This is one of the article which really explains why one should use any feature for example. One should use the feature of Executing Stored Procedure with Resulset because it gives better performance over traditional methods.
2012
Microsoft Certification – SQL Server 2012
Read this blog post which describes how new Microsoft Certification have changed with the release of SQL Server 2012.
Video – Step by Step Installation of SQL Server 2012
Here is a video tutorial for how to install SQL Server 2012.
[youtube=http://www.youtube.com/watch?v=2vQ_wrwUvK8]
A Tricky Question and Even Trickier Answer – Index Intersection – Partition Function
Write a select statement using a single table, using single table single time only without using join keywords, which generate an execution plan with 2 join operators. Use AdventureWorks as a sample database.
Identify Columnstore Index Usage from Execution Plan
How do I know if columnstore index is used by query through execution plan? Here is the detail blog post about how to identify if columnstore index is used in the query or not.
Here are the steps which are to be followed:
- Create a sample table
- Insert some data
- Create clustered index on it
- Create nonclustered Columnstore Index on it
- Enable execution plan in SSMS
- Run two SELECT statement together with using clustered index and columnstore index (use hint if needed)
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
