Explore topic-wise InterviewSolutions in .

The dtutil is the command prompt utility which is USED to MANAGE SSIS packages.
This utility can copy, move, DELETE or VERIFY the existence of a package.

The dtutil is the command prompt utility which is used to manage SSIS packages.
This utility can copy, move, delete or verify the existence of a package.

• DATAREADER Source in 2005 & ADO NET Source in 2008 & above.
• Excel Source
• Flat File Source
• OLE DB Source
• Raw File Source
• Script COMPONENT
• XML Source.

A page in SQL Server is an 8 KB data storage AREA.
There are 8 types of pages:

1. Data
2. Index
3. Text/Image
4. Global Allocation Map, SHARED Global Allocation Map
5. Page Free Space
6. Index Allocation Map
7. Bulk Changed Map
8. DIFFERENTIAL Changed Map.

A page in SQL Server is an 8 KB data storage area.
There are 8 types of pages:

• NESTED LOOP JOIN
• MERGE join
• HASH join.

NEW WAY to insert MULTIPLE records in a table with INSERT statement. ALSO known as Table Value Constructor.

New way to insert multiple records in a table with INSERT statement. Also known as Table Value Constructor.

• MESSAGE _id or message_str
• SEVERITY, and
• STATE.

• LOCAL TEMP TABLES (#)
• GLOBAL temp tables (##)
• Table VARIABLES (@).

1. SIMPLE
2. BULK LOGGED
3. FULL.

This involves 4 steps:

1. CREATE Database with different file GROUPS
2. Create Partition FUNCTION
3. Create Partition Scheme
4. Create Partitioned Table or Index.

This involves 4 steps:

It will GIVE ZERO.

It will give zero.

• TEXT FILE
• SQL Server Profiler
• SQL Server
• Windows Event LOG
• XML File.

• EXECUTION PLAN
• SET STATISTICS PROFILE ON.

Highest: SERIALIZABLE
LOWEST: READ UNCOMMITTED
DEFAULT: READ COMMITTED

Highest: SERIALIZABLE
Lowest: READ UNCOMMITTED
Default: READ COMMITTED

A – Atomicity (Transaction is atomic, if ONE part fails, then the entire transaction fails).
C – Consistency (Any transaction the database performs will take it from one consistent STATE to another, only VALID data will be written to the database).
I – Isolation (Other operations cannot access data that has been modified during a transaction that has not yet completed).
D – Durability (On a transaction’s success the transaction will not be lost, the transaction’s data CHANGES will survive system failure, and that all integrity constraints have been satisfied).

A – Atomicity (Transaction is atomic, if one part fails, then the entire transaction fails).
C – Consistency (Any transaction the database performs will take it from one consistent state to another, only valid data will be written to the database).
I – Isolation (Other operations cannot access data that has been modified during a transaction that has not yet completed).
D – Durability (On a transaction’s success the transaction will not be lost, the transaction’s data changes will survive system failure, and that all integrity constraints have been satisfied).

By USING sp_rename STORED PROCEDURE.

By using sp_rename stored procedure.

FROM, [JOIN CONDITION, JOIN TABLE ...], ON, OUTER, WHERE, GROUP BY, CUBE/ROLLUP/GROUPING SETS, HAVING, SELECT, DISTINCT, ORDER BY, TOP.

FROM, [JOIN CONDITION, JOIN TABLE ...], ON, OUTER, WHERE, GROUP BY, CUBE/ROLLUP/GROUPING SETS, HAVING, SELECT, DISTINCT, ORDER BY, TOP.

Integrity constraints are used to ensure ACCURACY and consistency of data in a relational database. Data integrity allows to define certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn’t meet these requirements, the RDBMS will not allow so.
A constraint is a property assigned to a column or the set of columns in a table that prevents certain TYPES of INCONSISTENT data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and RELIABILITY of the data in the database.

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity allows to define certain data quality requirements that the data in the database needs to meet. If a user tries to insert data that doesn’t meet these requirements, the RDBMS will not allow so.
A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.

Returns the FIRST nonnull expression AMONG its arguments.
Syntax
COALESCE ( expression [ ,...N ] )
expression
Is an expression of any type.

Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...n ] )
expression
Is an expression of any type.

MICROSOFT SQL Server 2005 provides the following types of REPLICATION for USE in distributed applications:

• Transactional Replication
• Merge Replication
• Snapshot Replication.

Microsoft SQL Server 2005 provides the following types of replication for use in distributed applications:

VARCHAR2 is specific to Oracle. MS SQL SERVER has VARCHAR & VARCHAR(MAX) data TYPES.

VARCHAR2 is specific to Oracle. MS SQL Server has VARCHAR & VARCHAR(MAX) data types.

ONE CLUSTERED INDEX, one or more than one non-clustered index, unique index, FILTERED, SPATIAL, xml, etc.

One Clustered Index, one or more than one non-clustered index, unique index, filtered, spatial, xml, etc.

UPTO 249 non-clustered INDEXES can be CREATED in a table.

Upto 249 non-clustered indexes can be created in a table.

Answer : declare @mycursor cursor declare @FirstName VARCHAR(12) SELECT CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable) SET @mycursor = cursor for select FirstName from Person.Contact select CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed) open @mycursor select CURSOR_STATUS('variable','@mycursor') --// 1 (The result set of the cursor has at least one row) fetch next from @mycursor into @FirstName select CURSOR_STATUS('variable','@mycursor') --// 1 (The result set of the cursor has at least one row) close @mycursor select CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed) deallocate @mycursor select CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable) select CURSOR_STATUS('variable','@nocursor') --// -3 (A cursor with the specified NAME does not exist)

• READ Uncommitted Isolation LEVEL
• Read Committed Isolation Level
• Repeatable Read Isolation Level
• SERIALIZABLE Isolation Level
• Snapshot Isolation Level
• Read Committed Snapshot Isolation Level.

SCOPE is LIMITED to its SESSION only.

Scope is limited to its session only.

Table with ## (DOUBLE pound signs) is CALLED Global TEMP table. Scope is outside the session but only TILL the original session lasts.

Table with ## (double pound signs) is called Global Temp table. Scope is outside the session but only till the original session lasts.

• DTS, import/export WIZARD.
• SCRIPTING out DATABASE OBJECTS.

BulkCopy is a tool used to copy huge AMOUNT of data from tables. BULK INSERT command HELPS to IMPORTS a data file into a database table or VIEW in a user-specified format.

BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.

YES, 32 LEVELS.

Yes, 32 levels.

The VIRTUAL TABLES in TRIGGERS are:

• INSERTED
• DELETED.

The virtual tables in Triggers are:

By using TRY-CATCH constructs, putting our SQL statements/scripts inside the TRY BLOCK and error HANDLING in the CATCH block.

By using TRY-CATCH constructs, putting our SQL statements/scripts inside the TRY block and error handling in the CATCH block.

SQL SERVER supports MAINLY 3 types of UDFs:

• SCALAR FUNCTIONS
• Inline Table-valued functions
• Multi-statement Table-valued functions.

SQL Server supports mainly 3 types of UDFs:

• SPATIAL
• XML.

CUBE & ROLLUP are the grouping sets USED with GROUP BY CLAUSE and are very helpful in creating REPORTS.

CUBE & ROLLUP are the grouping sets used with GROUP BY clause and are very helpful in creating reports.

ANSWER : SELECT *FROM WHERE IN (SELECT… UNION SELECT…) OR SELECT * FROM WHERE IN (SELECT… UNION ALL SELECT…).

• ISNULL accepts only 2 parameters. The first parameter is checked for NULL value, if it is NULL then the second parameter is returned, otherwise it returns first parameter.
• COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter.
• ISNULL does not implicitly converts the datatype if both parameters datatype are different. On the other side COALESCE implicitly converts the parameters datatype in order of higher precedence.
• SIMILAR to above point ISNULL always returns the value with datatype of first parameter. Contrary to this, COALESCE returns the datatype value according to the precedence and datatype compatibility.

EXEC sp_executesql, EXECUTE()
EXECUTE (or EXEC) vs sp_executesql
EXECUTE:
EXECUTE executes a command string or character string WITHIN a TSQL batch, or one of the following MODULES: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure. The TSQL query can be a direct string or a variable of char, varchar, nchar, or nvarchar DATA type.
sp_executesql:
sp_executesql executes a TSQL statement or batch that can be reused many times, or one that has been built dynamically. The TSQL statement or batch can contain EMBEDDED parameters. The SQL query is a Unicode string or a Unicode variable that contains a Transact-SQL statement or batch. Here the variable datatype is restricted to Unicode nchar or nvarchar only. If a Unicode constant (SQL string) is used then the it must be PREFIXED with N.

EXEC sp_executesql, EXECUTE()
EXECUTE (or EXEC) vs sp_executesql
EXECUTE:
EXECUTE executes a command string or character string within a TSQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure. The TSQL query can be a direct string or a variable of char, varchar, nchar, or nvarchar data type.
sp_executesql:
sp_executesql executes a TSQL statement or batch that can be reused many times, or one that has been built dynamically. The TSQL statement or batch can contain embedded parameters. The SQL query is a Unicode string or a Unicode variable that contains a Transact-SQL statement or batch. Here the variable datatype is restricted to Unicode nchar or nvarchar only. If a Unicode constant (SQL string) is used then the it must be prefixed with N.

If possible, avoid using SQL Server CURSORS. They generally use a LOT of SQL Server resources and REDUCE the performance and scalability of your applications. If you NEED to perform row-by-row operations, try to find another METHOD to perform the task. Here are some alternatives to using a cursor:

• Use WHILE LOOPS
• Use temp tables
• Use derived tables
• Use correlated sub-queries
• Use the CASE statement
• Perform multiple queries.

If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task. Here are some alternatives to using a cursor:

exec xp_cmdshell 'dir c:*.exe'
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell. command_string is varchar(8000) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are PRESENT in the file PATHS or PROGRAM names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a WORKAROUND.
no_output
Is an optional parameter executing the given command_string, and does not return any output to the client.

exec xp_cmdshell 'dir c:*.exe'
xp_cmdshell {'command_string'} [, no_output]
Arguments
'command_string'
Is the command string to execute at the operating-system command shell. command_string is varchar(8000) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.
no_output
Is an optional parameter executing the given command_string, and does not return any output to the client.

• With DELETE we can provide CONDITIONAL WHERE clause to remove/delete specific rows, which is not possible with TRUNCATE.
• TRUNCATE is faster than DELETE as Delete keeps LOG of each row it deletes in transaction logs, but truncate keeps log of only de-allocated PAGES in transaction logs.
• Both statements can be rolled backed if provided in a transaction (BEGIN TRANS). If not then none of them can be rollbacked.
• DELETE is DML just like INSERT, UPDATE, but TRANCATE is DDL, just like CREATE, ALTER, DROP.

By using OSQL & SQLCMD we can execute an SQL QUERY from command prompt.

By using OSQL & SQLCMD we can execute an sql query from command prompt.

SQL SERVER 2000 includes several TOOLS you may find useful when PERFORMANCE tuning your SQL Server applications. The include:

• Query ANALYZER
• PROFILER
• Index Wizard
• Performance Monitor.

SQL Server 2000 includes several tools you may find useful when performance tuning your SQL Server applications. The include:

• Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
• Exception handling (TRY-CATCH block)
• CTE (Common Table EXPRESSIONS)
• PIVOT, UNPOVIT
• CUBE, ROLLUP & GROUPING SET
• SYNONYMS.

Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply "nolock". nolock is NORMALLY applied while querying on production servers. This would MAKE the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The RISK will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.

Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply "nolock". nolock is normally applied while querying on production servers. This would make the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The risk will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.

UPDATE TABLENAME set ColumnName1 = (case ColumnName1 when 'Yes' then 'No' ELSE 'Yes' end).

Update tablename set ColumnName1 = (case ColumnName1 when 'Yes' then 'No' else 'Yes' end).

Returns the STARTING position of the first OCCURRENCE of a PATTERN in a SPECIFIED expression, or zeros if the pattern is not found.
Syntax - PATINDEX ( '%pattern%' , expression )
Eg: USE AdventureWorks;

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found.
Syntax - PATINDEX ( '%pattern%' , expression )
Eg: USE AdventureWorks;

SELECT NAME FROM tblPlayer WHERE Name LIKE '%[''%'']'.

SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']'.

SELECT * into TABLE2 from TABLE1 where 1 = 2

select * into table2 from table1 where 1 = 2

TSQL (Transact-SQL) is a set of programming extensions from SYBASE and Microsoft that ADD several features to the Structured Query Language (SQL) including transaction control, exception and error handling, row processing, and declared variables. Microsoft's SQL Server and Sybase's SQL server SUPPORT T-SQL STATEMENTS.

TSQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL) including transaction control, exception and error handling, row processing, and declared variables. Microsoft's SQL Server and Sybase's SQL server support T-SQL statements.