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
Search Text Field – CHARINDEX vs PATINDEX
Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.
Explanation of TRY…CATCH and ERROR Handling
SQL Server provides the TRY…CATCH construct, which is already present in many modern programming languages. TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.
UDF – User Defined Function to Extract Only Numbers From String
This is straight to script blog post where I had written about UDF which extracts only numbers from strings.
Disable Triggers – Drop Triggers
Till today I use it extensively when I have to disable or drop trigger.
Stored Procedures Advantages and Best Advantage
Here is a very old but still today relevant blog post where I discuss why one should use stored procedures. I just can’t believe that such a short article which I wrote 6 years ago is still very valid article and relevant.
2008
Change Order of Column In Database Tables
How to change the order of the column in a database table? It happens many times table with few columns is already created. After a while there is need to add new column to the previously existing table.
Restore Database Using Corrupt Datafiles (.mdf and .ldf) – Part 2
A straight to script kind of blog post!
2009
Maximum Columns per Primary Key
Maximum columns per Primary Key Index is 16. In fact, 16 is the limit for columns per Foreign Key and Index Key. You cannot have more than 16 columns per Index Key, Primary Key or Foreign Key. So, reduce the columns in those columns to less than or equal to 16 columns.
Restore or Attach Database Without .NDF or .MDF is Not Possible
All the .mdf and .ndf files are mandatory to attach or restore database successfully. Even though you have all the transactions stored in .ldf you will not be able to restore the database completely.
Interesting Observation of DMV of Active Transactions and DMV of Current Transactions
1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server.
2) sys.dm_tran_current_transaction – Returns a single row that displays the state information of the transaction in the current session.
Introduction to JOINs – Basic of JOINs
I have tried to explain the fundamentals of the join using following quick method. I used diagram and simple script which are quite popular.
2010
Configure Management Data Collection in Quick Steps – T-SQL Tuesday #005
The three most important components of any computer and server are the CPU, Memory, and Hard disk specification. This post talks about  how to get more details about these three most important components using the Management Data Collection. Management Data Collection generates the reports for the three said components by default. Configuring Data Collection is a very easy task and can be done very quickly.
2011
Add New Column With Default Value
Adding default value to column is very easy task but not everyone knows the secret and they often write long script do so. In this to the point script you can see how easy it is to do it.
Query to Recent Query on Server with Execution Plan Function to Get SQL
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output.
2012
DMV sys.dm_exec_describe_first_result_set_for_object – Describes the First Result Metadata for the Module
DMV sys. dm_exec_describe_first_result_set_for_object returns a result set which describes the columns used in the stored procedure. Here is the quick example. Let us first create stored procedure.
Social Media Series – Twitter and Myself
Follow me on Twitter
Social Media Series – Facebook and Google+
Like me on Facebook |Add me to Google+Â
Social Media Series – YouTube and Movies
Subscribe us on YouTube
Social Media Series – LinkedIn and Professional Profile
Connect me with LinkedIn
Installing AdventureWorks Sample Database – SQL in Sixty Seconds #010 – Video
Many SQL Books and online blogs and articles there are scripts written by using AdventureWork database. I often received request that where people can get sample database as well how to restore sample database.
[youtube=http://www.youtube.com/watch?v=-NRSvfKlh84]
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
