1)Difference between Primary Key and unique key ?
Ans)
Primary Key Restrict duplicate values and null values each table can have only one primary key,default clustered index is the primary key.
unique key restrict duplicate values and allow only one null value. default non clustered index is an unique key
------------------------------------
2)What is the clause that specifies a condition for a group or an aggregate?
Ans)
Select from following answers:
A. Distinct
B. Where clause
C. Exists
D. Having Clause
Having Clause
This is how we can create a condition laid on a group or aggregate
Example :
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
------------------------------------------------------------------------
3) Which command removes all the rows from the table without logging individual row deletions ?
Ans)
Select from following answers:
A. Truncate
B. delete
C. drop
D. Alter
Truncate will not log individual row deletion.
Here is an example of how we can count the rows before and after truncate
SELECT COUNT(*) AS BeforeTruncateCount FROM MyTable.MyJob;
TRUNCATE TABLE MyTable.MyJob;
SELECT COUNT(*) AS AfterTruncateCount FROM MyTable.MyJob;
---------------------------------------------------------------------
4) What is the command that is used to set a set of privileges that can be granted to users or different roles?
Ans)
Select from following answers:
A. Create Authority
B. Create Grant
C. Create Role
D. Create Authentication
Role Command is used to set the set of privileges which can be granted to users.
An example:
CREATE ROLE editors AUTHORIZATION db_SecurityServices;
-------------------------------------------------------------------------------------
5)Write a script to identify, Each character's count in a given string ? (Without using Loop)
i.e: Pandian
Ans)
Declare @String Varchar(100)
Select @String = 'Pandian'
;With CTEs
As
(
Select LEFT(@String,0) Chars,0 [String]
Union All
Select Substring(@String,[String]+1,1) Chars,[String]+1 From CTEs Where [String] <=LEN(@String)
)
Select Chars [Letter], COUNT(1) [Repeats] from CTEs Where Chars <>'' Group by Chars
Go
Result:
Letter Repeats
------ ------
a 2
d 1
i 1
n 2
P 1
------------------------------------------------------------------------------
1) What is magic table in Sql server ?
Ans)
Sql Server automatically creates and manages two temporary, memory-resident tables (deleted and inserted tables) which are popularly known as magic tables.
Usually used with the DML triggers. Can not directly modify the data in the tables or perform ddl operation.
Primarily used to perform certain action like
1) Extend referential integrity between tables
2) Test for errors and take action based on the error.
3) Find the difference between the state of a table before and after data modification and take actions based on that difference.
-----------------------------------
2)What is CTE in Sql server 2005 ?
Ans)
A common table expression (CTE) can be thought of as a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is similar to a derived table. Unlike a derived table, a CTE can be self-referencing, not stored as object and can be referenced multiple times in the same query.
It can be recursive and non-recursive.
It provides the significant advantage of being able to reference itself because earlier version sql server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive step.
CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.
------------------------------------------
3)What is the difference between DELETE and TRUNCATE in SQL ?
Ans)
Using TRUNCATE, we cannot restore the deleted data.
Syntax:
TRUNCATE TABLE table_name;
Example:
To delete all the rows from employee table, the query would be like,
TRUNCATE TABLE employee;
Unlike using DELETE, we can restore the data, as the physical data will not get deleted.
Syntax:
DELETE FROM table_name [WHERE condition];
Example:
To delete an employee with id 100 from the employee table, the sql delete query would be like,
DELETE FROM employee WHERE id = 100;
Finally, To delete rows using truncate, the identity column will reset , but not reset when using delete
Ans)
Primary Key Restrict duplicate values and null values each table can have only one primary key,default clustered index is the primary key.
unique key restrict duplicate values and allow only one null value. default non clustered index is an unique key
------------------------------------
2)What is the clause that specifies a condition for a group or an aggregate?
Ans)
Select from following answers:
A. Distinct
B. Where clause
C. Exists
D. Having Clause
Having Clause
This is how we can create a condition laid on a group or aggregate
Example :
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
------------------------------------------------------------------------
3) Which command removes all the rows from the table without logging individual row deletions ?
Ans)
Select from following answers:
A. Truncate
B. delete
C. drop
D. Alter
Truncate will not log individual row deletion.
Here is an example of how we can count the rows before and after truncate
SELECT COUNT(*) AS BeforeTruncateCount FROM MyTable.MyJob;
TRUNCATE TABLE MyTable.MyJob;
SELECT COUNT(*) AS AfterTruncateCount FROM MyTable.MyJob;
---------------------------------------------------------------------
4) What is the command that is used to set a set of privileges that can be granted to users or different roles?
Ans)
Select from following answers:
A. Create Authority
B. Create Grant
C. Create Role
D. Create Authentication
Role Command is used to set the set of privileges which can be granted to users.
An example:
CREATE ROLE editors AUTHORIZATION db_SecurityServices;
-------------------------------------------------------------------------------------
5)Write a script to identify, Each character's count in a given string ? (Without using Loop)
i.e: Pandian
Ans)
Declare @String Varchar(100)
Select @String = 'Pandian'
;With CTEs
As
(
Select LEFT(@String,0) Chars,0 [String]
Union All
Select Substring(@String,[String]+1,1) Chars,[String]+1 From CTEs Where [String] <=LEN(@String)
)
Select Chars [Letter], COUNT(1) [Repeats] from CTEs Where Chars <>'' Group by Chars
Go
Result:
Letter Repeats
------ ------
a 2
d 1
i 1
n 2
P 1
------------------------------------------------------------------------------
1) What is magic table in Sql server ?
Ans)
Sql Server automatically creates and manages two temporary, memory-resident tables (deleted and inserted tables) which are popularly known as magic tables.
Usually used with the DML triggers. Can not directly modify the data in the tables or perform ddl operation.
Primarily used to perform certain action like
1) Extend referential integrity between tables
2) Test for errors and take action based on the error.
3) Find the difference between the state of a table before and after data modification and take actions based on that difference.
-----------------------------------
2)What is CTE in Sql server 2005 ?
Ans)
A common table expression (CTE) can be thought of as a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is similar to a derived table. Unlike a derived table, a CTE can be self-referencing, not stored as object and can be referenced multiple times in the same query.
It can be recursive and non-recursive.
It provides the significant advantage of being able to reference itself because earlier version sql server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive step.
CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.
------------------------------------------
3)What is the difference between DELETE and TRUNCATE in SQL ?
Ans)
Using TRUNCATE, we cannot restore the deleted data.
Syntax:
TRUNCATE TABLE table_name;
Example:
To delete all the rows from employee table, the query would be like,
TRUNCATE TABLE employee;
Unlike using DELETE, we can restore the data, as the physical data will not get deleted.
Syntax:
DELETE FROM table_name [WHERE condition];
Example:
To delete an employee with id 100 from the employee table, the sql delete query would be like,
DELETE FROM employee WHERE id = 100;
Finally, To delete rows using truncate, the identity column will reset , but not reset when using delete
