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.
Use Always Outer Join Clause instead of (*= and =*)
Instead of using LEFT OUTER JOIN clause he was using *= and similarly instead of using RIGHT OUTER JOIN clause he was using =*. Once I replaced did necessary modification, queries run just fine.
Actual Execution Plan vs. Estimated Execution Plan
I always use the Actual Execution Plan as it is accurate. Why not Estimated Execution Plan? It is not accurate. Sometime it is easier or useful to to know the plan without running the query. I just run a query and have correct and accurate Execution Plan.
Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M
Difference and Explanation among DECIMAL, FLOAT and NUMERIC
Converting from Decimal or Numeric to float can cause some loss of precision. For the Decimal or Numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. DECIMAL(2,2) and DECIMAL(2,4) are different data types. This means that 11.22 and 11.2222 are different types though this is not the case for float. For FLOAT(6) 11.22 and 11.2222 are same data types.
Find Database Collation Using T-SQL and SSMS
This article is written based on feedback I have received on SQL SERVER – Cannot resolve collation conflict for equal to operation. Many readers asked me how to find the collation of the current database. There are two different ways to find out SQL Server database collation.
Find Database Status Using sys.databases or DATABASEPROPERTYEX
While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties.
Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.
Introduction to Table-Valued Parameters with Example
Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow the steps shown below:
SQL Server Express – A Complete Reference Guide
SQL Server Express is one of the most valuable products of Microsoft. Very often, I face many questions with regard to SQL Server Express. Today, we will be covering some of the most commonly asked questions.
Does Order of Column in WHERE clause Matter?
Quick puzzle time – Does the order column used in WHERE clause matter for performance? Here are the rules for you –
Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2
In this episode of limitation of the View, we will see how adding an additional column outside the view can be very expensive, whereas the same situation does not happen with regular T-SQL query.
Plan Cache – Retrieve and Remove – A Simple Script
In this blog post we discuss about query that demonstrates cache plans which are ‘ad hoc’ or called only once in a life time. You can see how much memory is already bloated by not-so-useful queries. If you want to remove any large plan cache which you do not think is useful to you, you can run the another command to remove it.
Q) You need to create a stored procedure which accepts a table-valued parameter named @Suppliers. What code will achieve this result?
Q) You need to create two functions that will each return a scalar result of the number of hours each user has logged in: 1) the current day, and 2) month to date. You will pass in the user ID as a parameter value. What two things must you do?
Q) When does SQL Server always raise an error message? (Choose two)
Q) You have tables named CurrentProducts and SalesInvoiceHeader. The CurrentProducts table has a foreign key relationship with the SalesInvoiceHeader table on the ProductID column. You are deleting ProductID 77 from the Product table and then trying to insert a sale for Product77 into the SalesInvoiceHeader table.
DELETE FROM CurrentProducts WHERE ProductID = 77
INSERT INTO SalesInvoiceHeader VALUES ( 95894, 77, 2 )
What will be the outcome when you run this query?
Q) If you don’t specify any option, then XML RAW will have your data streamed in…
Q) Without XSINIL, what happens to null values from your result set?
Q) You have a query which joins tables. You want to create a well-formed XML stream, which is attribute-based and nests the results in the table from the first field of the select list. Which code do you append to the SQL statement?
Answer – Value of Identity Column after TRUNCATE command
Earlier I had one conversation with reader where I almost got a headache. I suggest all of you to read it before continuing this blog post SQL SERVER – Reseting Identity Values for All Tables. I believed that he faced this situation because he did not understand the difference between SQL SERVER – DELETE, TRUNCATE and RESEED Identity. I wrote a follow up blog post explaining the difference between them. I asked a small question in the second blog post and I received many interesting comments. Let us go over the question and its answer here one more time.
A Brief Note on SET TEXTSIZE
I do not think they can be directly comparable even though both of them give the exact same result while using SSMS. LEFT is applicable only on the column of a single SELECT statement. Where it is used but it SET TEXTSIZE applies to all the columns in the SELECT and follow up SELECT statements till the SET TEXTSIZE is not modified again in the session. Incomparable!
Three Methods to Insert Multiple Rows into Single Table – SQL in Sixty Seconds #024 – Video
One of the biggest ask I have always received from developers is that if there is any way to insert multiple rows into a single table in a single statement. Currently when developers have to insert any value into the table they have to write multiple insert statements. First of all this is not only boring it is also very much time consuming as well. Additionally, one has to repeat the same syntax so many times that the word boring becomes an understatement.
Reference: Pinal Dave (http://blog.sqlauthority.com)