Interview Questions post

1. what are the SQL Server 2012 Editions available?

SQL Server 2012 Standard Edition
SQL Server 2012 Business Edition
SQL Server 2012 Enterprise Edition
SQL Server 2012 Express Edition and Local DB
SQL Server 2012 web and developer Edition

2. What is SQL Server Instance ?

SQL Server provide two type of instance

1. Named Instance -- > MachineName\sqlserverinstanceName
2. Default Instance   --> MSSQLSERVE

3. What is the difference between default and Named Instance?

While setting up  new environment you will be asked to select named instance or Default Instance

1. Named Instance: DBA  need give a name for instance there is no limit for named instance
2. Default Instance: if you select this while installation instance name will be MSSQLSERVE and you can select only 1 time this default instance in single machine.

4. How many SQL Server Instances can be installed on one Server?

50 instances on a stand-alone server.

25 instances on a failover cluster when using a shared cluster disk as the stored option for you cluster installation SQL Server supports 50 instances on a failover cluster if you choose SMB file shares as the storage option for your cluster installation

5. What is Collation? What Collation will you pick while installing SQL Server?

 Collation in SQL Server is set of rules that governs proper use of character and alphabets in any language That you can select installation time or setting up new database on existing server.
By default English-language (US) collation is SQL_Latin1_General*.

6. Can instance level collation be different from database collation?

yes instance level collation is default for all database but if you want you can change database collation different from instance level collation.

7. Can you have different Collation on different Databases in SQL Server Instance?

Yes

8. What is Best practices for SQL Server Database Files and SQL Server Tempdb Considerations

1. Data files and Log files should not be on same drive
2. Data files and/or Log files should not be on C:\ drive
3. Tempdb should have it's own dedicated drive

How to Delete all rows from table

1. Disable all check constrains from table
2. Disable all triggers on all tables
3. Delete all data from tables
4. Enable all check constrains from table
5. Enable all triggers on all table

--Declare a global variable used throught the query

DECLARE @SQLStmt NVARCHAR(MAX) = N''




--Disable all check constraints on all tables

SELECT @SQLStmt = @SQLStmt + COALESCE('ALTER TABLE ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) + ' NOCHECK CONSTRAINT ALL; ', '')

FROM sys.tables

SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt

EXEC (@SQLStmt)

SET @SQLStmt = N''

--Disable all triggers on all tables




SELECT @SQLStmt = @SQLStmt + COALESCE(N'DISABLE TRIGGER ALL ON ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) + '; ', '')

FROM sys.tables

SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt

EXEC (@SQLStmt)

SET @SQLStmt = N''

--Delete all data from all tables




SELECT @SQLStmt = @SQLStmt + COALESCE('DELETE FROM ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) + '; ', '')

FROM sys.tables

SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt

EXEC (@SQLStmt)

SET @SQLStmt = N''

--Enable all constraints on all tables




SELECT @SQLStmt = @SQLStmt + COALESCE('ALTER TABLE ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) + ' CHECK CONSTRAINT ALL; ', '')

FROM sys.tables

SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt

EXEC (@SQLStmt)

SET @SQLStmt = N''

--Enable all triggers on all tables




SELECT @SQLStmt = @SQLStmt + COALESCE(N'ENABLE TRIGGER ALL ON ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_id) + '; ', '')

FROM sys.tables

SET @SQLStmt = 'SET NOCOUNT ON; ' + @SQLStmt

EXEC (@SQLStmt)