Difference between Delete and Truncate command?
TRUNCATE cannot be rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
DELETE can be rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.
What are the Four Main query statement?Explain them?
Four Query Statement:Select ,Insert, Update and Delete .
Select -- To retrieve a information form a table.
Insert -- To Insert a Value into a table.
Update -- To update a exists value into a table.
Delete -- To delete a row from a Table.
What is an Index?
physical structure containing pointers to the data.
What are the Different Types of Triggers?
1)DML Trigger
2)DDL Trigger
1 )
1.1)Instead of Trigger.
1.2)After Trigger.
What is NOT NULL Constraint
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
What is the maximum size of a row ?
8060 bytes.
Which datatypes can be compressed inside SQL Server?
FLOAT, MONEY, TIMESTAMP,INT, DATE, TIME.
Which datatype allows to store more than 2GB?
Filestream.
Will COUNT(column) include columns with null values in its count?
Yes.
What is the default order of an ORDER BY clause?
Ascending Order.
What is the purpose of the USE command?
used for to select the database. For i.e Use Database Name.
What is the purpose of the model database?
It works as Template Database for the Create Database Syntax.
What are Different Types of Locks?
Types of Locks:
Shared Locks,Update Locks,Exclusive Locks,Intent Locks,Schema Locks,Bulk Update Locks.
What is the difference between a "where" clause and a "having" clause?
WHERE clause:Restriction statement.
Having clause:Using after retrieving the Data
Difference between REVOKE and GRANT?
Grant:It will give permission to the user on database
Revoke: we can remove the permission
What operator performs pattern matching?
LIKE.
What are E-R Diagram?
Entity-Relationship diagram shows the relationship between various tables in the database.
What is mean by JOIN and Types of JOIN?
Used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Types of JOIN:
JOIN: Return rows when there is at least one match in both tables.
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table.
FULL JOIN: Return rows when there is a match in one of the tables.
Why can a "group by" or "order by" clause be expensive to process?
requires creation of Temporary tables to process the results of the query.
How many types of triggers are there?
1. Insert. 2. Delete. 3. Update. 4. Instead of.
Can you create foreign key constraints on temporary tables?
No.
What are the 2 types of Temporary Tables in SQL Server?
1. Local Temporary Tables.2. Global Temporary Tables.
Explain DML statements with examples?
DML: DML stands for Data Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update data in database.
Examples of DML statements: SELECT, UPDATE, INSERT, DELETE statements.
Can you create a view based on other views?
yes.
What are the two types of Triggers in SQL Server?
1. After Triggers : Fired after Insert, Update and Delete operations on a table.
2. Instead of Triggers: Fired instead of Insert, Update and Delete operations on a table.
What is faster, a correlated sub query or an exists?
Exists.
Different kinds of Joins.
OUTER-JOIN - LEFT, RIGHT, CROSS, FULL.
INNERJOIN.
Difference between Set and Select ?
Set is a ANSI standard for variable assignment.We can assign only one variable at a time.
Select is a Non-ANSI standard when assigning variables.We can assign multiple variable at a time.
How and when to use LIKE statement?
(1)% allows you to match any string of any length (including zero length).
(2)_ allows you to match on a single character.
What command do we use to rename a db?
sp_renamedb 'oldname' , 'newname' .
what is store Procedure(SP)?
Its nothing but a set of T-SQL statements combined to perform a single task of several tasks.
Objects of SQL server?
Assemblies,Tables,Reports,Filegroups,Full-text Catalogs,Diagrams,User-defined data types,Views,Roles,Stored procedures,Users,User Defined Functions and Transaction log.
What are the Lock Types in SQL server?
Shared lock,Update lock,Exclusive lock.
Explain Cursor types?
DYNAMIC: It reflects changes happened on the table while scrolling through the row.
STATIC: It works on snapshot of record set and disconnects from the server. This kind doesn't reflects changes happened on the table while scrolling through the row.
KEYSET: In this kind, new record is not reflected, but data modification can be seen.
What are the benefits of User-Defined Functions?
created and defined by the user
They can be used to create joins and simple to invoke as compared to stored procedures.
what is tuple?
instance of data within relational database.
Explain the "EXPLAIN" Statement?
provides information about the optimizer's choice of access path of the SQL.
What are some SQL aggregates and other built-in functions?
AVG, SUM, MIN, MAX, COUNT and DISTINCT.
What is mean by CRUD?
C- Create R- retrieve U- Update D - Delete .
What is mean by DDL and Usage?
DDL -- Data Definition Language.
create and modify the structure of database objects.
what is use of sp_adduser in SQL server?
allows you to add a new user to a database.
What is the default value of an integer data type?
"NULL" is the default value of an integer data type.
Will COUNT(column) include columns with null values in its count?
Yes, COUNT(column) include columns with null values in its count.
what is mean by TCL? and explain it usage?
TCL: TCL stands for Transactional Control Language. TCL is used to manage transactions within a database.
Examples: COMMIT, ROLLBACK statements.
How do you display only few # of rows from any table?
Top Clause
What is the Use of ISNull function in SQL Server?
Find the not null value in the list of value.
Difference between:UNION and INTERSET?
UNION: OR operator (value is selected if it appears in either the first or the second statement)
INTERSET:AND operator (value is selected only if it appears in both statements).
Difference between UNION and UNION in SQL ?
UNION : Doesn't return Duplicate Values.
UNION ALL: return All values (includes Duplicate Values)
Difference between Top and LIMIT?
Top: Retrieve Top records from the Table,
LIMIT: Retrieve LIMIT records from the Table.
what is mean by constraint?
constraint:protect columns of the table from unwanted values.
NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY are the types of contraints define in SQL Server.
Difference between Primary and Unique key.
Primary Key:
A column defined as primary key doesnot allow null value.
By default, clustered index in created with the column having primary key.
Unique Key:
A column with unique key defined allows null value.
By default, it creates non-clustered index.
Difference between Store Procedure and function?
Store Procedure:Return Null,Input,OutPut Parameter,Call independently , Not called from Function
Function:Value Return Value,Input Parameter,Call SQL Statement,Called from Procedure
TRUNCATE cannot be rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
DELETE can be rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.
What are the Four Main query statement?Explain them?
Four Query Statement:Select ,Insert, Update and Delete .
Select -- To retrieve a information form a table.
Insert -- To Insert a Value into a table.
Update -- To update a exists value into a table.
Delete -- To delete a row from a Table.
What is an Index?
physical structure containing pointers to the data.
What are the Different Types of Triggers?
1)DML Trigger
2)DDL Trigger
1 )
1.1)Instead of Trigger.
1.2)After Trigger.
What is NOT NULL Constraint
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
What is the maximum size of a row ?
8060 bytes.
Which datatypes can be compressed inside SQL Server?
FLOAT, MONEY, TIMESTAMP,INT, DATE, TIME.
Which datatype allows to store more than 2GB?
Filestream.
Will COUNT(column) include columns with null values in its count?
Yes.
What is the default order of an ORDER BY clause?
Ascending Order.
What is the purpose of the USE command?
used for to select the database. For i.e Use Database Name.
What is the purpose of the model database?
It works as Template Database for the Create Database Syntax.
What are Different Types of Locks?
Types of Locks:
Shared Locks,Update Locks,Exclusive Locks,Intent Locks,Schema Locks,Bulk Update Locks.
What is the difference between a "where" clause and a "having" clause?
WHERE clause:Restriction statement.
Having clause:Using after retrieving the Data
Difference between REVOKE and GRANT?
Grant:It will give permission to the user on database
Revoke: we can remove the permission
What operator performs pattern matching?
LIKE.
What are E-R Diagram?
Entity-Relationship diagram shows the relationship between various tables in the database.
What is mean by JOIN and Types of JOIN?
Used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Types of JOIN:
JOIN: Return rows when there is at least one match in both tables.
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table.
FULL JOIN: Return rows when there is a match in one of the tables.
Why can a "group by" or "order by" clause be expensive to process?
requires creation of Temporary tables to process the results of the query.
How many types of triggers are there?
1. Insert. 2. Delete. 3. Update. 4. Instead of.
Can you create foreign key constraints on temporary tables?
No.
What are the 2 types of Temporary Tables in SQL Server?
1. Local Temporary Tables.2. Global Temporary Tables.
Explain DML statements with examples?
DML: DML stands for Data Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update data in database.
Examples of DML statements: SELECT, UPDATE, INSERT, DELETE statements.
Can you create a view based on other views?
yes.
What are the two types of Triggers in SQL Server?
1. After Triggers : Fired after Insert, Update and Delete operations on a table.
2. Instead of Triggers: Fired instead of Insert, Update and Delete operations on a table.
What is faster, a correlated sub query or an exists?
Exists.
Different kinds of Joins.
OUTER-JOIN - LEFT, RIGHT, CROSS, FULL.
INNERJOIN.
Difference between Set and Select ?
Set is a ANSI standard for variable assignment.We can assign only one variable at a time.
Select is a Non-ANSI standard when assigning variables.We can assign multiple variable at a time.
How and when to use LIKE statement?
(1)% allows you to match any string of any length (including zero length).
(2)_ allows you to match on a single character.
What command do we use to rename a db?
sp_renamedb 'oldname' , 'newname' .
what is store Procedure(SP)?
Its nothing but a set of T-SQL statements combined to perform a single task of several tasks.
Objects of SQL server?
Assemblies,Tables,Reports,Filegroups,Full-text Catalogs,Diagrams,User-defined data types,Views,Roles,Stored procedures,Users,User Defined Functions and Transaction log.
What are the Lock Types in SQL server?
Shared lock,Update lock,Exclusive lock.
Explain Cursor types?
DYNAMIC: It reflects changes happened on the table while scrolling through the row.
STATIC: It works on snapshot of record set and disconnects from the server. This kind doesn't reflects changes happened on the table while scrolling through the row.
KEYSET: In this kind, new record is not reflected, but data modification can be seen.
What are the benefits of User-Defined Functions?
created and defined by the user
They can be used to create joins and simple to invoke as compared to stored procedures.
what is tuple?
instance of data within relational database.
Explain the "EXPLAIN" Statement?
provides information about the optimizer's choice of access path of the SQL.
What are some SQL aggregates and other built-in functions?
AVG, SUM, MIN, MAX, COUNT and DISTINCT.
What is mean by CRUD?
C- Create R- retrieve U- Update D - Delete .
What is mean by DDL and Usage?
DDL -- Data Definition Language.
create and modify the structure of database objects.
what is use of sp_adduser in SQL server?
allows you to add a new user to a database.
What is the default value of an integer data type?
"NULL" is the default value of an integer data type.
Will COUNT(column) include columns with null values in its count?
Yes, COUNT(column) include columns with null values in its count.
what is mean by TCL? and explain it usage?
TCL: TCL stands for Transactional Control Language. TCL is used to manage transactions within a database.
Examples: COMMIT, ROLLBACK statements.
How do you display only few # of rows from any table?
Top Clause
What is the Use of ISNull function in SQL Server?
Find the not null value in the list of value.
Difference between:UNION and INTERSET?
UNION: OR operator (value is selected if it appears in either the first or the second statement)
INTERSET:AND operator (value is selected only if it appears in both statements).
Difference between UNION and UNION in SQL ?
UNION : Doesn't return Duplicate Values.
UNION ALL: return All values (includes Duplicate Values)
Difference between Top and LIMIT?
Top: Retrieve Top records from the Table,
LIMIT: Retrieve LIMIT records from the Table.
what is mean by constraint?
constraint:protect columns of the table from unwanted values.
NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY are the types of contraints define in SQL Server.
Difference between Primary and Unique key.
Primary Key:
A column defined as primary key doesnot allow null value.
By default, clustered index in created with the column having primary key.
Unique Key:
A column with unique key defined allows null value.
By default, it creates non-clustered index.
Difference between Store Procedure and function?
Store Procedure:Return Null,Input,OutPut Parameter,Call independently , Not called from Function
Function:Value Return Value,Input Parameter,Call SQL Statement,Called from Procedure
No comments:
Post a Comment