SQL Server Interview Questions: Explain The Use of Keyword WITH ENCRYPTION. Create A Store Procedure With Encryption
SQL Server Interview Questions: Explain The Use of Keyword WITH ENCRYPTION. Create A Store Procedure With Encryption
SQL Server Interview Questions: Explain The Use of Keyword WITH ENCRYPTION. Create A Store Procedure With Encryption
Explain the use of keyword WITH ENCRYPTION. Create a Store Procedure with Encryption.
It is a way to convert the original text of the stored procedure into encrypted form. The stored
procedure gets obfuscated and the output of this is not visible to
It enables SQL server to address diverse data sources like OLE DB similarly. It allows Remote server
access and has the ability to issue distributed queries, updates, commands and transactions.
Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages
multidimensional cubes of data and provides access to heaps of information including aggregation of
data One can create data mining models from data sources and use it for Business Intelligence also
including reporting features.
Some of the key features are:
Ease of use with a lot of wizards and designers.
Flexible data model creation and management
Scalable architecture to handle OLAP
Provides integration of administration tools, data sources, security, caching, and reporting etc.
Provides extensive support for custom applications
Every Analysis server has a repository to store metadata for the objects like cubes, data sources etc. It’s
by default stored in a MS Access database which can be also migrated to a SQL Server database.
What is user defined datatypes and when you should go for them?
User defined data types are based on system data types. They should be used when multiple tables
need to store the same type of data in a column and you need to ensure that all these columns are
exactly the same including length, and nullability.
Parameters for user defined datatype:
Name
System data type on which user defined data type is based upon.
Nullability.
For example, a user-defined data type called post_code could be created based on char system data
type.
A bit datatype is an integer data type which can store either a 0 or 1 or null value.
a. Creation of XML fragments: This is done from the relational data using FOR XML to the select
query.
b. Ability to shred xml data to be stored in the database.
Client-side XML support in SQL Server is in the form of SQLXML. It can be described in terms of
XML Views: providing bidirectional mapping between XML schemas and relational tables.
Creation of XML Templates: allows creation of dynamic sections in XML.
SQL profiler is a tool to monitor performance of various stored procedures. It is used to debug the
queries and procedures. Based on performance, it identifies the slow executing queries. Capture any
problems by capturing the events on production environment so that they can be solved.
What is XPath?
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
e. MERGE
f. CALL
g. EXPLAIN PLAN
h. LOCK TABLE
Examples:
a. GRANT
b. REVOKE
Steps
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.
UNION selects only distinct values whereas UNION ALL selects all values and not just distinct ones.
UNION: SELECT column_names FROM table_name1
UNION
SELECT column_names FROM table_name2
UNION All: SELECT column_names FROM table_name1
UNION ALL
SELECT column_names FROM table_name2
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.
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.
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 Available to any connection once created. They are
current user and are cleared when connection is cleared when the last connection is closed.
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?
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.
What are the commands available for Summarizing Data in SQL Server?
List out the difference between CUBE operator and ROLLUP operator
What are the guidelines to use bulk copy utility of SQL Server?
Bulk copy is an API that allows interacting with SQL Server to export/import data in one of the two data
formats. Bulk copy needs sufficient system credentials.
Need INSERT permissions on destination table while importing.
Need SELECT permissions on source table while exporting.
Need SELECT permissions on sysindexes, sysobjects and syscolumns tables.
bcp.exe northwind..cust out "c:\cust.txt" –c -T
Export all rows in Northwind.Cust table to an ASCII-character formatted text file.
Capabilities of cursors:
Cursor reads every row one by one.
Cursors can be used to update a set of rows or a single specific row in a resultset
Cursors can be positioned to specific rows.
Cursors can be parameterized and hence are flexible.
Cursors lock row(s) while updating them.
What are the differences among batches, stored procedures, and triggers?
TRUNCATE DELETE
This is also a logged operation but in terms of This is a logged operation for every row.
deallocation of data pages.
Cannot TRUNCATE a table that has foreign key Any row not violating a constraint can be Deleted.
constraints.
Resets identity column to the default starting Does not reset the identity column. Starts where it
value. left from last.
Removes all rows from a table. Used delete all or selected rows from a table based
on WHERE clause.
Cannot be Rolled back. Need to Commit or Rollback
DDL command DML command
http://www.careerride.com/SQLServer-Interview-Questions.aspx
We do not warrant the correctness of content. The risk from using it lies entirely with the user. While using this
document, you agree to have read and accepted the terms of use and privacy policy.