Interview Question of the Week #016 – How to Take Database Offline

Here is the question, I received in the recent conference where I was presenting on Database Technology.

Question: What does it mean by Taking Database Offline and How to do it?

Answer: Taking database offline means, it will be no more available for database operations. Here are is how you can take your database offline.

-- Take the Database Offline
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO

If you have previously taken database offline, you can once again take it online by running the following command:

-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO

You can read more about it in following blogs:

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

Interview Question of the Week #014 – How to DELETE Duplicate Rows

Here is another very common question I often received, which candidates face in the interview.

Question: How to delete duplicate rows in SQL Server (considering there is an identity column in table)?

Answer: Well, there is no better answer than following a script. If you ask me this question in the interview, I will even have to look up online and find following script:

DELETE
FROM
MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Here is the quick video I have recorded where I demonstrate the same functionality in simple words.

http://www.youtube.com/watch?v=ioDJ0xVOHDY

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

Interview Question of the Week #013 – Stored Procedure and Its Advantages – How to Create Stored Procedure

There are some questions which are so old that they should not be asked in the interview and gets old. Here is one of the questions I have spotted so many times in the interview that if co-interviewer asks to the candidate, I often feel bored (well, I was caught yawning last time). Here is the question:

Question: “How do you create stored procedure? and What are the advantages of the stored procedure?”

Answer: Well, as I said, I find this question are age old, so if you search on the internet you will find many different answers. Just make sure you read the answers which are from the latest version of SQL Server as stored procedure have changed a bit since the inception of the same.

Here are few of the advantages of the stored procedure:

  • Execution plan retention and reuse
  • Query auto-parameterization
  • Encapsulation of business rules and policies
  • Application modularization
  • Sharing of application logic between applications
  • Access to database objects that is both secure and uniform
  • Consistent, safe data modification
  • Network bandwidth conservation
  • Support for automatic execution at system start-up
  • Enhanced hardware and software capabilities
  • Improved security
  • Reduced development cost and increased reliability
  • Centralized security, administration, and maintenance for common routines

You can read the original article over here.

If you want to create stored procedure you can create with following simple script:

CREATE PROCEDURE MyFirstSP
AS
SELECT
GETDATE();
GO

You can execute above stored procedure by following a script:

EXEC MyFirstSP
GO

The above stored procedure will return results as a current date time.

 spcreate Interview Question of the Week #013   Stored Procedure and Its Advantages   How to Create Stored Procedure

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

Interview Question of the Week #012 – Steps to Restore Bak File to Database

Here is the question, I was asked the other day at the conference.

Question: If I have a .bak file and I want to restore it database what are the various steps involved in it. Assume that I do not know much about that backup file.

Answer: I love this question, as it has a clear need, but there is ambiguity in the process.

Step 1: Retrieve the logical file name of the database

Step 2: Bring database to single user

Step 3: Restore database with the help of the logical file name retrieved earlier.

Step 4: Bring database to multi user mode.

Here are all the four steps together:

----Retrieve logical file name
RESTORE FILELISTONLY
FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
GO
----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
GO
----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:\DataYourLDFFile.ldf'
GO
----Make Database to multi user Mode
ALTER DATABASE YourDB SET MULTI_USER
GO

Here is the blog post I wrote earlier, which describes the same process SQL SERVER – Restore Database Backup using SQL Script (T-SQL).

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

Interview Question of the Week #011 – Script to Convert List to Table and Table to List

Now today’s interview question is really more of daily routine task for many of the developer. 11 years ago, when I went to my very first interview call. My interview was scheduled at 6 PM in one of the very well known corporation. The corporation was known for its first initiatives for social media. When I reached there at 6 PM there were many candidates and we all got the same task to complete. The task was as follows:

Task 1) Write a script to convert a list to a table

Task 2) Write a script to convert the table to a list

We all had two hours of time to complete the task. There were quite a few developers who had completed the task, but I had failed the interview as I was not good with SQL Scripting. After I return from the interview, I had decided to write scripts for my personal learning. I did not want to fail the interviews in the future if I was ever going to get that task.

Here are the answers for both the tasks.

Answer for Task 1: SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP

Answer for Task 2: SQL SERVER – UDF – Function to Convert List to Table

The irony of the life is that I never got the same task again in future. Here is the question back to you – can you guess the name of the organization which was based out of California and one of the early social media giants?

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

Interview Question of the Week #010 – What is the Difference Between Primary Key Constraints and Unique Key Constraints?

I often see people getting confused between Primary Key and Unique Key. I see where people get confused about the same. Let us discuss.

Question: “What is the Difference Between Primary Key Constraints and Unique Key Constraints?”

Answer: “Primary Key (PK) Constraints enforce uniqueness just like Unique Key (UK) Constraints, however, PK does not allow  NULL value where as UK allows one NULL value.”

Well, the answer is as simple as above statement. You can read more about this over here: Primary Key Constraints and Unique Key Constraints.

I am very sure after reading above statement there will be few readers, who is going to comment and write email that when we create Primary Key Constraint it also creates clustered index on that column but Unique Key Constraint does not create clustered index. Well, this is the specific (and default) behavior in SQL Server where clustered index is automatically created, however user can create Primary Key Constraint without creating a clustered index as well and you can read about this over here Primary Key and NonClustered Index in Simple Words.

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

Interview Question of the Week #009 – 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.
    gostatement1 Interview Question of the Week #009   Is GO T SQL Statement?
  • 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
    gostatement2 Interview Question of the Week #009   Is GO T SQL Statement?

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?”

casestatement Interview Question of the Week #008   Write Scripts to Convert String to Title Case or Proper CaseWell, 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)