Interview Question of the Week #008 – Is GO T-SQL Statement?

I often get this question in the email – more frequently than I would like to get.

Question: “Is GO a T-SQL statement?”

Answer: “No, GO is not a T-SQL statement, it is a command which is recognized by SSMS or sqlcmd as a signal to send the current batch to SQL Server Engine.”

Well, here are two articles I recommend to read for more information – MSDN and Explanation SQL Command GO.

Here are few things I would like to add related to the GO statement.

  • If recommend a statement terminator semicolon after each statement, however, do not use semicolon after the GO statement or it will give an error.
  • ODBC or OLE DB API does not recognize the GO statement and it will give a syntax error.
  • You can use GO in a next line of T-SQL Statement. If you provide an integer after GO statement, it will execute that statement multiple times.
  • You can only have comments in the same line as a GO statement, any other statement will error.
  • You can change the command GO to any other value in SSMS by going to Tools > Option

Let me know your thoughts about the GO statement.

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

Interview Question of the Week #008 – Write Scripts to Convert String to Title Case or Proper Case

Just received a question in email:

“I just got out of the interview and I was asked to write scripts to convert a string to Title Case. I was also asked to refer to the website over here to validate my answer after I complete writing script.

Do you think it is not fair to ask such questions in an interview?”

Well, this time the question is not about writing scripts, but more about if this kind of questions is a good question for interview or not. The question is a bit open ended and my answer can be vague or full of philosophy of it depends. However, I will try to answer it bit more honest and clear.

I believe all questions are good question in the interview. It is never about the question’s validity, but more about quality of answer and attitude decides the capability of the user. If I ever face a situation where I am asked questions to write script to convert a string to title case, I would happily sit down and attempt to write it if I want the job and I am up for the challenge. If I do not know how to write it, I will honestly say I do not know and let the interviewer decide the next step. If you believe this kind of question is inappropriate for the job which you have applied, well, you can always mention that after you solve it.

Well – in my early career, I have written similar script and I have posted that on this blog post well. You can refer that in this blog post – SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case. I strongly suggest that you read the comments as those are packed with the wealth of the information. Here is the script from the blog post.

CREATE FUNCTION udf_TitleCase (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE
@Index INT
DECLARE
@Char CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString =
STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
WHILE @Index <= LEN(@InputString)
BEGIN
SET
@Char = SUBSTRING(@InputString, @Index, 1)
IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF
@Char != ''''
OR
UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
SET @OutputString =
STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
SET
@Index = @Index + 1
END
RETURN
ISNULL(@OutputString,'')
END

You can use this script as follows:

SELECT dbo.udf_TitleCase('This function will convert this string to title case!')

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

Interview Question of the Week #007 – How to Reindex Every Table of the Database?

Some questions are extremely popular questions and they never get old. Here is one such question which I see very often asked to DBAs in their early career.

Question: How to re-index every table of the database?

Answer: Well, The answer of this question can be only given in the form of the script.

For SQL Server 2014 and later version

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET
@fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
WHERE is_memory_optimized = 0
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET
@sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE
TableCursor
DEALLOCATE TableCursor
GO

(Remember that alter index will fail on in-memory table, hence they needs to be excluded)

For SQL Server 2005, 2008 and 2012 versions

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET
@fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET
@sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE
TableCursor
DEALLOCATE TableCursor
GO

For SQL Server 2000 version

DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT
table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
'Reindexing Table:  ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE
myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
GO

Well, there are many different methods and many different variations out there for this script, however, above script has always worked for me and I trust them.

Here are few related blog posts one should refer for further information.

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

Interview Question of the Week #006 – Is Shrinking Database Good or Bad?

Here is the most debated Interview Question – Is Shrinking Database Good or Bad?

I will try to answer this in a single statement – “Shrinking Database is bad practice for performance as it increases fragmentation. It should be used in rare cases of running out of space on drive.”

I believe above statement is not enough to explain the various details associated with it. Here are few reference blog posts, I strongly suggest to read with regards to this subject.

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

Interview Question of the Week #005 – List All the Tables with Name Containing Specific String

The other day, I received an email from a blog reader. When he appeared for an interview, he was asked to retrieve all the tables where the name of the table contained the words ‘tbls_’. This was a very interesting scenario. As the organization had earlier adopted a specific naming convention where they were writing the string ‘tbls_’ before any table name. However, as the new manager joined the organization, he decided to remove this static prefix and wanted the list of the all the tables where the name of the table contained ‘tbls_’ anywhere in the table. I think this is a very common question and we often see this particular requirement in the industry.

Question: How do you list all the tables in the database where the name of the table contains specific strings like ‘tbls_’ anywhere in the name of the table.

Answer: Here is a simple script which can list all the tables with specific string in the name of the table.

DECLARE @SearchString VARCHAR(256)
SET @SearchString = 'tbls_%' -- Change SearchString
SELECT name FROM sysobjects
WHERE TYPE = 'U'
AND crdate <= DATEADD(m,-1,GETDATE())
AND
name LIKE '%'+@SearchString+'%'

Remember this script is using LIKE keyword with a % sign before and after searching string, hence it may be a bit slower in performance, but will for sure return accurate results. You can change the like condition based on your business need.

Here is the similar script which I had written a few years ago with the help of the cursor SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created.

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

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)