SQL Server questions for interview Part2

What are the Authentication Modes in SQL Server? 

         Windows Authentication Mode  (Windows Authentication): uses user’s Windows account. And Mixed Mode (Windows Authentication and SQL Server Authentication): uses either windows or SQL server 

Explain Data Definition Language, Data Control Language and Data Manipulation Language. 

Data Definition Language (DDL):- are the SQL statements that define the database structure.

Example:

a.       CREATE

b.      ALTER

c.       DROP

d.      TRUNCATE

e.      COMMENT

f.        RENAME

Data Manipulation Language (DML):- statements are used for manipulate or edit data.

Example:

a.       SELECT - retrieve data from the a database

b.      INSERT - insert data into a table

c.       UPDATE - updates existing data within a table

d.      DELETE

e.      MERGE

f.        CALL

g.       EXPLAIN PLAN

h.      LOCK TABLE

Data Control Language (DCL):-statements to take care of the security and authorization.

Examples:

1. GRANT
2. REVOKE

What are the steps to process a single SELECT statement? 

a.       The select statement is broken into logical units

b.            A sequence tree is built based on the keywords and expressions in the form of the logical units.

c.             Query optimizer checks for various permutations and combinations to figure out the fastest way using minimum resources to access the source tables. The best found way is called as an execution plan.

d.      Relational engine executes the plan and processes the data 

Explain GO Command. 

Go command is a signal to execute the entire batch of SQL statements after previous Go.

What is the significance of NULL value and why should we avoid permitting null values? 

NULL value means that no entry has been made into the column. It states that the corresponding value is either unknown or undefined. It is different from zero or "". They should be avoided to avoid the complexity in select & update queries and also because columns which have constraints like primary or foreign key constraints cannot contain a NULL value. 

What is use of DBCC Commands? 

DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency. They are grouped as:

Maintenance: Maintenance tasks on Db, filegroup, index etc. Commands include DBCC CLEANTABLE, DBCC INDEXDEFRAG, DBCC DBREINDEX, DBCC SHRINKDATABASE, DBCC DROPCLEANBUFFERS, DBCC SHRINKFILE, DBCC FREEPROCCACHE, and DBCC UPDATEUSAGE.

Miscellaneous: Tasks such as enabling tracing, removing dll from memory. Commands include DBCC dllname, DBCC HELP, DBCC FREESESSIONCACHE, DBCC TRACEOFF, DBCC FREESYSTEMCACHE, and DBCC TRACEON.

Informational: Tasks which gather and display various types of information. Commands include DBCC INPUTBUFFER, DBCC SHOWCONTIG, DBCC OPENTRAN, DBCC SQLPERF, DBCC OUTPUTBUFFER, DBCC TRACESTATUS, DBCC PROCCACHE, DBCC USEROPTIONS, and DBCC SHOW_STATISTICS.

Validation: Operations for validating on Db, index, table etc. Commands include DBCC CHECKALLOC, DBCC CHECKFILEGROUP, DBCC CHECKCATALOG, DBCC CHECKIDENT, DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKDB.  

  

What is Log Shipping? 

Log shipping defines the process for automatically taking backup of the database and transaction files on a SQL Server and then restoring them on a standby/backup server. This keeps the two SQL Server instances in sync with each other. In case production server fails, users simply need to be pointed to the standby/backup server. 

Log shipping primarily consists of 3 operations:

• Backup transaction logs of the Production server.
• Copy these logs on the standby/backup server.
• Restore the log on standby/backup server.

What is the difference between a Local and a Global temporary table?

Temporary tables are used to allow short term use of data in SQL Server. They are of 2 types:

Local	Global
Only available to the current Db connection for current user and are cleared when connection is closed.	Available to any connection once created. They are cleared when the last connection is closed.
Multiple users can’t share a local temporary table.	Can be shared by multiple user sessions.

What is the STUFF and how does it differ from the REPLACE function? 

Both STUFF and REPLACE are used to replace characters in a string.

select replace('abcdef','ab','xx') results in xxcdef
 select replace('defdefdef','def','abc') results in abcabcabc
We cannot replace a specific occurrence of “def” using REPLACE.
select stuff('defdefdef',4, 3,'abc') results in defabcdef 

where 4 is the character to begin replace from and 3 is the number of characters to replace. 

What are the rules to use the ROWGUIDCOL property to define a globally unique identifier column? 

Only one column can exist per table that is attached with ROWGUIDCOL property. One can then use $ROWGUID instead of column name in select list.

Sql Server Interview Questions:

• SQL Server questions for interview
• Overview of Sql
• Sql server while loop
• Continue Statement in sql
• SQL Injection, prevent asp.net web application
• interview Part2
• interview Part3
• interview Part4