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:
- GRANT
- 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.
Comments
Post a Comment