Sql server interview questions

To rename db
sp_renamedb 'old_database_name' , 'new_database_name'

If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.
E.g.

USE master;

EXEC sp_dboption AdventureWorks, 'Single User', True

EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New'

EXEC sp_dboption AdventureWorks, 'Single User', False

To rename Table
We can change the table name using sp_rename as follows, 

sp_rename 'old_TableName' 'new_TableName'

E.g.

sp_RENAME 'Table_First', 'Table_Last'

To rename Column
The script for renaming any column :

sp_rename 'TableName.[Old_columnName]', 'New_ColumnName', 'Column'

E.g.

sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'

What command do we use to rename a db? 
sp_renamedb ‘oldname’ , ‘newname’
If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

What is sp_configure commands and set commands?
Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

What are the different types of replication? Explain.
The SQL Server 2000-supported replication types are as follows:

• Transactional
• Snapshot
• Merge

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.

What are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)

What are three SQL keywords used to change or set someone’s permissions?GRANT, DENY, and REVOKE.

What does it mean to have quoted_identifier on? What are the implications of having it off?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

What is the STUFF function and how does it differ from the REPLACE function? 
STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.
REPLACE function to replace existing characters of all occurance. Using this syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?
SELECT *FROM table1SELECT COUNT(*)FROM table1SELECT rowsFROM sysindexesWHERE id = OBJECT_ID(table1)
AND indid < 2
How to rebuild Master Database?
Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
In the Rebuild Master dialog box, click Browse.
In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.
To continue, you may need to stop a server that is running.
Source: http://msdn2.microsoft.com/en-us/library/aa197950(SQL.80).aspx

What is the basic functions for master, msdb, model, tempdb databases?
The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.

What are primary keys and foreign keys? 
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is data integrity? Explain constraints?
Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What is sp_configure commands and set commands?

Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

@@SPID (Transact-SQL)

Returns the session ID of the current user process.

@@SPID

-- Azure SQL Data Warehouse and Parallel Data Warehouse
@@SPID

This example returns the session ID, login name, and user name for the current user process.




SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name';




ID     Login Name                     User Name                     
------ ------------------------------ ------------------------------
54     SEATTLE\joanna                 dbo    


Examples: Azure SQL Data Warehouse Public Preview and Parallel Data Warehouse










This example returns the SQL Data Warehouse session ID, the SQL Server Control node session ID, login name, and user name for the current user process



SELECT SESSION_ID() AS ID, @@SPID AS 'Control ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

SQL SERVER – Index Seek Vs. Index Scan (Table Scan)



Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.

What is the Maximum Size per Database for SQL Server Express?

Maximum database size of 10 GB per database in SQL Server 2014, SQL Server 2012, and 2008 R2 Express (4 GB for SQL Server 2008 Express and earlier; compared to 2 GB in the former MSDE).

What is Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY for filtering query using aggregate values.

The following functions are aggregate functions.

AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP (Read more here )

What is Use of @@ SPID in SQL Server?

A SPID is the returns sessions ID of the current user process. And using that session ID, we can find out that the last query was executed. (Read more here)

What is the Difference between Index Seek vs. Index Scan?

An index scan means that SQL Server reads all the 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 the rows of the index are examined instead of the table directly. This is sometimes compared 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.

An index seek, on the other hand, means that the Query Optimizer relies entirely on the index leaf data to locate rows satisfying the query condition. An index seek will be most beneficial in cases where a small percentage of rows will be returned. An index seek will only affect the rows that satisfy a query condition and the pages that contain these qualifying rows; in terms of performance, this is highly beneficial when a table has a very large number of rows. (Read more here)

What is the Maximum Size per Database for SQL Server Express?

SQL Server Express supports a maximum size of 4 GB per database, which excludes all the log files. 4 GB is not a very large size; however, if the database is properly designed and the tables are properly arranged in a separate database, this limitation can be resolved to a certain extent.

(Read more here)

How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?

In one way, it is quite easy to figure this out by just looking at the result set; however, this method cannot be relied upon every time as it is difficult to reach a conclusion when there are many columns and many rows.

It is easy to measure how much data is retrieved from server to client side. The SQL Server Management Studio has feature that can measure client statistics. (Read more here)

What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?

In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users. (Read more here)

How to Create Primary Key with Specific Name while Creating a Table?

CREATE TABLE [dbo].[TestTable]([ID] [int] IDENTITY(1,1)NOTNULL,[FirstName] [varchar](100)NULL,CONSTRAINT [PK_TestTable] PRIMARYKEYCLUSTERED([ID] ASC))GO

(Read more here)

What is T-SQL Script to Take Database Offline – Take Database Online?

-- Take the Database OfflineALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATEGO-- Take the Database OnlineALTER DATABASE [myDB] SET ONLINE
GO

(Read more here)

How to Enable/Disable Indexes?

--Disable IndexALTER INDEX [IndexName] ON TableName DISABLE
GO--Enable IndexALTER INDEX [IndexName] ON TableName REBUILD
GO

(Read more here)

Can we Insert Data if Clustered Index is Disabled?

No, we cannot insert data if Clustered Index is disabled because Clustered Indexes are in fact original tables which are physically ordered according to one or more keys (Columns).
(Read more here)

How to Recompile Stored Procedure at Run Time?

We can Recompile Stored Procedure in two ways.

Option 1:

CREATE PROCEDURE dbo.PersonAge(@MinAge INT, @MaxAge INT)WITH RECOMPILEAS
SELECT*FROM dbo.tblPersonWHERE Age <= @MinAge AND Age >= @MaxAgeGO

Option 2:

EXEC dbo.PersonAge65, 70 WITHRECOMPILE

We can use RECOMPILE hint with a query and recompile only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.

This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan. (Read more here)

Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?

There is no performance difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table).(Read more here)

What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?

INSERT TOP (N) INTO Table is faster than Using Top with INSERT but when we use INSERT TOP (N) INTO Table, the ORDER BY clause is totally ignored. (Read more here)

Does the Order of Columns in UPDATE statements Matter?

No, the order of columns in UPDATE statement does not matter for results updated.

Both the below options produce the same results.

Option 1:

UPDATE TableNameSET Col1 ='Value', Col2 ='Value2'

Option 2:

UPDATE TableNameSET Col2 ='Value2', Col1 ='Value'

(Read more here)

How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?





Client Statistics is very important. Many a time, people relate queries execution plan with query cost. This is not a good comparison. Both are different parameters, and they are not always related. It is possible that the query cost of any statement is less, but the amount of the data returned is considerably large, which is causing any query to run slow. How do we know if any query is retrieving a large amount data or very little data? In one way, it is quite easy to figure this out by just looking at the result set; however, this method cannot be relied upon all the time as it is difficult to reach a conclusion when there are many columns and many rows.

To measure how much data is retrieved from server to client side is very simple. SQL Server Management Studio has feature that can measure client statistics. There are three different ways to enable client statistics.

Method 1

Press SHIFT+ALT+S together.

Method 2



Go to Menu >> Query >> Include Client Statistics



Method 3



Once you enable the client statistics, it will display another tab in the result span; when clicked, we obtain the following:








Method 1:



INSERT INTO TABLE …
SELECT TOP (N) Cols…
FROM Table1

Method 2:

INSERT TOP(N) INTO TABLE …
SELECT Cols…
FROM Table1

Today we will go over the second method which in fact is the enhancement in TOP along with INSERT. It is very interesting to also observe difference between both the methods. Let us play with one real example and we understand what exactly is happening in either of the case.

USE tempdb
GO-- Create TableIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TestValue') AND type IN (N'U'))DROP TABLE TestValue
GOCREATE TABLE TestValue(ID INT)INSERT INTO TestValue (ID)SELECT 1UNION ALLSELECT 2UNION ALLSELECT 3UNION ALLSELECT 4UNION ALLSELECT 5
GO-- Select Data from TableSELECT *FROM TestValue
GO-- Create Two Table where Data will be InsertedIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'InsertTestValue') AND type IN (N'U'))DROP TABLE InsertTestValue
GOCREATE TABLE InsertTestValue (ID INT)IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'InsertTestValue1') AND type IN (N'U'))DROP TABLE InsertTestValue1
GOCREATE TABLE InsertTestValue1 (ID INT)GO-- Option 1: Top with SelectINSERT INTO InsertTestValue (ID)SELECT TOP (2) IDFROM TestValueORDER BY ID DESCGO-- Option 2: Top with InsertINSERT TOP (2) INTO InsertTestValue1 (ID)SELECT IDFROM TestValueORDER BY ID DESCGO-- Check the DataSELECT *FROM InsertTestValue
GOSELECT *FROM InsertTestValue1
GO-- Clean upDROP TABLE InsertTestValueDROP TABLE InsertTestValue1DROP TABLE TestValue
GO

Now let us check the result of above SELECT statements.



It is very interesting to see when Option 2 is used, ORDER BY is absolutely ignored and data is inserted in any order.

In future articles, we will talk about performance for these queries. What are your thoughts on this feature? Have you used INSERT TOP(N) in your application?

SQL SERVER – SELECT 1 vs SELECT * – An Interesting Observation


Many times I have seen the issue of SELECT 1 vs SELECT * discussed in terms of performance or readability while checking for the existence of rows in the table. I ran quick 4 tests about this observed that I am getting same result when used SELECT 1 and SELECT *. I think smart readers of this blog will come up the situation when SELECT 1 and SELECT * have a different execution plan when used to find the existence of rows.

Let us see 4 tests I ran and note their result is same and their Execution Plan take the same amount of resources (50% and 50%)

You can click on the images to see larger images.

Test 1: Whole Table SELECT




1

2

3

4

5

6

7

8

9

10

11

12


USE AdventureWorks

GO

IF EXISTS(

SELECT 1

FROM Production.Product)

SELECT 'SELECT 1'

GO

IF EXISTS(

SELECT *

FROM Production.Product)

SELECT 'SELECT *'

GO








Test 2: Condition WHERE on Indexed Column




1

2

3

4

5

6

7

8

9

10

11

12

13

14







USE AdventureWorks

GO

IF EXISTS(

SELECT 1

FROM Production.Product

WHERE Name = 'Adjustable Race')

SELECT 'SELECT 1'

GO

IF EXISTS(

SELECT *

FROM Production.Product

WHERE Name = 'Adjustable Race')

SELECT 'SELECT *'

GO








Test 3: Using Aggregate function COUNT




1

2

3

4

5

6

7

8

9

10

11

12

13

14


USE AdventureWorks

GO

IF (

SELECT 1

FROM Production.Product

WHERE Name = 'Adjustable Race') = 1

SELECT 'SELECT 1'

GO

IF (

SELECT COUNT(*)

FROM Production.Product

WHERE Name = 'Adjustable Race') = 1

SELECT 'SELECT *'

GO











Test 4: Using COUNT with search on non-indexed Column




















USE AdventureWorks

GO

IF (

SELECT COUNT(1)

FROM Production.Product

WHERE SafetyStockLevel = '800') &amp;gt; 1

SELECT 'SELECT 1'

GO

IF (

SELECT COUNT(*)

FROM Production.Product

WHERE SafetyStockLevel = '800') &amp;gt; 1

SELECT 'SELECT *'

GO








Note: I have used single quotes around 800, which is numeric and I am aware of that fact. Above queries are for testing purpose only.

I have been using SELECT 1 instead of SELECT * when checking existence of rows. I would like to see what my readers have an opinion about this. Please have your opinion and make your comment here.