The following are the Types of constraints
NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
what is stored procedure?
1)Stored Procedure is Predefined collection of SQL
Statements.
2)Stored Procedure is Precompiled .
3)SP will reduce the client server network traffic.
4)SP will reduce the bandwidth.
5)more number of sql statements can execute the one place.
6)easy to ERROR Handling.
7)Sp Have input and output parameters.
2)Stored Procedure is Precompiled .
3)SP will reduce the client server network traffic.
4)SP will reduce the bandwidth.
5)more number of sql statements can execute the one place.
6)easy to ERROR Handling.
7)Sp Have input and output parameters.
Difference Between Where Clause and Having Clause?
Where clause is used for check condition or fetch specfic
values
select * from employee where empid=1001
Having clause is used for Grouping Group by Clause.
avg,max,count,min....
select * from employee where empid=1001
Having clause is used for Grouping Group by Clause.
avg,max,count,min....
What is Trigger ,types of Trigger?
Trigger is a self contained block of statements that is
fired when insert update delete statement(automaticaly)
two types of trigger available in sql server
1)instead of trigger : Fired instead of Insert, Update and Delete operations on a table.
2)After Trigger: Fired after Insert, Update and Delete operations on a table.
sql server 2005 trigger supporting for DML-statements(Insert ,update,delete)
sql server 2008 DML and DDL Also
two types of trigger available in sql server
1)instead of trigger : Fired instead of Insert, Update and Delete operations on a table.
2)After Trigger: Fired after Insert, Update and Delete operations on a table.
sql server 2005 trigger supporting for DML-statements(Insert ,update,delete)
sql server 2008 DML and DDL Also
What is Self Join?
To join within that table is called self join.
Example to check Duplicate Records:
select empid from employee where empid not in(select empid from employee group by empid having count(*)>1)
select empid a ,empname b from emp a, emp b
where a.empid=b.empid
Example to check Duplicate Records:
select empid from employee where empid not in(select empid from employee group by empid having count(*)>1)
select empid a ,empname b from emp a, emp b
where a.empid=b.empid
Define Constraints in SQL SERVER
Constraints Provide the Mechanism provide integrity in the
data base to create a table to check some conditions.
There are Three type of constraints in sql server
1) Domain Integtity Constraint(not null,check)
2) Entity Integrity Constrinnt( Primary key ,Unique Key)
3) Referential Integrity Constraint(Parent table,Child Table Relationship)..
There are Three type of constraints in sql server
1) Domain Integtity Constraint(not null,check)
2) Entity Integrity Constrinnt( Primary key ,Unique Key)
3) Referential Integrity Constraint(Parent table,Child Table Relationship)..
joins in SQL SERVER
Joins Actualy performs Two or more table combined into a
single result set..
Three are Four Types of joins in SQL SERVER.
1) inner join
2) Outer join(Left outer join,Right outer join)
3) Full join
4) Cross join
Three are Four Types of joins in SQL SERVER.
1) inner join
2) Outer join(Left outer join,Right outer join)
3) Full join
4) Cross join
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 DDL and Usage?
DDL -- Data Definition Language.
create and modify the structure of database objects.
create and modify the structure of database objects.
Tell me types of triggers?
Insert ,Update, Delete, Instead of
What are some SQL aggregates and other built-in functions?
AVG, SUM, MIN, MAX, COUNT and DISTINCT.
What keyword does an SQL SELECT statement use for a string search?
The LIKE keyword allows for string searches. The % sign is
used as a wildcard.
What are the Lock Types in SQL server?
Shared lock,
Update lock, Exclusive lock
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
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
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
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.
Examples of DML statements: SELECT, UPDATE, INSERT, DELETE statements.
Can you create a view based on other views?
Yes
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
What is the difference between a "where" clause and a "having" clause?
WHERE clause:
Restriction statement.
Having clause:
Using after retrieving the Data
Restriction statement.
Having clause:
Using after retrieving the Data
Difference between REVOKE and GRANT?
GRANT : Define Access Privilege
REVOKE:Removes a previously granted or denied permission.
REVOKE:Removes a previously granted or denied permission.
grant will give permission to the user on database
by using revoke we can remove the permission
by using revoke we can remove the permission
GETDATE: the precision is till milliseconds
SYSDATETIME the precision is till nanoseconds.
SYSDATETIME the precision is till nanoseconds.
Filestream
Yes
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.
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.
8060 bytes
PK will enforce entity integrity whereas FK will enforce
referential integrity.
When database and transaction log fields of production
server is backed up automatically to a non-production server then this process
is called Log Shipping.
All Database systems which include transaction support
implement ACID properties to ensure the integrity of the database. ACID stands
for Atomicity, Consistency, Isolation and Durability
Atomicity: Each transaction is said to be "atomic." If one part of the transaction fails, the entire transaction fails. Modifications on the data in the database either fail or succeed.
Consistency: This property ensures that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database's consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.
Isolation: It requires that multiple transactions occurring at the same time not impact each other's execution.
Durability: It ensures that any transaction committed to the database will not be lost.
Atomicity: Each transaction is said to be "atomic." If one part of the transaction fails, the entire transaction fails. Modifications on the data in the database either fail or succeed.
Consistency: This property ensures that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database's consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.
Isolation: It requires that multiple transactions occurring at the same time not impact each other's execution.
Durability: It ensures that any transaction committed to the database will not be lost.
The msdb database stores information regarding database
backups, SQL Agent information, DTS packages, SQL Server jobs, and some
replication information such as for log shipping.
A local table exist only for the duration of connection.
No, Identity and GUID column do not need to be indexed.
\What are the benefits of views?
Users focus only on data that they need; user manipulation
of data is simplified; database and query complexity is hidden from users,
allowing users to see friendly names; and views provide a security mechanism by
allowing users access to data through views only.
Primary key in SQL is also known as Surrogate Key. and we
all know that primary key can not null and always unique.
0 is the minimum value of TINYINT datatype in SQL Server
When two or more user try to update same type of data in a
table then Database Concurrency helps in this situation.
There are 2 types of Database Concurrency
Pessimistic: When one user try to change the data with pessimistic concurrency then a lock is placed on the data in a table so that another user cannot change data. when one user finishes then other user can able to change.
Optimistic: When two user works on the same data in a table and one change that data first then second user cannot change that same data becasue the which he have using is allready changed so he cannot do the change becasue change apply to another data that is changed by first user.
There are 2 types of Database Concurrency
Pessimistic: When one user try to change the data with pessimistic concurrency then a lock is placed on the data in a table so that another user cannot change data. when one user finishes then other user can able to change.
Optimistic: When two user works on the same data in a table and one change that data first then second user cannot change that same data becasue the which he have using is allready changed so he cannot do the change becasue change apply to another data that is changed by first user.
To copy only the structure, the WHERE clause of the SELECT
command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.
Copy data from one
table to another
INSERT
INTO TABLE2 SELECT * FROM TABLE1
