SQL SERVER – Weekly Series – Memory Lane – #044

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

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.

2008

How to Rename a Column Name or Table Name
[youtube=http://www.youtube.com/watch?v=5xviNDISwis]

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:

1. Create a table type and define the table structure
2. Declare a stored procedure that has a parameter of the table type.
3. Declare a table type variable and reference the table type.
4. Using the INSERT statement and occupy the variable.
5. We can now pass the variable to the procedure.

2009

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.

2010

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 –

• You can use any numbers of the tables in your query
• You can only change the order of columns in WHERE clause
• You need to use either AND or OR clause between conditions of the WHERE clause
• Performance will be measured using Actual Execution Plan and SET IO Statistics ON
• The resultset returned from the query should be the same before changing order of columns in WHERE condition and after changing order of columns in WHERE condition.

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.

2011

Table-Valued Store Procedure Parameters – Day 25 of 35

Q) You need to create a stored procedure which accepts a table-valued parameter named @Suppliers. What code will achieve this result?

1. CREATE PROCEDURE AddSuppliers
   @Suppliers Float READONLY
   

2. CREATE PROCEDURE AddSuppliers
   @Suppliers Int READONLY
   

3. CREATE PROCEDURE AddSuppliers
   @Suppliers Money READONLY
   

4. CREATE PROCEDURE AddSuppliers
   @Suppliers SupplierType READONLY
   

5. CREATE PROCEDURE AddSuppliers
   @Suppliers GeographyType READONLY
   

Table-Valued Functions – Day 26 of 35

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?

1. Create a function that returns a list of values representing the login times for a given user.
2. Create a function that returns a list of values representing the people who have logged more hours than the current user has logged.
3. Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
4. Create a function that returns a number value representing the number of hours that a user has logged for the current month.

SQL Server Error Messages – Day 27 of 35

Q) When does SQL Server always raise an error message? (Choose two)

1. When a statement in SQL Server cannot run
2. When multiple records are updated in one table
3. When you issue a RAISERROR message

Structured Error Handling – Day 28 of 35

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.

BEGIN TRY
BEGIN TRANSACTION
DELETE FROM CurrentProducts WHERE ProductID = 77
BEGIN TRANSACTION
INSERT INTO SalesInvoiceHeader VALUES ( 95894, 77, 2 )
COMMIT TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE()
END CATCH

What will be the outcome when you run this query?

1. 1 The product will be deleted from the CurrentProducts table.
   2) The order details will be inserted into the SalesInvoiceHeader table.
2. 1) The product will be deleted from the CurrentProducts table.
   2) The order details will not be inserted into the SalesInvoiceHeader table.
3. 1) The product will not be deleted from the CurrentProducts table.
   2) The order details will be inserted into the SalesInvoiceHeader table.
4. 1) The product will not be deleted from the CurrentProducts table.
   2) The order details will not be inserted into the SalesInvoiceHeader table.

What is XML? – Day 29 of 35

Q) If you don’t specify any option, then XML RAW will have your data streamed in…

1. Element text.
2. Attributes.

What is XML? – Day 30 of 35

Q) Without XSINIL, what happens to null values from your result set?

1. They error out since XSINIL does not allow nulls.
2. They appear as empty tags.
3. No tags are present for null values.

Using Root With Auto XML Mode – Day 31 of 35

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?

1. FOR XML AUTO
2. FOR XML RAW
3. FOR XML AUTO, ROOT
4. FOR XML RAW, ROOT

2012

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.

[youtube=http://www.youtube.com/watch?v=T4054BuE2Vg]

Reference: Pinal Dave (https://blog.sqlauthority.com)