SQL Server FAQ
Backup [4 types] :-
1.Full backup
2.Differntail backup
3.Transcation log backup
4. File & File group backup Full backup : it's take full databaseDifferential backup : what are the changes made after fullback up to current backup is called differential backup
Lock [4 types]
1.Shared (S) Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
2. Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
3. Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
4. Intent Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
View If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specific users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time
Primary key is a unique id for each table. This prevents duplication of records cannot be null. Each table can have a single primary key. It can be referenced by any tables as a foreign key
INDEX – [2types] are physical address of data, its is same just we Have indexes in our Book. through this we can make our search much faster. in Idle way always try to make Indexes on that Column which have Integer Value(i.e. Not on text type).There are two types of Indexes in SQL SEREVR
1. Clustered Index.(Create automatically if there is a Primary key on Table)
2. Non Clustered Index -249
ISOLATION Transaction LEVEL [ 5 Types ]
1. READ UNCOMMITTED
2. READ COMMITTED
3.REPEATABLE READ
4.SNAPSHOT
5.SERIALIZABLE
Replication Is way of keeping data synchronized in multiple DB.SQL server replication has two important aspects Publisher and SubscriberPublisher à Database server that makes data available for replication is called as publisher
Subscriber : - Database server that get data from the publisher is called as subscribersSnapshot Replication , Transactional Replication , Merge Replication
Cursors help us to do an operation on a set of data that we retrieve by commands such as Select columns from table. example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retrieval one by one and remove rows which have duplicate values 4 Types of Cursor [FORWARD_ONLY, STATIC, KEYSET, and DYNAMIC]Forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursorStatic cursor always displays the result set as it was when the cursor was openedKeyset cursor, a row deletion followed by a row insertion using the same key as the deleted row caused the inserted row to occupy the slot of the original rowDynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor
Normalization: - eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table).
1NF - Eliminate duplicative columns from the same table. Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
2NF - Remove subsets of data that apply to multiple rows of a table and place them in separate tables. Create relationships between these new tables and their predecessors through the use of foreign keys.
3NF -Remove columns that are not dependent upon the primary key
4NF -A relation is in 4NF if it has no multi-valued dependencies.
Inner join: displays matched rows from tables. this is based on equality condition ,so also called equijoin.
exp- select empid,d.deptid, deptname, empname, location,salary from emp e inner join dept d on e.deptid=d.deptid
Cross join. It results every row of one table matching with every row of another table.
exp- select * from emp cross join dept
Self join: This type of join joins a table to itself.
outer join:Three types -
a.left outer join: It returns match rows from both tables as well as all unmatched rows from left table.
exp- select empid,d.deptid,deptname,name,location,salaryfrom emp e left outer join dept d on e.emp=d.deptit
displays all data from emp table .
b.right Outer join:It reults matched rows from both tables as well as all unmatched rows from right table.
exp- select empid,d.deptid,deptname,name,location,salaryfrom emp e right outer join dept d on e.emp=d.dept
it displays all data from dept table .
C.Full Outer Join: This type of join results all matched rows from both tables as well as unmatched rows from both tables.
exp .- select empid,d.deptid,deptname,name,location,salary from emp e full outer join dept d on emp e=d.dept d
Displays all matched & unmatched rows from both emp & dept tables.
RAID what are different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. MSDN has some information about RAID levels and for detailed information, check out the RAID advisory board’s homepage
Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictionary - case insensitive and Binary
Bulk Copy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same
Deadlock occurs when two or more processes have acquired locks on their respective resource, and they need to get an incompatible lock on the other's resource in order to finish a transaction. The result is mutual blocking: each waits on the other to acquire some resource that the other process already has.
Difference Between the HAVING and WHERE Clauses
1. The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
2.The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3.The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functionsdisplay all tables names in SQL Server (Query analyzer)? select * from sysobjects where xtype='U'
Triggers [2 types] are basically used to implement business rules. Triggers are also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.
AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed.
Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server.
AFTER triggers can be specified only on tables
Function [4 type]
1)Table-Valued Function: Return Data table
2)Scalar Valued Function: return Single value
3)Aggregated Function: Return Aggregated Result set
4)System Funtion:used by system like rowset etc
Tune a stored procedure? There could be 'n' reasons for slow performance. For tuning the performance of a SP we need to know how much resources it is using and how much time it takes in execution. We can check this using the performance monitor (for example, RPC Counter) and execution plan. Check if we are using appropriate joins, table defrag, index rebuilt can be the solutions.
Maximum and minimum
Maximum Parameters per stored procedure 1024
Nested stored procedure level -32
REFERENCES per table - 253
Clustered indexes -1
Non-Clustered indexes - 249
Foreign key table references per table - 253
Trigger - 32
Tables per SELECT statement 256
Clustered index-1 is a special type of index that reorders The way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
Non-clustered index-249 is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
Difference between DELETE & TRUNCATE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster.
Constraints? Explain different types of constraints. Constraints enable the RDBMS enforce the integrity of the database automatically,
without needing you to create triggers, rule or defaults.
Types of Constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY. For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"
Candidate key, alternate key, composite key.
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.
DBCC SHOWCONTIG - TO FIND THE FRAGMENTATIONDBCC SHRINKDATABASE -
TO REDUCE THE SIZE OF THE DATABASE DBCc SHRINK FILE - TO REDUCE THE SIZE OF THE FILE
ACID : - Atomicity : Group of T-SQL statements act in a batch . Consistency :
All or none concept.Isolation: Visibility of resources used in one transaction to concurrent Transactions Durability :
Once changes are committed to database they are permanent .
Advantages in sql 2005 over sql 2000Sql server 2005 have some new data types like varchar (max), xml. Sql server 2005 have SSIS[SQL Server Integration Services] instead of dts in sql server 2000. SSRS[SQL Reporting Services] with adhoc reporting. Some sys command are changed like sys.objects, sys.columns
Delete Duplicate Record
delete empid from emp a where (select count(*) from emp b where a.empid = b.empid)>1Find Duplicate value
Select empid from emp a where (select count(*) from emp b where a.empid = b.empid)>1
***********
Select Empid From depat Group By deptno Having count (*)> 1***********
select empid from test.dbo.Employee a where (select count(*) from test.dbo.Employee b where a.empid=b.empid)>1
Nth highest salary
Select salary from emp a where n=(select count(distinct salary) from emp B where a. salary <= b. salary) Group by having :- select dept, sum(salary) from emp group by dept having sum(salary) >=1000
Who is belong to which Manager
select worker.ename 'Works for' manager.ename from emp worker, emp manager where worker.manager=manager.ename
Rename DB
sp_renamedb ‘oldname’ , ‘newname’
Copy structure only
create table newtable AS select * from oldtable where 1=2
Single query Update salary in some condition
UPDATE employee SET salary = CASE WHEN salary
BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary
BETWEEN 40000 AND 55000 THEN salary + 7000 WHEN salary
BETWEEN 55000 AND 65000 THEN salary + 10000
END
SELECT * FROM information_schema.tables where Table_type='BASE TABLE'
Error Handling In Stored procedure
CREATE PROCEDURE dbo.add_emp @emp_id varchar(11),@emp_name varchar(40),@phone char(12)
AS
-- Execute the INSERT statement.
INSERT INTO employee (emp_id,emp_name,phone) values (@emp_id, @emp_name, @phone)
-- Test the error value.
IF @@ERROR <> 0 BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT 'An error occurred loading the new Employee information'
RETURN(99)
END /
ELSE BEGIN
-- Return 0 to the calling program to indicate success.
PRINT 'The new Employee information has been loaded'
RETURN(0)
END
GO to EXEC add_emp '198824','ABC','1234567890'
What’s the maximum size of a row?
Which TCP/IP port does SQL Server run on?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties --> Port number. both on client and the server.
To find out the databse Name
Nth highest salary
Select salary from emp a where n=(select count(distinct salary) from emp B where a. salary <= b. salary) Group by having :- select dept, sum(salary) from emp group by dept having sum(salary) >=1000
Who is belong to which Manager
select worker.ename 'Works for' manager.ename from emp worker, emp manager where worker.manager=manager.ename
Rename DB
sp_renamedb ‘oldname’ , ‘newname’
Copy structure only
create table newtable AS select * from oldtable where 1=2
Single query Update salary in some condition
UPDATE employee SET salary = CASE WHEN salary
BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary
BETWEEN 40000 AND 55000 THEN salary + 7000 WHEN salary
BETWEEN 55000 AND 65000 THEN salary + 10000
END
SELECT * FROM information_schema.tables where Table_type='BASE TABLE'
Error Handling In Stored procedure
CREATE PROCEDURE dbo.add_emp @emp_id varchar(11),@emp_name varchar(40),@phone char(12)
AS
-- Execute the INSERT statement.
INSERT INTO employee (emp_id,emp_name,phone) values (@emp_id, @emp_name, @phone)
-- Test the error value.
IF @@ERROR <> 0 BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT 'An error occurred loading the new Employee information'
RETURN(99)
END /
ELSE BEGIN
-- Return 0 to the calling program to indicate success.
PRINT 'The new Employee information has been loaded'
RETURN(0)
END
GO to EXEC add_emp '198824','ABC','1234567890'
What’s the maximum size of a row?
8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’. 1024 columns per table. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".
Which TCP/IP port does SQL Server run on?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties --> Port number. both on client and the server.
To find out the databse Name
SELECT DB_NAME() AS DataBaseName
