SQL SERVER – Weekly Series – Memory Lane – #043

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

Find Last Day of Any Month – Current Previous Next
Few questions are always popular. They keep on coming up through email, comments or from co-workers. Finding Last Day of Any Month is similar question. I have received it many times and I enjoy answering it as well.

T-SQL Script to Insert Carriage Return and New Line Feed in Code
Very simple and very effective. We use all the time for many reasons – formatting, while creating dynamically generated SQL to separate GO command from other T-SQL, saving some user input text to database etc.

Use of Non-deterministic Function in UDF – Find Day Difference Between Any Date and Today
I always thought that use of Non-Deterministic function is prohibited in UDF. I even wrote about it earlier SQL SERVER – User Defined Functions (UDF) Limitations. It seems like SQL Server 2005 either have removed this restriction or it is a bug. I think I will not say this is bug but I will categorize it as a feature.

T-SQL Script to Attach and Detach Database
Following script can be used to detach or attach the database. If the database is to be from one database to another database following script can be used to detach from old server and attach to a new server.

2008

Detailed Explanation of Transaction Lock, Lock Type, Avoid Locks
This blog explains in detail the details about Transaction Lock, Lock Type and how to avoid the same.

Disable All the Trigger of Current Database
I have previously written article about SQL SERVER – Disable All Triggers on a Database – Disable All Triggers on All Servers. This is an alternate method to achieve the same task.

Behind the Scene of SQL Server Activity of – Transaction Log – Shrinking Log
When a transaction comes to SQL Server, it first comes to transaction log buffer, and then it is hardened to disk ( log file, .Def ) and then it is written to the data file ( .mdf). Then we say the transaction is committed or it is inactive, because the transaction performed all the actions that it should.

UDF – Function to Convert Text String to Title Case – Proper Case – Part 2
I had previously written SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case and I had really enjoyed writing it. Above script converts the first letter of each word from sentence to upper case.

Configure Database Mail – Send Email From SQL Database
[youtube=http://www.youtube.com/watch?v=ZGDBB2uwNp8]

2009

Get Query Plan Along with Query Text and Execution Count
I use the following handy script, which I use when I need to know the details regarding how many times any query has ran on my server along with its execution plan. You can add an additional WHERE condition if you want to learn about any specific object.

Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note
An index scan means that SQL Server reads all rows in a table, and then returns only those rows that satisfy the search criteria. When an index scan is performed, all the rows in the leaf level of the index are scanned. This essentially means that all of the rows of the index are examined instead of the table directly. This is sometimes contrasted to a table scan, in which all the table data is read directly. However, there is usually little difference between an index scan and a table scan.

2010

Negative Identity Seed Value and Negative Increment Interval
A very lesser known fact and even a lesser implemented feature of negative seed value and negative increment interval is described in this blog.

Function to Retrieve First Word of Sentence – String Operation
This is straight to the script kind of blog where I have described how to retrieve a first word of the sentence.

ORDER BY Does Not Work – Limitation of the Views Part 1
Order By clause does not work in View. I agree with all of you  who say that there is no need of using ORDER BY in the View. ORDER BY should be used outside the View and not in the View. This example is another reason why one should not use ORDER BY in Views.

SQL SERVER – Computed Column – PERSISTED and Storage
This article talks about how computed columns are created and why they take more storage space than before.

SQL SERVER – Computed Column – PERSISTED and Performance
This article talks about how PERSISTED columns give better performance than non-persisted columns.

SQL SERVER – Computed Column – PERSISTED and Performance – Part 2
This article talks about how non-persisted columns give better performance than PERSISTED columns.

SQL SERVER – Computed Column and Performance – Part 3
This article talks about how Index improves the performance of Computed Columns.

SQL SERVER – Computed Column – PERSISTED and Storage – Part 2
This article talks about how creating index on computed column does not grow the row length of table.

SQL SERVER – Computed Columns – Index and Performance
This article summarized all the articles related to computed columns.

2011

Geography Data Type – Calculating Distance Between Two Points on the Earth – Day 18 of 35

Q) The STDistance function of the Geography Data type calculates the distance between two points in …

1. Feet
2. Meters
3. Kilometers
4. Miles
5. Units

The Clustered Index – Simple Understanding – Day 19 of 35

Q) A heap has:

1. No clustered indexes.
2. One clustered index.
3. Many clustered indexes.

Introduction to Page Split – Day 20 of 35

Q) When do page splits happen?

1. When records from one memory page are moved to another page during changes to your table.
2. When records from one memory page are collapsed into fewer pages from excessive deletes.
3. When you insert records in order by the clustered index and you table needs to claim more memory space.

All about SQL Statistics – Day 21 of 35

Q) What advantage do SQL statistics offer the query optimizer?

1. They allow the table to save space.
2. The query optimizer knows the selectivity level of values before a query is run.
3. They show the performance statistics history since the last SQL restart.
4. They prevent page splits by buffer data page memory for later.

All about SQL Constraints – Day 22 of 35

Q) You have a table named Feedback that contains every record of how a customer felt about their purchase. One field is called Complaint, where 0 is no complaint and 1 is a complaint. You also have a field called Rating that ranges from 0 to 100. If a customer complains they should not be giving a perfect rating of 100. If they complain then they can enter a score between 0 and 90. If they don’t then it can be between 1 and 100. Which check constraint would you use?

1. CHECK (Rating BETWEEN 1 and 100)
2. CHECK (Rating <=90 AND Complaint = 1)
3. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1) )
   OR ( Rating BETWEEN 1 and 100 AND Complaint = 0) )
4. CHECK ( (Rating BETWEEN 1 and 90 AND Complaint = 1)
   AND ( Rating BETWEEN 1 and 100 AND Complaint = 0) )

Introduction to Views – Day 23 of 35

Q) What are the two ways to see the code that created a view? (Choose Two)

1. WITH SCHEMABINDING
2. WITH ENCRYPTION
3. sp_helptext
4. sp_depends
5. sys.syscomments

Easy Introduction to CHECK Options – Day 24 of 35

Q) You have a table named dbo.Sales. You need to create three views from the sales table.

vSalesSeattle

vSalesBoston

vSalesSpokane

Each view will be used by each region to make changes to their rows.  One day a Seattle sales manager updated his sales data to have a new LocationID and the record showed up on the vSalesBoston view. Changes made to the vSalesSeattle view must not be made in a way that the record falls outside of the scope of the view.  Which view should you create for Region1?

1. CREATE VIEW dbo.vSalesSeattle
   AS
   SELECT SalesID, OrderQty, SalespersonID, RegionID
   FROM dbo.Sales
   WHERE RegionID = 1
   WITH DIFFERENTIAL
2. CREATE VIEW dbo.vSalesSeattle
   AS
   SELECT SalesID, OrderQty, SalespersonID, RegionID
   FROM dbo.Sales
   WHERE RegionID = 1
   WITH CHECK OPTION
3. CREATE VIEW dbo.vSalesSeattle
   WITH SCHEMABINDING
   AS
   SELECT SalesID,OrderQty,SalespersonID, RegionID
   FROM dbo.Sales
   WHERE RegionID = 1
4. CREATE VIEW dbo.vSalesSeattle
   WITH NOCHECK
   AS
   SELECT SalesID, OrderQty, SalespersonID, RegionID
   FROM dbo.Sales
   WHERE RegionID = 1

2012

Installing Data Quality Services (DQS) on SQL Server 2012
In this blog post, I explain in the simple words how to install Data Quality Services.

Color Coding SQL Server Management Studio Status Bar – SQL in Sixty Seconds #023 – Video
[youtube=http://www.youtube.com/watch?v=HnsQDLcF4sY]

Reseting Identity Values for All Tables
Very interesting conversation – DBA: “I deleted all of the data from my database and now it contains a table structure only. However, when I tried to insert new data in my tables I noticed that my identity values starts from the same number where they actually were before I deleted the data.”

DELETE, TRUNCATE and RESEED Identity
Question for you: If I reseed value with some random number followed by the truncate command on the table what will be the seed value of the table. (Example, if original seed value is 11 and I reseed the value to 1. If I follow up with truncate table what will be the seed value now?

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