Interview Question of the Week #004 – List All Columns and Their Data Type for a View

Earlier this week I wrote a blog about finding stored procedure parameters and their data types. After that blog I received few emails from my blog readers asking for similar script for a view. I asked them what did they like about the script and they said that it gives us base datatype and user defined data type as well. So I have put some more efforts to write similar script about view. This is a fine example of how each one of you out there inspire me with some great content ideas. These keep these emails coming my way.

Here is the question – How to list all columns and their datatype for a view in SQL Server?

Usage of script

To use the below script you need to replace the view name (vEmployee in sample code) and schema name (HumanResources in sample code). Also make sure you are in same database which has stored procedure (AdventureWorks2014 is sample code):

USE AdventureWorks2014
GO
DECLARE  @ViewName NVARCHAR(4000)
       ,
@SchemaName NVARCHAR(4000)
SELECT   @ViewName = N'vEmployee'
      
,@SchemaName = N'HumanResources'
SELECT c.NAME AS [Name]
  
,CAST(ISNULL(ic.index_column_id, 0) AS BIT) AS [InPrimaryKey]
  
,CAST(ISNULL((
              
SELECT TOP 1 1
              
FROM sys.foreign_key_columns AS colfk
              
WHERE colfk.parent_column_id = c.column_id
                  
AND colfk.parent_object_id = c.OBJECT_ID
              
), 0) AS BIT) AS [IsForeignKey]
  
,u_t.NAME AS [DataType]
  
,ISNULL(b_t.NAME, N'') AS [SystemType]
  
,CAST(CASE
          
WHEN b_t.NAME IN (
                  
N'nchar'
                  
,N'nvarchar'
                  
)
               AND
c.max_length <> - 1
              
THEN c.max_length / 2
          
ELSE c.max_length
          
END AS INT) AS [Length]
  
,CAST(c.PRECISION AS INT) AS [NumericPrecision]
  
,CAST(c.scale AS INT) AS [NumericScale]
  
,c.is_nullable AS [Nullable]
  
,c.is_computed AS [Computed]
  
,ISNULL(s.NAME, N'') AS [XmlSchemaNamespaceSchema]
  
,ISNULL(xsc.NAME, N'') AS [XmlSchemaNamespace]
  
,ISNULL((
          
CASE c.is_xml_document
              
WHEN 1
                  
THEN 2
              
ELSE 1
              
END
          
), 0) AS [XmlDocumentConstraint]
  
,CAST(c.is_sparse AS BIT) AS [IsSparse]
  
,CAST(c.is_column_set AS BIT) AS [IsColumnSet]
  
,c.column_id AS [ID]
FROM sys.all_views AS v
INNER JOIN sys.all_columns AS c ON c.OBJECT_ID = v.OBJECT_ID
LEFT
JOIN sys.indexes AS i ON i.OBJECT_ID = c.OBJECT_ID
  
AND 1 = i.is_primary_key
LEFT JOIN sys.index_columns AS ic ON ic.index_id = i.index_id
  
AND ic.column_id = c.column_id
  
AND ic.OBJECT_ID = c.OBJECT_ID
  
AND 0 = ic.is_included_column
LEFT JOIN sys.types AS u_t ON u_t.user_type_id = c.user_type_id
LEFT JOIN sys.types AS b_t ON (
      
b_t.user_type_id = c.system_type_id
      
AND b_t.user_type_id = b_t.system_type_id
      
)
   OR (
       (
b_t.system_type_id = c.system_type_id)
       AND (
b_t.user_type_id = c.user_type_id)
       AND (
b_t.is_user_defined = 0)
       AND (
b_t.is_assembly_type = 1)
       )
LEFT JOIN sys.xml_schema_collections AS xsc ON xsc.xml_collection_id = c.xml_collection_id
LEFT JOIN sys.schemas AS s ON s.schema_id = xsc.schema_id
WHERE (v.TYPE = 'V')
   AND (
      
v.NAME = @ViewName
      
AND SCHEMA_NAME(v.schema_id) = @SchemaName
      
)
ORDER BY [ID] ASC

Here is the sample execution. I have highlighted the modification needed to use the script.

I hope these scripts will help you in your environments. I would love to hear back from you how these can be enhanced if possible.

Click to Download Scripts

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

Interview Question of the Week #003 – How to Write Script for Database Cursor?

Just another day I heard a senior DBA discussing advantages and disadvantages of the cursor. Well, personally I am a firm believer of the set based operations. However, there are few places where cursors are the only solutions. It is quite possible that different experts have different opinion about cursor; some love cursor and some hate it but definitely user can’t ignore cursors.

If you are ever asked to write a cursor, you can just write cursor based on following a script.

USE AdventureWorks2014
GO
DECLARE @ProductID INT
DECLARE
@getProductID CURSOR
SET
@getProductID = CURSOR FOR
SELECT
ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
@ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE
@getProductID
DEALLOCATE @getProductID
GO            

You can read about cursor examples over here Simple Example of Cursor – Sample Cursor Part 2.

Here are few additional examples of cursors:

Cursor uses WHILE keyword to loop over variables. If you are working with local variable you can also use while loop. Here is an example of WHILE loop where the variable is incremented by 1 at every iteration.

DECLARE @intFlag INT
SET
@intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT
@intFlag
SET @intFlag = @intFlag + 1
END
GO

You can read about this over Simple Example of WHILE Loop With CONTINUE and BREAK Keywords.

This is just a simple example of cursor, in reality there are quite a few different options of the cursor as well. We will discuss this in future blog posts.

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

Interview Question of the Week #002 – Script to Find Byte Size of a Row for All the Tables in Database

In recent times I was part of the interview panel where I was stunned to see that following question was asked to interviewee. I personally believed that this question is no longer relevant as we are in the age where database are capable to handle large amount of data and the row size is no more significant. However, the question was still asked and the interviewee has to still answer it with regards to the latest version of SQL Server. Here it goes:

Question: How to find total row size in bytes for any table in SQL Server?

Answer: In SQL Server 2000 and earlier version it was crucial to know that byte size of the row as there was limit to how big a row can be. This limit has been removed in the recent versions of SQL Server so this question is no more relevant now.

Here is the script for recent versions of SQL Server. I have an additional column which indicates that if a table contains any column with datatype MAX.

SELECT
CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
SUM(scol.max_length) AS Approx_Max_Length,
CASE WHEN MIN(scol.max_length) = -1 THEN 'Yes'
ELSE 'No' END AS Fieldwith_MAX_Datatype
FROM sys.objects sob
INNER JOIN sys.columns scol ON scol.OBJECT_ID=sob.OBJECT_ID
WHERE sob.TYPE='U'
GROUP BY sob.name
WITH CUBE          

If you are using SQL Server 2000, here is the blog post which contains the answer SQL SERVER – Query to Find ByteSize of All the Tables in Database.

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

Interview Question of the Week #001 – Script to List Foreign Key Relationships and Constraint Name

Two years ago I wrote a book SQL Server Interview Questions and Answers with my very close friend Vinod Kumar. It is a very popular book of mine and has sold many thousands of copies so far. Every single day I still get quite a many different interview questions in email and users wants me to answer them. I have been collecting them for quite a while and in this new year, I am starting this new series, which will take a one interview question sent by readers and will try to answer in short and simple words. The goal of this series is to build a knowledge on the subject. If you want to pass any interview, you should know much more than this question and answers. You should have real world experience. Read this blog post over here before you continue reading this story.

Well, let us start this series. In this week’s episode we will try to answer following question -

Question: Write a script to display foreign key relationships and name of the constraint for each table in the database for SQL Server 2005 and later version.

Answer: Here is the script which will display foreign key relationships and name of the constraint for each table in the database.

SQL Server 2005 and later version

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Reference Link

If you execute the above query with Adventure sample database, it will give following result set.

SQL Server 2000

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4

Reference Link

If you have a better answer, please leave a comment and I will include the answer with due credit.

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

SQL SERVER – How to Catch Errors While Inserting Values in Table

Question: “I often get errors when I insert values into a table, I want to gracefully catch them, how do I do that.”

Answer: Very simple. Just use TRY… CATCH. Here is the simple example of TRY…CATCH I have blogged earlier when it was introduced.

Here is the example, I have build from the earlier blog post where user can catch the error details during inserting value in table.

First, we will create a sample table.

CREATE TABLE SampleTable (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO

Now we will attempt to insert value in this table which will throw errors and the same error we will catch into the table.

BEGIN TRY
INSERT INTO SampleTable (Col)
SELECT 'FourthRow'
UNION ALL
SELECT 'FifthRow---------'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
GO

The second row of the above table will throw an error as the length of the row is larger than the column in which we are inserting values. It will throw an error and the same error will be caught via TRY…CATCH and it will be displayed in the SELECT statement. Here is the result set.

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

SQL SERVER – Basics of Delayed Durability in SQL Server 2014

SQL Server 2014 has introduced many new features and one of performance is Delayed Transaction Durability. This concept is indeed very interesting. To explain this feature in detail, we will require to understand what is Full Transaction Durability. The current default of SQL Server is Full Transaction Durability. A common question I often received is why would we go away from to delayed durability. The answer is – Performance prioritation over Durability.

I try to stay as brief as possible to explain various concepts over here. I will try to convert this blog post in questions, and answer format so it is easy to understand.

What is Full Transaction Durability?
In Full Transaction Durability transactions write the transaction log to disk before returning control to the client / user. This means your data is safe on disk before it is committed. SQL Server uses Write Ahead Log logic for this default behavior of Full Transaction Durability.

What is Delayed Transaction Durability?
In Delayed Transaction Durability transactions are written asynchronously and in batches to the disk before returning control to the client / user. This means your transaction log records are kept buffer and written to disk either when it is filled up or buffer flush happens.

When to use Full Transaction Durability?
You should full transaction durability when you can’t afford any data loss.

When to use Delayed Transaction Durability?
You should use Delayed Transaction Durability when performance is priority over data loss.

What particular sign I should observe in my SQL Server, which suggests that if I implement Delayed Durability it will improve my performance?
Well, if your performance issue is due to latency in transaction log writes, or in another word, if you notice the bottleneck on transaction log writes, you can implement delayed transaction durability.

When are transaction written to the disk in case of delayed transaction durability?
There are three different scenarios, when in-memory transaction logs are flushed to the disk (as per MSDN).

  • A fully durable transaction in the same database makes a change in the database and successfully commits.
  • The user executes the system stored procedure sp_flush_log successfully.
  • The in-memory transaction log buffer fills up and automatically flushes to disk. (Update: One of my SQL Expert friend suggest this statement requires amendment and it is on the way, I will update once I have more details).

Well, that’s it for today. In upcoming blog posts we will see a working demo for Delayed Transaction Durability.

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

SQL SERVER – What is the Difference Between Latches and Locks

A very common question I often get in the email is

“What is the difference between latches and locks?”

The answer to this question is not an easy one. Here is a quick answer from the MS white paper.

A page in SQL Server is 8KB and can store multiple rows. To increase concurrency and performance, buffer latches are held only for the duration of the physical operation on the page, unlike locks which are held for the duration of the logical transaction. Latches are internal to the SQL engine and are used to provide memory consistency, whereas locks are used by SQL Server to provide logical transactional consistency.

Above answer is taken from the old, but extremely relevant white paper written in the year 2011. It was written for SQL Server 2008 R2, however, almost all the concepts from this Whitepaper are still very accurate.

If you want to learn more about Latches and how to diagnose and resolve problems around latch contention, I strongly suggest following whitepaper.

Diagnosing and Resolving Latch Contention on SQL Server

Let me know your thoughts about this subject.

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