SQL SERVER – Weekly Series – Memory Lane – #040

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

Complete Series of SQL Server Interview Questions and Answers
Data Warehousing Interview Questions and Answers – Introduction
Data Warehousing Interview Questions and Answers – Part 1
Data Warehousing Interview Questions and Answers – Part 2
Data Warehousing Interview Questions and Answers – Part 3
Data Warehousing Interview Questions and Answers Complete List Download

2008

Simple Example of Recursive CTE
Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression).

Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop
Now if your CTE goes beyond nth recursion it will throw an error and stop executing. If you put MAXRECURSION value too low it may be possible before your desire result is accomplished and will throw an error.

Get Current System Date Time
[youtube=http://www.youtube.com/watch?v=BL5GO-jH3HA]

Effect of Order of Join In Query
When we use Left or Right Join, We have a base table Employee and the records are order by the primary key i.e The EmployeeID of the base table by default. But when we use the Inner Join, then the table having smallest number of records are used to order by. Here in our above example, the HumanResources.Department has 16 Records. So  the records are sorted by the departmentId of the HumanResources.Department table.

Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN
INTERSECT operator in SQL Server 2005 is used to retrieve the common records from both the left and the right query of the Intersect Operator. INTERSECT operator returns almost same results as INNER JOIN clause many times.

2009

Design Process Decision Flow
The following six steps represent the most critical design elements in a well-planned SQL Server 2008 design:

• Step 1: Determine the Project Scope
• Step 2: Determine Which Roles Will Be Required
• Step 3: Design the SQL Server Database Engine Infrastructure
• Step 4: Design the SQL Server Integration Services Infrastructure
• Step 5: Design the SQL Server Analysis Services Infrastructure
• Step 6: Design the SQL Server Reporting Services Infrastructure

Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table
I had written on the subject of how to insert data from one table to another table without generating any script or using wizard in my article SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE. Today, we will go over a similar question regarding how to generate script for data from database as well as table. SQL Server 2008 has simplified everything.

Introduction to Cloud Computing
“Cloud Computing,” to put it simply, means “Internet Computing.” The Internet is commonly visualized as clouds; hence the term “cloud computing” for computation done through the Internet. With Cloud Computing users can access database resources via the Internet from anywhere, for as long as they need, without worrying about any maintenance or management of actual resources. Besides, databases in cloud are very dynamic and scalable.

Introduction to SQL Server 2008 Profiler
SQL Server Profiler is a powerful tool that is available with SQL Server since a long time; however, it has mostly been underutilized by DBAs. SQL Server Profiler can perform various significant functions such as tracing what is running under the SQL Server Engine’s hood, and finding out how queries are resolved internally and what scripts are running to accomplish any T-SQL command.

2010

Introduction to BINARY_CHECKSUM and Working Example
I was asked if I can give a working example of BINARY_CHECKSUM. This is usually used to detect changes in a row. If any row has any value changed, this function can be used to figure out if the values are changed in the rows. However, if the row is changed from A to B and once again changed back to A, the BINARY_CHECKSUM cannot be used to detect the changes. Let us see a quick example of the of same.

This is the list of the all the articles in the series of computed columns.

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

SQL SERVER – Interview Questions and Answers – Guest Post by Nakul Vachhrajani – Day 28 of 31

Nakul explains the word – ‘interview ‘ with an entire different angle to the subject. What really attacks on Nakul’s writing is his extremely clear ideas which are super crisp and to the point. Nakul talks about ‘Educational Qualification has been never the only requirement’ with such a decent argument, that makes this article a must read.

SQL SERVER – Interview Questions and Answers – Guest Post by Feodor Georgiev – Day 29 of 31

Feodor is our most read gust blog authors. He always comes up with an interesting subject. Feodor start writing with interesting concept that – the job interview is like an enchanted dance between a potential employee and a potential employer which plays a defining role in their entire collaboration. This article has many witty comments and many interesting subjects. A long article which has not a single dull moment.

SQL SERVER – Interview Questions and Answers – Guest Post by Jacob Sebastian – Day 30 of 31

Jacob Sebastian never likes to talk about easy subject. He always talks on difficult subjects. This time he talks about the philosophy of XML. The purpose of the questions and their answers given below is to ensure that the people who read them and learn them will get a basic understanding of the XML functionalities provided by SQL Server. Jacob makes the dry subject like XML to worth reading and learning it.

SQL SERVER – The Difficult Interview Question – Moment in the Life – Day 31 of 31
I guess the title says all – Interview is the moment of life. Final write up on this subject by myself.

Learning is an extremely important part of life. From the first step, everybody progresses in life and learns something new.

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard Basics Recap – Day 1 of 35

You want to find all first names that start with the letters A-M in your Customer table. Which SQL code would you use?

1. SELECT * FROM Customer
   WHERE Firstname <= ‘m%’
2. SELECT * FROM Customer
   WHERE Firstname  = ‘a-m%’
3. SELECT * FROM Customer
   WHERE Firstname like ‘a-m%’
4. SELECT * FROM Customer
   WHERE Firstname  = ‘[a-m]% ‘
5. SELECT * FROM Customer
   WHERE Firstname like ‘[a-m]%’

Q 2) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard – Querying Special Characters – Day 2 of 35

You want to find all grant names that have an Underscore as the second letter. Which SQL code would you use?

1. SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
2. SELECT * FROM [Grant] WHERE GrantName like ‘[_]_% ‘
3. SELECT * FROM [Grant] WHERE GrantName like ‘_%[_]%_ ‘
4. SELECT * FROM [Grant] WHERE GrantName = ‘_[_]% ‘
5. SELECT * FROM [Grant] WHERE GrantName = ‘[_]_% ‘
6. SELECT * FROM [Grant] WHERE GrantName = ‘_%[_]%_ ‘

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding Apostrophes in String and Text – Day 3 of 35

You want to find all first names that have an apostrophe anywhere in the name. Which SQL code would you use?

1. SELECT * FROM Employee
   WHERE Firstname like ‘_’% ‘
2. SELECT * FROM Employee
   WHERE Firstname like ‘_”% ‘
3. SELECT * FROM Employee
   WHERE Firstname like ‘_[‘]% ‘
4. SELECT * FROM Employee
   WHERE Firstname like ‘%’% ‘
5. SELECT * FROM Employee
   WHERE Firstname like ‘%”% ‘
6. SELECT * FROM Employee
   WHERE Firstname like ‘%[‘]% ‘

2012

Difference Between ORIGINAL_LOGIN() and SUSER_SNAME()
Function ORIGINAL_LOGIN() returns the name of the original or very first login that connected to the instance of SQL Server and it is used to identity of the original login in sessions.

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