SQL SERVER – Weekly Series – Memory Lane – #041

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

Stop SQL Server Immediately Using T-SQL
How would you stop SQL Server using T-SQL – this is a simple script demonstrating the same.

List Tables in Database Without Primary Key
A simple to the script blog where user can find all the tables where there is no Primary Key.

List All The Column With Specific Data Types
SQL Server 2005 has new datatype of VARCHAR(MAX), we decided to change all our TEXT datatype columns to VARCHAR(MAX). The reason to do that as TEXT datatype will be deprecated in a future version of SQL Server and VARCHAR(MAX) is superior to TEXT datatype in features. We run following script to identify all the columns which are TEXT datatype and developer convert them to VARCHAR (MAX).

2008

Query to Find Column From All Tables of Database
This blog post is straight to script blog post where I demonstrate script to find a table name in the database.

EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle
EXCEPT clause in SQL Server is exactly similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.

2009

Forgot the Password of Username SA
There may be many more similar situations where employees who have sysamin password leaves the job or a hacker disables the SA account. Resetting the password of SA is a breeze!

Get a List of Fixed Hard Drive and Free Space on Server
At one point in optimization project, I have to decide on index filegroup placement as well TempDB files (.ldf and .mdf) placement. It is commonly known that system gives enhanced performance when index and tempdb are on separate drives than where the main database is placed.

Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime
I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution.

<strong>Quick Video on the same subject</strong>

[youtube=http://www.youtube.com/watch?v=BL5GO-jH3HA]

Here is the video discussing the same concepts.

[youtube=https://www.youtube.com/watch?v=61pHO5pZ1o4]

Multiple CTE in One SELECT Statement Query
I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT statement. Let us see quickly two examples for the same. I had done my best to take simplest examples in this subject.

2010

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 an index on a computed column does not grow the row length of the table.

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

Parallelism Query in Database
If I have a dual core computer and I would like to get a query executed with parallelism in order to test it, how would I do that? You can use the AdventureWorks database and let me know if you can get a query to execute in parallel.

Best Practices for DBA Before Taking Vacation
Everybody wants to take a vacation. Who does not love vacation, anyway? However, it seems that it has been getting more and more difficult to take vacation recently. There are two reasons why a person is not able to enjoy his vacation. First is due to company policies (bad boss!), and second is your responsibilities. Well, I cannot guide you much about company policy issues simply because I cannot do something about it. I have a wonderful boss and I have been taking many vacations, doing a few things whenever I’m on vacations.

2011

Tips from the SQL Joes 2 Pros Development Series – Efficient Query Writing Strategy – Day 4 of 35
Q) Square brackets are required when…

1. The table name conflicts with a keyword
2. The table name is the same as another table.
3. The table uses the same name as the database.
4. To alias the table.

Tips from the SQL Joes 2 Pros Development Series – Finding un-matching Records – Day 5 of 35
Q) Which of the following queries will show all location that have no employees?

1. SELECT P.EmployeeID, T.[Name] FROM Employee P LEFT OUTER JOIN Location T
   ON T.TerritoryID = P.TerritoryID
   WHERE T.TerritoryID IS NULL
2. SELECT P.EmployeeID, T.[Name] FROM Employee P LEFT OUTER JOIN Location T
   ON T.TerritoryID = P.TerritoryID
   WHERE P.TerritoryID IS NULL
3. SELECT P.EmployeeID, T.[Name] FROM Employee P RIGHT OUTER JOIN Location T
   ON T.TerritoryID = P.TerritoryID
   WHERE T.TerritoryID IS NULL
4. SELECT P.EmployeeID, T.[Name] FROM Employee P RIGHT OUTER JOIN Location T
   ON T.TerritoryID = P.TerritoryID
   WHERE P.TerritoryID IS NULL

Tips from the SQL Joes 2 Pros Development Series – Row Constructors – Day 6 of 35
Q) Which code will insert two records with 1 insert into statement?

1. INSERT into tblSports value(1,’Football’,) (2,’Cricket’,)
2. INSERT into tblSports values(1,’Football’), (2,’Cricket’)
3. INSERT into tblSports values(1,’Football’) (2,’Cricket’)
4. INSERT into tblSports values(1,’Football’):(2,’Cricket’)
5. INSERT into tblSports values(1,’Football’)::(2,’Cricket’)

Tips from the SQL Joes 2 Pros Development Series – Dirty Records and Table Hints – Day 7 of 35
Q) What happens when you try to query records in the intermediate state?

1. Your query waits until the data is no longer in the intermediate state.
2. Your query runs normally.
3. Your query splits the results into permanent and intermediate results.

Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 of 35
Q) You have tables named dbo.SalesInvoice and dbo.SalesInvoiceDetail. CustomerID is located in the SalesInvoice table and InvoiceID is located in both tables. You have been told to show the discount amounts from the SalesInvoiceDetail table that correspond to the sales of a specific CustomerID of 490. Which T-SQL statement should you use?

1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
   FROM dbo.SalesInvoiceDetail sd
   INNER JOIN dbo.SalesInvoice si
   ON sd.InvoiceID= si.InvoiceID
   INNER JOIN CurrentProducts AS cp
   ON cp.ProductID = sd.ProductID
   WHERE si.CustomerID= 490
2. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
   FROM dbo.SalesInvoiceDetail sd
   INNER JOIN CurrentProducts AS cp
   ON cp.ProductID = sd.ProductID
   WHERE si.CustomerID= 490
3. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
   FROM dbo.SalesInvoiceDetail sd
   WHERE EXISTS (dbo.SalesInvoice si
   ON sd.InvoiceID= si.InvoiceID
   INNER JOIN CurrentProducts AS cp
   ON cp.ProductID = sd.ProductID
   WHERE si.CustomerID= 490)

Tips from the SQL Joes 2 Pros Development Series – Overriding Identity Fields – Tricks and Tips of Identity Fields – Day 9 of 35
Q) You need to explicitly insert a value into an identity field for the SalesInvoice table. What two things must you do in order for your insert statement to successfully execute? (Choose two)

1. Turn the IDENTITY_INSERT  to ON for the SalesInvoice table
2. Turn the IDENTITY_INSERT  to OFF for the SalesInvoice table
3. Insert your values by position
4. Insert your values by name

Use INSERT INTO … SELECT instead of Cursor
Sometimes there is a simpler solution to different problems. When we do not know enough we often rely on ancient techniques. In this blog post, I explain how we can solve multiple insert related problems very quickly.

Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 of 35
Q) You want to show all fields of the Employee table. You want an additional field called StartDate that shows the first HireDate for all Employees. Which query should you use?

1. SELECT *, Min(HireDate) as StartDate FROM Employee
2. SELECT *, Max(HireDate) as StartDate FROM Employee
3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
4. SELECT *, Max(HireDate) OVER() as StartDate FROM Employee

2012

In this week we ran contests where we asked every day new question and in return there was interesting giveaways to win. It will take a few seconds only – take a shot and see how many answer you know.

Query Hint – Day 1 of 5
Which of the following queries will return dirty data?

a) SELECT * FROM Table1 (READUNCOMMITED)
b) SELECT * FROM Table1 (NOLOCK)
c) SELECT * FROM Table1 (DIRTYREAD)
d) SELECT * FROM Table1 (MYLOCK)

Identity Fields – Day 2 of 5
Which of the following queries will return dirty data?

a) SELECT * FROM Table1 (READUNCOMMITED)
b) SELECT * FROM Table1 (NOLOCK)
c) SELECT * FROM Table1 (DIRTYREAD)
d) SELECT * FROM Table1 (MYLOCK)

Clustered Index and Primary Key – Day 3 of 5
Which of the following datatype is usually NOT the best choice for Primary Key and Clustered Index?

a) INT
b) BIGINT
c) GUID
d) SMALLINT

Expanding Views – Day 4 of 5
Which of the following key word will force the query to use indexes created on views?

a) ENCRYPTION
b) SCHEMABINDING
c) NOEXPAND
d) CHECK OPTION

Understanding XML – Day 5 of 5
Is following XML a well formed XML Document?

<?xml version=”1.0″?>
<address>
<firstname>Pinal</firstname>
<lastname>Dave</lastname>
<title>Founder</title>
<company>SQLAuthority.com</company>
</address>

a) Yes
b) No
c) I do not know

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