SQL SERVER – Weekly Series – Memory Lane – #043

memory lane SQL SERVER   Weekly Series   Memory Lane   #043Here 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

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

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 (http://blog.sqlauthority.com)

SQL SERVER – Weekly Series – Memory Lane – #042

memory lane SQL SERVER   Weekly Series   Memory Lane   #042Here 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

Validate Integer Function
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). Now this is an issue with ISNUMERIC () function. It even suggest variable as numeric when it is not. I use following function to validate instead of ISNUMERIC () to validate if the number is numeric or not.

Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Difference and Similarity Between NEWSEQUENTIALID() and NEWID()
NEWSEQUENTIALID() and NEWID() both generates the GUID of datatype of uniqueidentifier. NEWID() generates the GUID in random order whereas NEWSEQUENTIALID() generates the GUID in sequential order.

Explanation and Script for Online Index Operations – Create, Rebuild, Drop
Online operation means when online operations are happening in the database are in normal operational condition, the processes which are participating in online operations does not require exclusive access to the database. In case of Online Indexing Operations, when Index operations (create, rebuild, dropping) are occuring they do not require exclusive access to database, they do not lock any database tables. This is a major important upgrade in SQL Server from previous versions.

2008

Get Date Time in Any Format – UDF – User Defined Functions
A very comprehensive function which returns date time in any format. 

Introduction to Online Indexing Operation
SQL Server 2005 and later versions have provided feature called “Online Indexing”. Everytime index is updated it puts a lock on a table where index operations are happening. Depending on the situation SQL Server puts schema lock or shared lock while the index is modified. If a feature of “Online Indexing” is used SQL Server will behave normally when the Index is modified.

2009

Backup master Database Interval – master Database Best Practices

  • The master database is the most important database of all and the most recent version of the backup should be available in the case of disaster.
  • Backup of the master database should be made for:
    • Changing server-level configuration settings;
    • Changing database-level configuration settings; and
    • Changing any logon accounts details.

Measure CPU Pressure – CPU Business
Let us understand the simple meaning of CPU pressure. CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks and there are more tasks in the queue that have not yet started.

2010

SHRINKDATABASE For Every Database in the SQL Server
Shrinking the database is indeed bad and it reduces the performance of the server. In this blog post, I clearly demonstrate the real world scenario for the same.

Why SQL Server is better than any other RDBMS Applications?
Very interesting arguments on this subject in this blog. I really enjoyed and I am very sure everybody who loves SQL Server will enjoy this blog post.

Finding the Occurrence of Character in String
A straight to script blog where I explained how to find the occurrence of character in any string.

2011

Tips from the SQL Joes 2 Pros Development Series – Advanced Aggregates with the Over Clause – Day 11 of 35
Q) You have a table named CurrentProducts. The table contains a column named Category. You need to make a T-SQL statement that calculates the percentage (with decimals) of products in each product Category. Which query should you use?

  1. SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts
  2. SELECT DISTINCT Category,
    COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
    FROM CurrentProducts
  3. SELECT DISTINCT Category,
    COUNT(*) OVER ( )*100.0/ COUNT(*) OVER(PARTITION BY Category) as PctCategory
    FROM CurrentProducts

Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) – Day 12 of 35
Q) The figure below shows the scores of 6 contest winners. Tom is the highest

and Eric made 6th place. There were 6 people but only 5 distinct scores.

5 Highest Scores        5 Highest Distinct Scores

9.9                               9.9

9.8                               9.8

9.7                               9.7

9.6                               9.6

9.6                               9.2

You are writing a query to list the 5 highest distinct scores. The Ranked field should be called ScoreRating. You have written the following code.

SELECT * FROM
(SELECT  *  More code here.
FROM [Contestants])  AS ContestantFinal
WHERE ScoreRating <= 5

What code will achieve this goal?

  1. SUM(*) OVER(ORDER BY Score DESC) as ScoreRating
  2. RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
  3. COUNT(*) OVER(ORDER BY Score DESC) as ScoreRating
  4. DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – Advanced NTILE in Detail – Day 13 of 35
Q) You want to find the top 2% of all students’ Grade Point Averages (GPA). Which NTILE would you use?

  1. NTILE(1) OVER(ORDER BY GPA DESC)
  2. NTILE(2) OVER(ORDER BY GPA DESC)
  3. NTILE(25) OVER(ORDER BY GPA DESC)
  4. NTILE(50) OVER(ORDER BY GPA DESC)

Tips from the SQL Joes 2 Pros Development Series – Output Clause in Simple Examples – Day 14 of 35
Q) You have an HourlyPay table and are giving all hourly employees a $1 raise. When you run the update statement you want to see the EmpID, OldPay, and NewPay. What code will achieve this result?

  1. UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Updated.Hourly as NewPay
    WHERE Hourly IS NOT NULL
  2. UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Updated.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL
  3. UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
    WHERE Hourly IS NOT NULL
  4. UPDATE HourlyPay SET Hourly = Hourly + 1
    OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
    WHERE Hourly IS NOT NULL

Tips from the SQL Joes 2 Pros Development Series – Data Row Space Usage and NULL Storage – Day 15 of 35
Q) You have three variable length data fields. What are the rules that go into the calculation of how large the variable block will be (Choose two)?

  1. You will allocate 2 bytes to the creation of the variable block
  2. You will allocate 3 bytes to the creation of the variable block
  3. You will allocate 2 more bytes for each of the three variable fields
  4. You will allocate 1 byte for every eight columns in the table.

Tips from the SQL Joes 2 Pros Development Series – System and Time Data Types – Day 16 of 35
Q) Which one of the following functions will return the date and time in the current time zone to a precision of milliseconds?

  1. GETDATE( )
  2. SYSDATETIME( )
  3. GETUTCDATE( )
  4. SYSUTCDATETIME( )

Tips from the SQL Joes 2 Pros Development Series – Sparse Data and Space Used by Sparse Data – Day 17 of 35
Q) You have two fields, of INT and MONEY, in your Bonus table. You have 1000 records and all instances of the money column are null. When you set up the money field, you used the Sparse option. How much space are the 1000 rows of the money field using?

  1. None
  2. 4000 bytes
  3. 8000 bytes

2012

eureka SQL SERVER   Weekly Series   Memory Lane   #042

Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – Part 1 of 2
In simple words – due to ON DELETE CASCASE whenever is specified when the data from Table A is deleted and if it is referenced in another table using foreign key it will be deleted as well.

Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – T-SQL Example – Part 2 of 2
My friend was confused as there was no delete was firing over ProductsDetails Table still there was a delete happening. The reason was because there is a foreign key created between Products and ProductsDetails Table with the keywords ON DELETE CASCADE. Due to ON DELETE CASCADE whenever is specified when the data from Table A is deleted and if it is referenced in another table using foreign key it will be deleted as well.

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

SQL SERVER – Weekly Series – Memory Lane – #041

memory lane SQL SERVER   Weekly Series   Memory Lane   #041Here 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>

Here is the video discussing the same concepts.

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.

vacation SQL SERVER   Weekly Series   Memory Lane   #041

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 (http://blog.sqlauthority.com)

SQL SERVER – Weekly Series – Memory Lane – #040

memory lane SQL SERVER   Weekly Series   Memory Lane   #040Here 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

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

designflow SQL SERVER   Weekly Series   Memory Lane   #040

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

KL Malaysia%20(2) SQL SERVER   Weekly Series   Memory Lane   #040

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 (http://blog.sqlauthority.com)

SQL SERVER – Weekly Series – Memory Lane – #039

memory lane SQL SERVER   Weekly Series   Memory Lane   #039Here 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

FQL – Facebook Query Language
Facebook list following advantages of FQL:

  • Condensed XML reduces bandwidth and parsing costs.
  • More complex requests can reduce the number of requests necessary.
  • Provides a single consistent, unified interface for all of your data.
  • It’s fun!

UDF – Get the Day of the Week Function
The day of the week can be retrieved in SQL Server by using the DatePart function. The value returned by the function is between 1 (Sunday) and 7 (Saturday). To convert this to a string representing the day of the week, use a CASE statement.

UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday
While reading ColdFusion blog of Ben Nadel Getting the Previous Day In ColdFusion, Excluding Saturday And Sunday, I realize that I use similar function on my SQL Server Database. This function excludes the Weekends (Saturday and Sunday), and it gets previous as well as next work day.

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

Introduction to Log Viewer
In SQL Server all the windows event logs can be seen along with SQL Server logs. Interface for all the logs is same and can be launched from the same place. This log can be exported and filtered as well.

DBCC SHRINKFILE Takes Long Time to Run
If you are DBA who are involved with Database Maintenance and file group maintenance, you must have experience that many times DBCC SHRINKFILE operations takes a long time but any other operations with Database are relatively quicker.

mssqlsystemresource – Resource Database
The purpose of resource database is to facilitates upgrading to the new version of SQL Server without any hassle. In previous versions whenever version of SQL Server was upgraded all the previous version system objects needs to be dropped and new version system objects to be created.

2009

Puzzle – Write Script to Generate Primary Key and Foreign Key
In SQL Server Management Studio (SSMS), there is no option to script all the keys. If one is required to script keys they will have to manually script each key one at a time. If database has many tables, generating one key at a time can be a very intricate task. I want to throw a question to all of you if any of you have scripts for the same purpose.

Maximizing View of SQL Server Management Studio – Full Screen – New Screen
I had explained the following two different methods:
1) Open Results in Separate Tab – This is a very interesting method as result pan shows up in a different tab instead of the splitting screen horizontally.
2) Open SSMS in Full Screen – This works always and to its best. Not many people are aware of this method; hence, very few people use it to enhance performance.

2010

Find Queries using Parallelism from Cached Plan
T-SQL script gets all the queries and their execution plan where parallelism operations are kicked up. Pay attention there is TOP 10 is used, if you have lots of transactional operations, I suggest that you change TOP 10 to TOP 50

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 – Frequently Asked Questions – Data Warehousing Concepts – Day 21 of 31

  • What is Data Warehousing?
  • What is Business Intelligence (BI)?
  • What is a Dimension Table?
  • What is Dimensional Modeling?
  • What is a Fact Table?
  • What are the Fundamental Stages of Data Warehousing?
  • What are the Different Methods of Loading Dimension tables?
  • Describes the Foreign Key Columns in Fact Table and Dimension Table?
  • What is Data Mining?
  • What is the Difference between a View and a Materialized View?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehousing Concepts – Day 22 of 31

  • What is OLTP?
  • What is OLAP?
  • What is the Difference between OLTP and OLAP?
  • What is ODS?
  • What is ER Diagram?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehousing Concepts – Day 23 of 31

  • What is ETL?
  • What is VLDB?
  • Is OLTP Database is Design Optimal for Data Warehouse?
  • If denormalizing improves Data Warehouse Processes, then why is the Fact Table is in the Normal Form?
  • What are Lookup Tables?
  • What are Aggregate Tables?
  • What is Real-Time Data-Warehousing?
  • What are Conformed Dimensions?
  • What is a Conformed Fact?
  • How do you Load the Time Dimension?
  • What is a Level of Granularity of a Fact Table?
  • What are Non-Additive Facts?
  • What is a Factless Facts Table?
  • What are Slowly Changing Dimensions (SCD)?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehousing Concepts – Day 24 of 31

  • What is Hybrid Slowly Changing Dimension?
  • What is BUS Schema?
  • What is a Star Schema?
  • What Snow Flake Schema?
  • Differences between the Star and Snowflake Schema?
  • What is Difference between ER Modeling and Dimensional Modeling?
  • What is Degenerate Dimension Table?
  • Why is Data Modeling Important?
  • What is a Surrogate Key?
  • What is Junk Dimension?
  • What is a Data Mart?
  • What is the Difference between OLAP and Data Warehouse?
  • What is a Cube and Linked Cube with Reference to Data Warehouse?
  • What is Snapshot with Reference to Data Warehouse?
  • What is Active Data Warehousing?
  • What is the Difference between Data Warehousing and Business Intelligence?
  • What is MDS?
  • Explain the Paradigm of Bill Inmon and Ralph Kimball.

SQL SERVER – Azure Interview Questions and Answers – Guest Post by Paras Doshi – Day 25 of 31

Paras Doshi has submitted 21 interesting question and answers for SQL Azure.

1.What is SQL Azure?
2.What is cloud computing?
3.How is SQL Azure different than SQL server?
4.How many replicas are maintained for each SQL Azure database?
5.How can we migrate from SQL server to SQL Azure?
6.Which tools are available to manage SQL Azure databases and servers?
7.Tell me something about security and SQL Azure.
8.What is SQL Azure Firewall?
9.What is the difference between web edition and business edition?
10.How do we synchronize On Premise SQL server with SQL Azure?
11.How do we Backup SQL Azure Data?
12.What is the current pricing model of SQL Azure?
13.What is the current limitation of the size of SQL Azure DB?
14.How do you handle datasets larger than 50 GB?
15.What happens when the SQL Azure database reaches Max Size?
16.How many databases can we create in a single server?
17.How many servers can we create in a single subscription?
18.How do you improve the performance of a SQL Azure Database?
19.What is code near application topology?
20.What were the latest updates to SQL Azure service?
21.When does a workload on SQL Azure get throttled?

SQL SERVER – Interview Questions and Answers – Guest Post by Malathi Mahadevan – Day 26 of 31

Malachi had asked a simple question which has several answers. Each answer makes you think and ponder about the reality of the IT world. Look at the simple question – ‘What is the toughest challenge you have faced in your present job and how did you handle it’? and its various answers. Each answer has its own story.

SQL SERVER – Interview Questions and Answers – Guest Post by Rick Morelan – Day 27 of 31

Rick Morelan of Joes2Pros has written an excellent blog post on the subject how to find top N values. Most people are fully aware of how the TOP keyword works with a SELECT statement. After years preparing so many students to pass the SQL Certification I noticed they were pretty well prepared for job interviews too. Yes, they would do well in the interview but not great. There seemed to be a few questions that would come up repeatedly for almost everyone. Rick addresses similar questions in his lucid writing skills.

2012

Observation of Top with Index and Order of Resultset
SQL Server has lots of things to learn and share. It is amazing to see how people evaluate and understand different techniques and styles differently when implementing. The real reason may be absolutely different but we may blame something totally different for the incorrect results. Read the blog post to learn more.

How do I Record Video and Webcast

How to Convert Hex to Decimal or INT
Earlier I asked regarding a question about how to convert Hex to Decimal. I promised that I will post an answer with Due Credit to the author but never got around to post a blog post around it. Read the original post over here SQL SERVER – Question – How to Convert Hex to Decimal.

Query to Get Unique Distinct Data Based on Condition – Eliminate Duplicate Data from Resultset
The natural reaction will be to suggest DISTINCT or GROUP BY. However, not all the questions can be solved by DISTINCT or GROUP BY. Let us see the following example, where a user wanted only latest records to be displayed. Let us see the example to understand further.

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

SQL SERVER – Weekly Series – Memory Lane – #038

memory lane SQL SERVER   Weekly Series   Memory Lane   #038Here 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

CASE Statement in ORDER BY Clause – ORDER BY using Variable
This article is as per request from the Application Development Team Leader of my company. His team encountered code where the application was preparing string for ORDER BY clause of the SELECT statement. Application was passing this string as variable to Stored Procedure (SP) and SP was using EXEC to execute the SQL string. This is not good for performance as Stored Procedure has to recompile every time due to EXEC. sp_executesql can do the same task but still not the best performance.

SSMS – View/Send Query Results to Text/Grid/Files

Results to Text – CTRL + T
Results to Grid – CTRL + D
Results to File – CTRL + SHIFT + F

2008

Introduction to SPARSE Columns Part 2
I wrote about Introduction to SPARSE Columns Part 1. Let us understand the concept of the SPARSE column in more detail. I suggest you read the first part before continuing reading this article. All SPARSE columns are stored as one XML column in the database. Let us see some of the advantage and disadvantage of SPARSE column.

Deferred Name Resolution
How come when table name is incorrect SP can be created successfully but when an incorrect column is used SP cannot be created?

2009

Backup Timeline and Understanding of Database Restore Process in Full Recovery Model
In general, databases backup in full recovery mode is taken in three different kinds of database files.

  1. Full Database Backup
  2. Differential Database Backup
  3. Log Backup

backuptimeline SQL SERVER   Weekly Series   Memory Lane   #038

Restore Sequence and Understanding NORECOVERY and RECOVERY
While doing RESTORE Operation if you restoring database files, always use NORECOVER option as that will keep the database in a state where more backup file are restored. This will also keep database offline also to prevent any changes, which can create itegrity issues. Once all backup file is restored run RESTORE command with a RECOVERY option to get database online and operational.

Four Different Ways to Find Recovery Model for Database
Perhaps, the best thing about technical domain is that most of the things can be executed in more than one ways. It is always useful to know about the various methods of performing a single task.

Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database
When Information Schema is used, we will not be able to discern between primary key and foreign key; we will have both the keys together. In the case of sys schema, we can query the data in our preferred way and can join this table to another table, which can retrieve additional data from the same.

Get Last Running Query Based on SPID
PID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.

2010

SELECT * FROM dual – Dual Equivalent
Dual is a table that is created by Oracle together with data dictionary. It consists of exactly one column named “dummy”, and one record. The value of that record is X. You can check the content of the DUAL table using the following syntax. SELECT * FROM dual

Identifying Statistics Used by Query
Someone asked this question in my training class of query optimization and performance tuning.  “Can I know which statistics were used by my query?”

2011

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 14 of 31

  • What are the basic functions for master, msdb, model, tempdb and resource databases?
  • What is the Maximum Number of Index per Table?
  • Explain Few of the New Features of SQL Server 2008 Management Studio
  • Explain IntelliSense for Query Editing
  • Explain MultiServer Query
  • Explain Query Editor Regions
  • Explain Object Explorer Enhancements
  • Explain Activity Monitors

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 15 of 31

  • What is Service Broker?
  • Where are SQL server Usernames and Passwords Stored in the SQL server?
  • What is Policy Management?
  • What is Database Mirroring?
  • What are Sparse Columns?
  • What does TOP Operator Do?
  • What is CTE?
  • What is MERGE Statement?
  • What is Filtered Index?
  • Which are the New Data Types Introduced in SQL SERVER 2008?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31

  • What are the Advantages of Using CTE?
  • How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?
  • What is CLR?
  • What are Synonyms?
  • What is LINQ?
  • What are Isolation Levels?
  • What is Use of EXCEPT Clause?
  • What is XPath?
  • What is NOLOCK?
  • What is the Difference between Update Lock and Exclusive Lock?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 17 of 31

  • How will you Handle Error in SQL SERVER 2008?
  • What is RAISEERROR? What is RAISEERROR?
  • How to Rebuild the Master Database?
  • What is the XML Datatype?
  • What is Data Compression?
  • What is Use of DBCC Commands?
  • How to Copy the Tables, Schema and Views from one SQL Server to Another?
  • How to Find Tables without Indexes?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31

  • How to Copy Data from One Table to Another Table?
  • What is Catalog Views?
  • What is PIVOT and UNPIVOT?
  • What is a Filestream?
  • What is SQLCMD?
  • What do you mean by TABLESAMPLE?
  • What is ROW_NUMBER()?
  • What are Ranking Functions?
  • What is Change Data Capture (CDC) in SQL Server 2008?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31

  • How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?
  • What is the CPU Pressure?
  • How can I Get Data from a Database on Another Server?
  • What is the Bookmark Lookup and RID Lookup?
  • What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
  • What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?
  • How can I Check that whether Automatic Statistic Update is Enabled or not?
  • How to Find Index Size for Each Index on Table?
  • What is the Difference between Seek Predicate and Predicate?
  • What are Basics of Policy Management?
  • What are the Advantages of Policy Management?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 20 of 31

  • What are Policy Management Terms?
  • What is the ‘FILLFACTOR’?
  • Where in MS SQL Server is ’100’ equal to ‘0’?
  • What are Points to Remember while Using the FILLFACTOR Argument?
  • What is a ROLLUP Clause?
  • What are Various Limitations of the Views?
  • What is a Covered index?
  • When I Delete any Data from a Table, does the SQL Server reduce the size of that table?
  • What are Wait Types?
  • How to Stop Log File Growing too Big?
  • If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?

2012

Example of Width Sensitive and Width Insensitive Collation
Width Sensitive Collation: A single-byte character (half-width) represented as single-byte and the same character represented as a double-byte character (full-width) are when compared are not equal the collation is width sensitive. In this example we have one table with two columns. One column has a collation of width sensitive and the second column has a collation of width insensitive.

Find Column Used in Stored Procedure – Search Stored Procedure for Column Name
Very interesting conversation about how to find column used in a stored procedure. There are two different characters in the story and both are having a conversation about how to find column in the stored procedure. Here are two part story Part 1 | Part 2

SQL SERVER – 2012 Functions – FORMAT() and CONCAT() – An Interesting Usage

Generate Script for Schema and Data – SQL in Sixty Seconds #021 – Video
In simple words, in many cases the database move from one place to another place. It is not always possible to back up and restore databases. There are possibilities when only part of the database (with schema and data) has to be moved. In this video we learn that we can easily generate script for schema for data and move from one server to another one.

INFORMATION_SCHEMA.COLUMNS and Value Character Maximum Length -1
I often see the value -1 in the CHARACTER_MAXIMUM_LENGTH column of INFORMATION_SCHEMA.COLUMNS table. I understand that the length of any column can be between 0 to large number but I do not get it when I see value in negative (i.e. -1). Any insight on this subject?

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

SQL SERVER – Weekly Series – Memory Lane – #037

memory lane SQL SERVER   Weekly Series   Memory Lane   #037Here 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

Convert Text to Numbers (Integer) – CAST and CONVERT
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.

List All Stored Procedure Modified in Last N Days
If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If a stored procedure was created but never modified afterwards modified date and create a date for that stored procedure are same.

Count Duplicate Records – Rows

Validate Field For DATE datatype using function ISDATE()
We always checked DATETIME field for incorrect data type. One of the user input date as 30/2/2007. The date was sucessfully inserted in the temp table but while inserting from temp table to final table it crashed with error. We had now task to validate incorrect date value before we insert in final table. Jr. Developer asked me how can he do that? We check for incorrect data type (varchar, int, NULL) but this is incorrect date value. Regular expression works fine with them because of mm/dd/yyyy format.

2008

Find Space Used For Any Particular Table
It is very simple to find out the space used by any table in the database.

Two Convenient Features Inline Assignment – Inline Operations
Here is the script which does both – Inline Assignment and Inline Operation

DECLARE @idx INT = 0
SET @idx+=1
SELECT @idx

Introduction to SPARSE Columns
SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).

SP_CONFIGURE – Displays or Changes Global Configuration Settings
If advanced settings are not enabled at configuration level SQL Server will not let user change the advanced features on server. Authorized user can turn on or turn off advance settings.

2009

Standby Servers and Types of Standby Servers
Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server.

BLOB – Pointer to Image, Image in Database, FILESTREAM Storage
When it comes to storing images in database there are two common methods. I had previously blogged about the same subject on my visit to Toronto. With SQL Server 2008, we have a new method of FILESTREAM storage. However, the answer on when to use FILESTREAM and when to use other methods is still vague in community.

2010

Upper Case Shortcut SQL Server Management Studio
I select the word and hit CTRL+SHIFT+U and it SSMS immediately changes the case of the selected word. Similar way if one want to convert cases to lower case, another short cut CTRL+SHIFT+L is also available.

The Self Join – Inner Join and Outer Join
Self Join has always been a noteworthy case. It is interesting to ask questions about self join in a room full of developers. I often ask – if there are three kinds of joins, i.e.- Inner Join, Outer Join and Cross Join; what type of join is Self Join? The usual answer is that it is an Inner Join. However, the reality is very different.

Parallelism – Row per Processor – Row per Thread – Thread 0 
If you look carefully in the Properties window or XML Plan, there is “Thread 0″. What does this “Thread 0” indicate? Well find out from the blog post.

How do I Learn and How do I Teach
The blog post has raised three very interesting questions. How do you learn? How do you teach? What are you learning or teaching? Let me try to answer the same.

2011

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 7 of 31

  • What are Different Types of Locks?
  • What are Pessimistic Lock and Optimistic Lock?
  • When is the use of UPDATE_STATISTICS command?
  • What is the Difference between a HAVING clause and a WHERE clause?
  • What is Connection Pooling and why it is Used?
  • What are the Properties and Different Types of Sub-Queries?
  • What are the Authentication Modes in SQL Server? How can it be Changed?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 8 of 31

  • Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
  • What is an SQL Server Agent?
  • Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
  • What is Log Shipping?
  • Name 3 ways to get an Accurate Count of the Number of Records in a Table?
  • What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
  • What is the Difference between a Local and a Global Temporary Table?
  • What is the STUFF Function and How Does it Differ from the REPLACE Function?
  • What is PRIMARY KEY?
  • What is UNIQUE KEY Constraint?
  • What is FOREIGN KEY?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31

  • What is CHECK Constraint?
  • What is NOT NULL Constraint?
  • What is the difference between UNION and UNION ALL?
  • What is B-Tree?
  • How to get @@ERROR and @@ROWCOUNT at the Same Time?
  • What is a Scheduled Job or What is a Scheduled Task?
  • What are the Advantages of Using Stored Procedures?
  • What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
  • Can SQL Servers Linked to other Servers like Oracle?
  • What is BCP? When is it Used?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 10 of 31

  • What Command do we Use to Rename a db, a Table and a Column?
  • What are sp_configure Commands and SET Commands?
  • How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
  • What is Difference between Commit and Rollback when Used in Transactions?
  • What is an Execution Plan? When would you Use it? How would you View the Execution Plan?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31

  • What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?
  • What is the difference between CHAR and VARCHAR Datatypes?
  • What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?
  • What is the Difference between VARCHAR and NVARCHAR datatypes?
  • Which are the Important Points to Note when Multilanguage Data is Stored in a Table?
  • How to Optimize Stored Procedure Optimization?
  • What is SQL Injection? How to Protect Against SQL Injection Attack?
  • How to Find Out the List Schema Name and Table Name for the Database?
  • What is CHECKPOINT Process in the SQL Server?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 12 of 31

  • How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?
  • How to Find the List of Fixed Hard Drive and Free Space on Server?
  • Why can there be only one Clustered Index and not more than one?
  • What is Difference between Line Feed (\n) and Carriage Return (\r)?
  • Is It Possible to have Clustered Index on Separate Drive From Original Table Location?
  • What is a Hint?
  • How to Delete Duplicate Rows?
  • Why the Trigger Fires Multiple Times in Single Login?

2012

CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis
Shortcut key is CTRL+SHIFT+]. This key can be very useful when dealing with multiple subqueries, CTE or query with multiple parentheses. When exercised this shortcut key it selects T-SQL code between two parentheses.

Monday Morning Puzzle – Query Returns Results Sometimes but Not Always
I am beginner with SQL Server. I have one query, it sometime returns a result and sometime it does not return me the result. Where should I start looking for a solution and what kind of information I should send to you so you can help me with solving. I have no clue, please guide me.

Remove Debug Button in SSMS – SQL in Sixty Seconds #020 – Video

Effect of Case Sensitive Collation on Resultset
Collation is a very interesting concept but I quite often see it is heavily neglected. I have seen developer and DBA looking for a workaround to fix collation error rather than understanding if the side effect of the workaround.

Switch Between Two Parenthesis using Shortcut CTRL+]
Earlier this week I wrote a blog post about CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis, I received quite a lot of positive feedback from readers. If you are a regular reader of the blog post, you must be aware that I appreciate the learning shared by readers.

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

SQL SERVER – Weekly Series – Memory Lane – #036

memory lane SQL SERVER   Weekly Series   Memory Lane   #036Here 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

Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions
How to hide code of my Stored Procedure that no one can see it? 2) Our DBA has left the job and one of the function which retrieves important information is encrypted, how can we decrypt it and find original code?

Comparison SP_EXECUTESQL vs EXECUTE/EXEC
sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation.

Comparison : Similarity and Difference #TempTable vs @TempVariable
#TempTable and @TempVariable are different things with different scope. Their purpose is different but highly overlapping. TempTables are originated for the storage and & storage & manipulation of temporal data. TempVariables are originated (SQL Server 2000 and onwards only) for returning date-sets from table-valued functions.

Definition, Comparison and Difference between HAVING and WHERE Clause
HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

2008

Insert Multiple Records Using One Insert Statement – Use of Row Constructor
How to insert multiple records using One Insert Statement is one of the most asked question in this blog post. Since I have written this blog post, every single time when I am asked this question, I have been referring my readers to this blog post.

Introduction to New Feature of Backup Compression
Backup and Data Storage is my most favorite subject and I have not written about this for some time. I was experimenting with a new feature of SQL Server 2008 and I come across a very interesting feature of Backup compression.

Difference Between Database Mail and SQLMail
Database mail is a newly introduced concept in SQL Server 2005 and it is the replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancements over SQLMail.

Introduction to Row Compression
The row Compression feature applies to zeros and null values and optimize their space in SQL Server. In fact, due to Row Compression feature SQL Server does not take any disk space for zero or null values.

2009

Difference between Line Feed (\n) and Carriage Return (\r) – T-SQL New Line Char
What is the difference between Line Feed (\n) and Carriage Return (\r)?

Prior to continuing with this article let us first look into a few synonyms for LF and CR.

Line Feed – LF – \n – 0x0a – 10 (decimal)

Carriage Return – CR – \r – 0x0D – 13 (decimal)

2010

Introduction to Best Practices Analyzer – Quick Tutorial
This blog post explains step by step how one can use Best Practices Analyzer tool from Microsoft.

Parallelism – Row per Processor – Row per Thread
This blog post tries to answer following question – “When SQL Server executes any query on multiple processors, do all processors process equal numbers of rows?”

View XML Query Plans in SSMS as Graphical Execution Plan
You can save execution plan with the extension .sqlplan. The same plan can be sent to another user via email or a USB drive. Another user can just double click on the file and open the execution plan at another local computer without physically having any underlying object.

Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats
The following is the diagram on Clustered Index that I have quickly drawn using MS Word for the said developer.

Clustered Index B-Tree
Clustered Index B-Tree

PowerShell Version Info
I recently had a scenario where I was listing a PowerShell version installed in my computer systems. While searching online, I found two different commands that can determine the version of PowerShell. One of them worked fine in Version 1, while both worked on Version 2.

2011

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Introduction – Day 1 of 31

In this very first blog post – various aspect of the interview questions and answers are discussed. Some people like the subject for their helpful hints and thought provoking subject, and others dislike these posts because they feel it is nothing more than cheating.  I’d like to discuss the pros and cons of a Question and Answer format here.

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 2 of 31

  • What is RDBMS?
  • What are the Properties of the Relational Tables?
  • What is Normalization?
  • What is De-normalization?
  • How is ACID property related to Database?
  • What are the Different Normalization Forms?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 3 of 31

  • What is a Stored Procedure?
  • What is a Trigger?
  • What are the Different Types of Triggers?
  • What is a View?
  • What is an Index?
  • What is a Linked Server?
  • What is a Cursor?
  • What is Collation?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 4 of 31

  • What is the Difference between a Function and a Stored Procedure?
  • What is subquery? Explain the Properties of a Subquery?
  • What are Different Types of Join?
  • What are Primary Keys and Foreign Keys?
  • What is User-defined Functions? What are the types of User-defined Functions that can be created?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 5 of 31

  • What is an Identity?
  • What is DataWarehousing?
  • What languages BI uses to achieve the goal?
  • What is Standby Servers? Explain Types of Standby Servers.
  • What is Dirty Read?
  • Why can’t I use Outer Join in an Indexed View?
  • What is the Correct Order of the Logical Query Processing Phases?

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 6 of 31

  • Which TCP/IP port does the SQL Server run on? How can it be Changed?
  • What are the Difference between Clustered and a Non-clustered Index?
  • What are the Different Index Configurations a Table can have?
  • What are Different Types of Collation Sensitivity?
  • What is OLTP (Online Transaction Processing)?
  • What’s the Difference between a Primary Key and a Unique Key?
  • What is Difference between DELETE  and TRUNCATE Commands?

2012

Validating Spatial Object as NULL using IsNULL
How is NULL handled by spatial functions? Well, NULL is NULL. It is very easy to work with NULL. There are two different ways to validate if the passed in the value is NULL or not. There are two different methods described in this blog post where this is discussed in detail.

Discard Results After Query Execution – SSMS
In SSMS 2012 go to Tools >> Options >> Query Results > SQL Server >> Results to Grid >> Discard Results After Query Execution. When enabled this option will discard results after the execution. The advantage of disabling the option is that it will improve the performance by using less memory.

Monitoring SQL Server Database Transaction Log Space Growth – DBCC SQLPERF(logspace) – Puzzle for You
In this blog post I have described how one can monitor the log space growth. After I have described the DMV method, I have asked two interesting puzzle. If you can answer it today, it is great, but if you can’t answer it, you need to continue reading the blog post.

Tricks to Comment T-SQL in SSMS – SQL in Sixty Seconds #019 – Video

Retrieve SQL Server Installation Date Time
Do you know when was your SQL Server installed? If you do not know you can figure it out using two different methods described in this blog post.

NTFS File System Performance for SQL Server
In this blog post I describe following a very essential topics which directly relates to SQL Server Performance and lots of other issues. I personally liked this blog post a lot.

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

SQL SERVER – Weekly Series – Memory Lane – #035

memory lane SQL SERVER   Weekly Series   Memory Lane   #035Here 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

Row Overflow Data Explanation
 In SQL Server 2005 one table row can contain more than one varchar(8000) fields. One more thing, the exclusions has exclusions also the limit of each individual column max width of 8000 bytes does not apply to varchar(max), nvarchar(max), varbinary(max), text, image or xml data type columns.

Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005
An old but like a gold article. Talks about lots of concepts related to Index and the difference from earlier version to the newer version. I strongly suggest that everyone should read this article just to understand how SQL Server has moved forward with the technology.

Improvements in TempDB
SQL Server 2005 had come up with quite a lots of improvements and this blog post describes them and explains the same. If you ask me what is my the most favorite article from early career. I must point out to this article as when I wrote this one I personally have learned a lot of new things.

Recompile All The Stored Procedure on Specific Table
I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles marks stored procedures to recompile when they execute next time. This blog post explains the same with the help of a script. 

2008

SQLAuthority Download – SQL Server Cheatsheet You can download and print this cheat sheet and use it for your personal reference. If you have any suggestions, please let me know and I will see if I can update this SQL Server cheat sheet.

Difference Between DBMS and RDBMS What is the difference between DBMS and RDBMS?

DBMS – Data Base Management System RDBMS – Relational Data Base Management System or Relational DBMS High Availability – Hot Add Memory Hot Add CPU and Hot Add Memory are extremely interesting features of the SQL Server, however, personally I have not witness them heavily used. These features also have few restriction as well. I blogged about them in detail.

2009

Delete Duplicate Rows I have demonstrated in this blog post how one can identify and delete duplicate rows.

Interesting Observation of Logon Trigger On All Servers – Solution The question I put forth in my previous article was – In single login why the trigger fires multiple times; it should be fired only once. I received numerous answers in thread as well as in my MVP private news group. Now, let us discuss the answer for the same. The answer is – It happens because multiple SQL Server services are running as well as intellisense is turned on. Blog post demonstrates how we can do the same with the help of SQL scripts.

Management Studio New Features I have selected my favorite 5 features and blogged about it.

  1. IntelliSense for Query Editing
  2. Multi Server Query
  3. Query Editor Regions
  4. Object Explorer Enhancements
  5. Activity Monitors

Maximum Number of Index per Table One of the questions I asked in my user group was – What is the maximum number of Index per table? I received lots of answers to this question but only two answers are correct. Let us now take a look at them in this blog post.

2010

Default Statistics on Column – Automatic Statistics on Column The truth is, Statistics can be in a table even though there is no Index in it. If you have the auto- create and/or auto-update Statistics feature turned on for SQL Server database, Statistics will be automatically created on the Column based on a few conditions. Please read my previously posted article, SQL SERVER – When are Statistics Updated – What triggers Statistics to Update, for the specific conditions when Statistics is updated.

2011

T-SQL Scripts to Find Maximum between Two Numbers In this blog post there are two different scripts listed which demonstrates way to find the maximum number between two numbers. I need your help, which one of the script do you think is the most accurate way to find maximum number?

Find Details for Statistics of Whole Database – DMV – T-SQL Script I was recently asked is there a single script which can provide all the necessary details about statistics for any database. This question made me write following script. I was initially planning to use sp_helpstats command but I remembered that this is marked to be deprecated in future.

2012

Introduction to Function SIGN SIGN Function is very fundamental function. It will return the value 1, -1 or 0. If your value is negative it will return you negative -1 and if it is positive it will return you positive +1. Let us start with a simple small example.

Template Browser – A Very Important and Useful Feature of SSMS Templates are like a quick cheat sheet or quick reference. Templates are available to create objects like databases, tables, views, indexes, stored procedures, triggers, statistics, and functions. Templates are also available for Analysis Services as well. The template scripts contain parameters to help you customize the code. You can Replace Template Parameters dialog box to insert values into the script.

An invalid floating point operation occurred If you run any of the above functions they will give you an error related to invalid floating point. Honestly there is no workaround except passing the function appropriate values. SQRT of a negative number will give you result in real numbers which is not supported at this point of time as well LOG of a negative number is not possible (because logarithm is the inverse function of an exponential function and the exponential function is NEVER negative).

Validating Spatial Object with IsValidDetailed Function SQL Server 2012 has introduced the new function IsValidDetailed(). This function has made my life very easy. In simple words, this function will check if the spatial object passed is valid or not. If it is valid it will give information that it is valid. If the spatial object is not valid it will return the answer that it is not valid and the reason for the same. This makes it very easy to debug the issue and make the necessary correction.

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

SQL SERVER – Weekly Series – Memory Lane – #034

memory lane SQL SERVER   Weekly Series   Memory Lane   #034Here 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

UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression
The UDF used in the blog does fantastic task – it scans entire HTML text and removes all the HTML tags. It keeps only valid text data without HTML task. This is one of the quite commonly requested tasks many developers have to face everyday.

De-fragmentation of Database at Operating System to Improve Performance
Operating system skips MDF file while defragging the entire filesystem of the operating system. It is absolutely fine and there is no impact of the same on performance. Read the entire blog post for my conversation with our network engineers.

Delay Function – WAITFOR clause – Delay Execution of Commands
How do you delay execution of the commands in SQL Server – ofcourse by using WAITFOR keyword. In this blog post, I explain the same with the help of T-SQL script.

Find Length of Text Field
To measure the length of TEXT fields the function is DATALENGTH(textfield). Len will not work for text field. As of SQL Server 2005, developers should migrate all the text fields to VARCHAR(MAX) as that is the way forward.

Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
There are three ways to retrieve the current datetime in SQL SERVER. CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

Explanation and Comparison of NULLIF and ISNULL
An interesting observation is NULLIF returns null if it comparison is successful, whereas ISNULL returns not null if its comparison is successful. In one way they are opposite to each other.

Here is my question to you – How to create infinite loop using NULLIF and ISNULL? If this is even possible?

2008

Introduction to SERVERPROPERTY and example
SERVERPROPERTY is a very interesting system function. It returns many of the system values. I use it very frequently to get different server values like Server CollationServer Name etc.

SQL Server Start Time
We can use DMV to find out what is the start time of SQL Server in 2008 and later version. In this blog you can see how you can do the same.

Find Current Identity of Table
Many times we need to know what is the current identity of the column. I have found one of my developers using aggregated function MAX () to find the current identity. However, I prefer following DBCC command to figure out current identity.

Create Check Constraint on Column
Some time we just need to create a simple constraint over the table but I have noticed that developers do many different things to make table column follow rules than just creating constraint. I suggest constraint is a very useful concept and every SQL Developer should pay good attention to this subject.

2009

List Schema Name and Table Name for Database
This is one of the blog post where I straight forward display script. One of the kind of blog posts, which I still love to read and write.

Clustered Index on Separate Drive From Table Location
A table devoid of primary key index is called heap, and here data is not arranged in a particular order, which gives rise to issues that adversely affect performance. Data must be stored in some kind of order. If we put clustered index on it then the order will be forced by that index and the data will be stored in that particular order.

Understanding Table Hints with Examples
Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.

2010

Data Pages in Buffer Pool – Data Stored in Memory Cache
One of my earlier year article, which I still read it many times and point developers to read it again. It is clear from the Resultset that when more than one index is used, datapages related to both or all of the indexes are stored in Memory Cache separately.

TRANSACTION, DML and Schema Locks
Can you create a situation where you can see Schema Lock? Well, this is a very simple question, however during the interview I notice over 50 candidates failed to come up with the scenario. In this blog post, I have demonstrated the situation where we can see the schema lock in database.

2011

Solution – Puzzle – Statistics are not updated but are Created Once
In this example I have created following situation:

  • Create Table
  • Insert 1000 Records
  • Check the Statistics
  • Now insert 10 times more 10,000 indexes
  • Check the Statistics – it will be NOT updated
  • Auto Update Statistics and Auto Create Statistics for database is TRUE

Now I have requested two things in the example 1) Why this is happening? 2) How to fix this issue?

Selecting Domain from Email Address
This is a straight to script blog post where I explain how to select only domain name from entire email address.

Solution – Generating Zero Without using Any Numbers in T-SQL
How to get zero digit without using any digit? This is indeed a very interesting question and the answer is even interesting. Try to come up with answer in next 10 minutes and if you can’t come up with the answer the blog post read this post for solution.

2012

Simple Explanation and Puzzle with SOUNDEX Function and DIFFERENCE Function
In simple words – SOUNDEX converts an alphanumeric string to a four-character code to find similar-sounding words or names. DIFFERENCE function returns an integer value. The  integer returned is the number of characters in the SOUNDEX values that are the same.

Read Only Files and SQL Server Management Studio (SSMS)
I have come across a very interesting feature in SSMS related to “Read Only” files. I believe it is a little unknown feature as well so decided to write a blog about the same.

Identifying Column Data Type of uniqueidentifier without Querying System Tables
How do I know if any table has a uniqueidentifier column and what is its value without using any DMV or System Catalogues? Only information you know is the table name and you are allowed to return any kind of error if the table does not have uniqueidentifier column. Read the blog post to find the answer.

Solution – User Not Able to See Any User Created Object in Tables – Security and Permissions Issue
Interesting question – “When I try to connect to SQL Server, it lets me connect just fine as well let me open and explore the database. I noticed that I do not see any user created instances but when my colleague attempts to connect to the server, he is able to explore the database as well see all the user created tables and other objects. Can you help me fix it?”

Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video
Here is interesting small 60 second video on how to import CSV file into Database.

ColumnStore Index – Batch Mode vs Row Mode
Here is the logic behind when Columnstore Index uses Batch Mode and when it uses Row Mode. A batch typically represents about 1000 rows of data. Batch mode processing also uses algorithms that are optimized for the multicore CPUs and increased memory throughput.

Follow up – Usage of $rowguid and $IDENTITY
This is an excellent follow up blog post of my earlier blog post where I explain where to use $rowguid and $identity.  If you do not know the difference between them, this is a blog with a script example.

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