What is data integrity? Explain constraints?

Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
...

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

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.

Best way to remember joins

What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

 

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

 

How do you load large data to the SQL server database?

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.

How to get @@ERROR and @@ROWCOUNT at the Same Time?

If @@Rowcount is checked after Error checking statement, then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement, then @@Error would get reset. To get @@error and @@rowcount at the same time, include both in same statement and store them in a local variable.

SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

What’s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

How to copy the tables, schema and views from one SQL Server to another?

There are multiple ways to do this.

“Detach Database” from one server and “Attach Database” to another server.
...Manually script all the objects using SSMS and run the script on new server.
Use Wizard of SSMS.

SQL Server - Common Interview Questions and Answers(31-33)

31. What is BCP? When does it used?

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

32. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

33. What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

SQL Server - Common Interview Questions and Answers(25-30)

25. 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.

26. How to get @@ERROR and @@ROWCOUNT at the same time?

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable.

SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

 

27. What is a Scheduled Jobs or What is a Scheduled Tasks?

Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

28. What are the advantages of using Stored Procedures?

1. Stored procedure can reduced network traffic and latency, boosting application performance.
2. Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
3. Stored procedures help promote code reuse.
4. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
5. Stored procedures provide better security to your data.

29. What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?

Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.

30. Can SQL Servers linked to other servers like Oracle?

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

SQL Server - Common Interview Questions and Answers(19-24)

19. What is the difference between a Local and a Global temporary table?

1. A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
2. A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

20. What is the STUFF function and how does it differ from the REPLACE function?

STUFF function is used to overwrite existing characters. Using this syntax, STUFF (string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string. REPLACE function to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

21. What is PRIMARY KEY?

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

22. What is UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

23. What is FOREIGN KEY?

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

24. What is CHECK Constraint?

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

SQL Server - Common Interview Questions and Answers(13-18)

13. Which command using Query Analyzer will give you the version of SQL server and operating system?

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

14. What is SQL Server Agent?

SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full- function scheduling engine, which allows you to schedule your own jobs and scripts.

15. Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

16. What is Log Shipping?

Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

17. Name 3 ways to get an accurate count of the number of records in a table?

SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

18. What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

SQL Server - Common Interview Questions and Answers(7-12)

7. What is difference between DELETE and TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

1. TRUNCATE:
   1. TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
   2. TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
   3. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
   4. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
   5. TRUNCATE cannot be rolled back.
   6. TRUNCATE is DDL Command.
   7. TRUNCATE Resets identity of the table

DELETE:

1. DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
2. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
3. DELETE Can be used with or without a WHERE clause
4. DELETE Activates Triggers.
5. DELETE can be rolled back.
6. DELETE is DML Command.
7. DELETE does not reset identity of the table.

Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

8. When is the use of UPDATE_STATISTICS command?

This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

9. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

10. What are the properties and different Types of Sub-Queries?

1. Properties of Sub-Query
   1. A sub-query must be enclosed in the parenthesis.
   2. A sub-query must be put in the right hand of the comparison operator, and
   3. A sub-query cannot contain an ORDER-BY clause.
   4. A query can contain more than one sub-query.

Types of Sub-Query

1. Single-row sub-query, where the sub-query returns only one row.
2. Multiple-row sub-query, where the sub-query returns multiple rows,. and
3. Multiple column sub-query, where the sub-query returns multiple columns

11. What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

12. What are the authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode - SQL and Windows. To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

SQL Server - Common Interview Questions and Answers(1-6)

1. Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.

2. What are the difference between clustered and a non-clustered index?

1. A clustered index 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.
2. A non clustered index 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.

3. What are the different index configurations a table can have?

A table can have one of the following index configurations:

1. No indexes
2. A clustered index
3. A clustered index and many nonclustered indexes
4. A nonclustered index
5. Many nonclustered indexes

4. What are different types of Collation Sensitivity?

1. Case sensitivity - A and a, B and b, etc.
2. Accent sensitivity
3. Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
4. Width sensitivity - A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.

5. What is OLTP (Online Transaction Processing)?

In OLTP - online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

6. What's the difference between a primary key and a unique key?

Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

How can we improve SQL performance using SQL profiler?

Answer:

 
By using SQL profiler we can capture the load and then feed that load to SQL Server tuning advisor. Tuning advisor scans the load and then gives out index suggestions.

SQL “Group by” Clause syntax capability?

Answer:

 
Let us assume that we have the following table of Tourist with their respective rows,columns and data.



Assuming the above table, we have to display the total number of tourists from the different part of the countries.
In order to achieve the required result we have to use SQL “Group by” clause.
Query: -

select T1.TouristCountry,count(T1.TouristName) as NumberOfTourist from 
Tourist T1 group by T1.TouristCountry

Output: -

Hence you can see that the total number of tourist belongs from different countries has been displayed.

What is the difference between char (10) and nchar (10)?

Answer:

 

char	nchar
char are fixed length data-types.	nchar are also fixed length data-types.
char does not support Unicode character.	nchar support Unicode character.
char reserves 1 byte(8 bits) of memory space.	nchar reserves 2 bytes (16 bits) of memory space.
char support Ansi code character, which max upto 256 character.	nchar support Unicode character, which max upto 65536 character.
char(n) specifies a length of n bytes by default.	nchar(n) specifies a length of n characters by default.
char does not support character of different languages.	nchar support characters of different languages.

In order to view the exact difference between the char(10) and nvarchar(10), we will use DataLength.
DataLength: -
Use DATALENGTH when the actual number of bytes is required, including trailing spaces.So, in a double-byte encoding, you'll get actual bytes, not characters.
Let us assume that we have following table with their respective fields and values.



Now let’s see what will be the DataLength of the StudentName column which is declared char (10) as datatype and what will be the DataLength of the StudentAddress column which is declared nchar(10) as datatype.
Query: -

select DATALENGTH(CustomerName) as DataLengthOfchar ,DATALENGTH(CustomerAddress) 
as DataLengthOfnchar from Customer

Output: -

Can you explain and show how indexes make search faster?

Answer:

 

SQL Server Indexes are favorites of interviewers when it comes to SQL Server interviews and .NET interviews. One of the basic questions which interviewers ask is “why do we need indexes?” and our excited developer friend shouts “PERFORMANCE”.

Then the smart interviewer drills down more asking further how does it increase performance and there’s a big “SILENCE” :-).

In this question let’s demonstrate practically how indexes improve performance and why.

Assume that we have the following “Customer” table.



Let’s first see an example for the above table without creating an index on it and let’s look how much time the SQL engine takes to fetch the required data.





You can see from the above image that there are no indexes.

In order to view what exact time does the SQL engine takes to retrieve the requested data, you need to set the “set statistics io on” in your query.

Query: - set statistics io on Select * from Customer where Customer.CustomerID = 1034

When you execute your query the result set will be shown along with the Message like below diagram.




In the above diagram you can see that the Logical reads is 17

Now let’s see an example for the same table but with “Index” created on it and let’s see the difference between the logical reads value.

When we create an index on any column of a table then data gets divided like the following B-Tree diagram, so that search becomes easier and faster. For example if we want to search 1500 it will straight go to the section 1001 – 1500 rather than looping though all records.





To create index on table follow the below steps




Query: - Set statistics io on Select * from Customer where Customer.CustomerID = 1034

The result set will look by below diagram.



In above diagram you can see that now the “Logical read is 11” as compared to the result set of the table without index which was “Logical read 17”.

This shows that the indexes make search faster because it uses B-Tree structure to search the required result set.

One of the other ways to increase performance is by using SQL Server profiler and index tuning wizard.

Are SQL Server Views Updatable?

Answer:
View is a virtual table, which can contains data (rows with columns) from one or more table and you can retrieve data from a view.

Let’s demonstrate a simple example in which we will see that how to create a view on single table and will also see that if we update the view the respective table on which the view is created  is updated or not.

Now let first see how to create view.

Go to View folder in SQL Server > Right click on it > select New View.

As soon as you click on New View, the following window will appear like below.

Now, just select the table name from the list on which you wish to create a View and Click on Add then click on close. Once you click on close a new window will appear which allow you to create View on the respective column.



After selecting the column name just save the view and give View a nice name.

Once you have completed the above step you will see that the respective View is added in the View folder.

Now let’s see that when we update the view the respective table is also updated or not.
Query:-

Update [Practice].[dbo].[Cust_View] set Customer_Contact = 96641122 where Customer_Name = 'Feroz'

Now just go to the table on which the view was created and check whether the table is updated or not, you will see that the table is also updated when you update the View.
Now let’s create a view based on two tables and try to update a view.

create view View_Cust as SELECT    dbo.Customer.Customer_Name, dbo.Customer.Customer_Contact,dbo.[Order].Product_Name,dbo.[Order].Price
FROM dbo.Customer
INNER JOIN dbo.[Order] ON dbo.Customer.Order_ID = dbo.[Order].Order_ID

Let’s try to Update View:

Query:-

Update [Practice].[dbo].[View_Cust] set Customer_Contact = 098767554, Price = 4000 where Customer_Name = 'Feroz'

As you can see in the above query, I am trying to update a column from the Product table and another column from the Order table and try to execute the query the compiler will throw the below error.
Error Message:- View or function 'Practice.dbo.View_Cust' is not updatable because the modification affects multiple base tables.
This means that when you try to update both the table’s column from the view then it is not allowed but you can update single table column.

Select second highest salary from the table?

Answer:
Let's us assume that we have the following table of Employee.

Emp_Id	Emp_Name	Emp_Salary
1	Shiv	17000
2	Raju	13500
3	Sham	15000
4	Moosa	11000
5	Feroz	12000

Now we want to find out second highest salary from Employee table, as you see that the second highest salary is 15000 and we want to display the same.

Query:-

SELECT Emp_Name,Emp_Salary
FROM Employee e1
WHERE
2 = (SELECT COUNT(DISTINCT (e2.Emp_Salary))FROM Employee e2 WHERE e2.Emp_Salary >= e1.Emp_Salary)

The above employee table contains the second highest salary as 15000 therefore the result set will look like below output table.

Output:-

Emp_Name	Emp_Salary
Sham	15000

        
If the table contains two or more same record of salary which is the second highest salary then the query will give you all the record of second highest salary as you see in the above output table.

What is trigger and different types of Triggers?

Answer:

 

Trigger is a SQL server code, which execute when a kind of action on a table occurs like insert, update and delete. It is a database object which is bound to a table and execute automatically.

Triggers are basically of two type’s namely "After Triggers" and "Instead of Triggers".

1.After Triggers:- this trigger occurs after when an insert, update and delete operation has been performed on a table.

“After Triggers” are further divided into three types
AFTER INSERT Trigger.
AFTER UPDATE Trigger.
AFTER DELETE Trigger.

Let us consider that we have the following two tables.

Create “Customer” table with the following field as you see in the below table.

Cust_ID	Cust_Code	Cust_Name	Cust_Salary
1	A-31	Moosa	4500
2	A-09	Feroz	5000
3	A-16	Wasim	4000

Create “Customer_Audit” table with the following field as you see in the below table.

Cust_ID

Cust_Name

Operation_Performed

Date_Time

The main purpose of creating “Customer_Audit” table is to record the data which occurs on the “Customer” table with their respective operation and date-time.

Let’s begin with “After Insert Trigger”:- This trigger fire after an insert operation performed on a table.
Let us see the example of “After Insert Trigger” for better understanding.

Query:-Create Trigger TrigInsert on Customer
For insert as
declare @Cust_ID int;
declare @Cust_Name varchar(100);
declare @Operation_Performed varchar(100);
select @Cust_ID=i.Cust_ID from inserted i;
select @Cust_Name=i.Cust_Name from inserted i;
set @Operation_Performed='Inserted Record -- After Insert Trigger';
insert into Customer_Audit
(Cust_ID,Cust_Name,Operation_Performed,Date_Time)
values(@Cust_ID,@Cust_Name,@Operation_Performed,getdate());
PRINT 'AFTER INSERT trigger fired.'

Now, insert a record into Customer table:

Query:- insert into Customer values ('A-10','Danish')

Once the insert statement is successfully done, the record is inserted into the “Customer” table and the “After Trigger” (TrigInsert) is fired and the same record is also stored into “Cutomer_Audit” table.

To see the record in “Customer_Audit” table write query as below:-

Query:- select * from Customer_Audit

Cust_ID	Cust_Name	Operation_Performed	Date_Time
4	Danish	Inserted Record -- After Insert Trigger	2011-04-06 19:46:56.390

You can see that the same record is seen in the “Customer_Audit” table with Operation_performed and the date_time when it was updated.

Now let’s see for “After Update Trigger”:-This trigger fire after an update operation performed on a table.
Let us see the example of “After Update Trigger” for better understanding.

Query:- Create trigger TrigUpdate on Customer
For Update as
declare @Cust_ID int;
declare @Cust_Name varchar(100);
declare @Operation_Performed varchar(100);
select @Cust_ID=i.Cust_ID from inserted i;
select @Cust_Name=i.Cust_Name from inserted i;
set @Operation_performed='Inserted Record -- After Insert';

if update(Cust_Name)
set @Operation_Performed='Updated Record -- After Update Trigger.';
insert into Customer_Audit
(Cust_ID,Cust_Name,Operation_Performed,Date_Time)
values(@Cust_ID,@Cust_Name,@Operation_Performed,getdate())

PRINT 'AFTER UPDATE Trigger fired.'

Now, update a record into “Customer” table:-

Query:- update Customer set Cust_Name = 'Khan Wasim' where Cust_Code like 'A-16'

The record is updated into the Customer table and the TrigUpdate is fired and it stores a record into
“Cutomer_audit” table.

To see the record Customer_Audit table write query for that.

Query:- select * from Customer_Audit

Cust_ID	Cust_Name	Operation_Performed	Date_Time
4	Danish	Inserted Record -- After Insert Trigger	2011-04-06 19:46:56.390
3	Khan Wasim	Updated Record -- After Update Trigger	2011-04-06 20:03:05.367

Now for, “After Delete Trigger”:-This trigger fire after a delete operation performed on a table.

In a similar way, you can code “After Delete trigger” on the table.

2.Instead of Triggers:- this trigger fire before the DML operations occur, first inserted and deleted get flourished and then trigger fires

“Instead of Triggers” are further divided into three types
Instead of INSERT Trigger.
Instead of UPDATE Trigger.
Instead of DELETE Trigger.

Let us see the example of “Instead of UPDATE Trigger” for better understanding.

Query:-
CREATE TRIGGER trgInsteadOfUpdate ON Customer
INSTEAD OF update
AS
declare @cust_id int;
declare @cust_name varchar(100);
declare @cust_salary  int;
select @cust_id=d. Cust_ID from deleted d;
select @cust_name=d. Cust_Name from deleted d;
select @cust_salary =d.Cust_Salary from deleted d;

BEGIN
if(@cust_salary >4500)
begin
RAISERROR('Cannot delete where salary > 4500',16,1);
ROLLBACK;
end
else
begin
delete from Customer where Cust_ID =@cust_id;
COMMIT;
insert into Customer_Audit(Cust_ID,Cust_Name,Cust_Salary,Operation_Performed,Date_Time)
values(@cust_id,@cust_name,@cust_salary,'Updated -- Instead Of Updated Trigger.',getdate());
PRINT 'Record Updated -- Instead Of  Updated Trigger.'
end
END
Now, update a record into “Customer” table:-

Query:- update Customer set Cust_Name = 'Khan Wasim' where Cust_Code like 'A-09'

When you try to update Customer table it will raise an error as we have use Instead of Update trigger.

Error:- Server: Msg 50000, Level 16, State 1, Procedure trgInsteadOfUpdate, Line 15
Cannot update where salary > 4500

In a similar way, you can code “Instead Delete trigger” and “Instead Insert trigger” on the table.

Difference between Stored Procedure and Function?

Answer:

Function are compiled and executed at run time.

Stored Procedure are stored in parsed and compiled format in the database.

Function cannot affect the state of the database which means we cannot perform CRUD operation on the database.

Stored Procedure can affect the state of the database by using CRUD operations.

Store Procedure can return zero or n values whereas Function can return only one value.

Store Procedure can have input,output parameters for it whereas functions can have only input parameters.

Function can be called from Stored Procedure whereas Stored Procedure cannot be called from Function.

What are difference between Cluster index and Non-Cluster index?

Answer:

Both of these indexes uses "B-tree" structure but in Cluster index the "Leaf Node" actually points the physical data, but in Non-Cluster index it point’s to the "Row ID" and then the "Row ID" points to the "Leaf Node" of Cluster Index.

Below is the diagram of Cluster and Non-Clustered index.

Cluster Index	Non-Cluster Index
A table can have only one Cluster Index as it point to the physical data.	A table can have more than one Non-Cluster Index as it only points to the pointer of Cluster Index.
Physical data is stored as per Cluster Index.	There is no relation of physical data.
The leaf node of Cluster Index consist of data pages.	The leaf node of Non-Clusted Index contain Index row.

How does index makes search faster?

Answer:

 

For better understanding, let us consider a simple search example which shows differences between a table, declared with index and without index.

Let's first see an example for a table which is created without declaring an index and look how exactly the SQL search engine will perform action. Below diagram will give u better idea…

In the above example, SQL search engine will search from initial till it finds the respective record and once the record is found it will basically display the record.

Further, When we create an index on any column of a table then the large data get divided like following B-Tree diagram, so that search becomes easier and faster.

                                                B-tree structure of a SQL Server index

For example:-

Suppose we have to search value 25 in an indexed column, the query engine will first look in the “Root Node” to determine which node to refer in the “Branch Nodes”. In the above example first “Branch Node” has Value 1 to 20 and the second “Branch Node” has Value 21 to 40, so the query engine will go to the second “Branch Node” and will skip the first “Branch Node” as we have to search Value 25. Same like “Branch Nodes” the query engine will operate the “Leaf Node” to retrieve respected result.

Can you name some aggregate function is SQL Server?

Answer:
Some of them which every interviewer will expect: -

• AVG: - Computes the average of a specific set of values, which can be an expression list or a set of data records in a table.

• SUM: - Returns the sum of a specific set of values, which can be an expression list or a set of data records in a table.

• COUNT: - Computes the number of data records in a table.

• MAX: - Returns the maximum value from a specific set of values, which can be an expression list or a set of data records in a table.

• MIN: - Returns the minimum value from a specific set of values, which can be an expression list or a set of data records in a table.

What is the difference between unique key and primary key?

Answer:

 

This is a typical SQL Server interview question and below is the comparison sheet.

Unique Key	Primary Key
Unique key can have nulls	Primary key cannot have nulls.
In a single table we can create multiple unique keys.	In a single table we can have only one primary key.
It creates a non-clustered index by default.	It created a clustered index by default.
Both unique keys and primary keys can be referenced by foreign key.

 

What is the difference between DELETE and TRUNCATE?

Answer:

 

Following are difference between them:

• DELETE TABLE: - syntax logs the deletes thus making the delete operations low.

 TRUNCATE table does not log any information but it logs information about deallocation of data page of the table. So TRUNCATE table is faster as compared to delete table.

• DELETE table can be rolled back while TRUNCATE cannot be.

• DELETE table can have criteria while TRUNCATE cannot.

• TRUNCATE table cannot have triggers.

What are different types of joins in SQL?

Answer:
INNER JOIN Inner join shows matches only when they exist in both tables. Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers.Customerid and Orders.Customerid. So this SQL will only give you result with customers who have orders. If the customer does not have order, it will not display that record.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON
Customers.CustomerID =Orders.CustomerID

LEFT OUTER JOIN Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON
Customers.CustomerID =Orders.CustomerID

RIGHT OUTER JOIN Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON
Customers.CustomerID =Orders.CustomerID

What is a DDL, DML and DCL concept in RDBMS world?

Answer:
DDL (Data definition language) defines your database structure. CREATE and ALTER are DDL statements as they affect the way your database structure is organized.
DML (Data Manipulation Language) lets you do basic functionalities like INSERT, UPDATE, DELETE and MODIFY data in database.
DCL (Data Control Language) controls you DML and DDL statements so that your data is protected and has consistency. COMITT and ROLLBACK are DCL control statements. DCL guarantees ACID fundamentals of a transaction.

What is Cascade and Restrict in DROP table SQL? OR What is “ON DELETE CASCADE” and “ON DELETE RESTRICT”?

Answer:

 

RESTRICT specifies that table should not be dropped if any dependencies (i.e. triggers, stored procedure, primary key, foreign key etc) exist. Therefore, if there are dependencies then error is generated and the object is not dropped.

CASCADE specifies that even if there dependencies go ahead with the drop. That means drop the dependencies first and then the main object. So if the table has stored procedures and keys (primary and secondary keys) they are dropped first and then the table is finally dropped.

What is Collation in SQL Server?

Answer:
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

Collation according to language

Note: - Different languages will have different sort orders.

Case Sensitivity

If A and a, B and b, etc. are treated in the same way, then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent Sensitivity

If a and A, o and O are treated in the same way, then it is accent-insensitive. A computer treats a and A differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and A 225. The ASCII value of o is 111 and O is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width Sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

In which Files does SQL Server Actually Store Data?

Answer:
Any SQL Server database is associated with two kinds of files: *.mdf and *.ldf. *.mdf files are actual physical database files where your data is stored finally. *.ldf (LOG) files are actually data, which is recorded from the last time data was committed in the database.

MDF and LDF files.

What is Denormalization?

Answer:
Denormalization is the process of putting one fact in numerous places (it is vice-versa of normalization). Only one valid reason exists for denormalizing a relational design - to enhance performance. The sacrifice to performance is that you increase redundancy in a database.

What is Normalization? OR What are the Different Types of Normalization?

Answer:
Note: - A regular .NET programmer working on projects often stumbles on this question, which is but obvious. The bad part is sometimes the interviewer can take this as a very basic question to be answered and it can be a turning point for the interview. So let's cram it.
It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.
Benefits of Normalizing your database include:

• Avoiding repetitive entries
• Reducing required storage space
• Preventing the need to restructure existing tables to accommodate new data
• Increased speed and flexibility of queries, sorts, and summaries

Note: - During an interview, people expect to answer a maximum of three normal forms and that's what is expected practically. Actually you can normalize database to fifth normal form. But believe this book, answering three normal forms will put you in a decent shape during an interview.
The three normal forms as follows:

First Normal Form

For a table to be in first normal form, data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields.

Repeating groups example

In the above example, city1 and city2 are repeating. In order for these tables to be in First normal form, you have to modify the table structure as follows. Also note that the Customer Name is now broken down to first name and last name (First normal form data should be broken down to the smallest unit).

Customer table normalized to first normal form

Second Normal Form

The second normal form states that each field in a multiple field primary key table must be directly related to the entire primary key. In other words, each non-key field should be a fact about all the fields in the primary key.
In the above table of customer, city is not linked to any primary field.

Normalized customer table.

 

City is now shifted to a different master table.

That takes our database to a second normal form.

Third Normal Form

A non-key field should not depend on another Non-key field. The field Total is dependent on Unit price and qty.

Fill third normal form

So now the Total field is removed and is the multiplication of Unit price * Qty.

Fourth Normal Form

Note: - Whenever the interviewer is trying to go above the third normal form, there can be two reasons, ego or to fail you. Three normal forms are really enough, practically anything more than that is an overdose.
In fourth normal form, it should not contain two or more independent multi-valued facts about an entity and it should satisfy “Third Normal form”.

So let us try to see what multi-valued facts are. If there are two or more many-to-many relationship in one entity and they tend to come to one place, it is termed as “multi-valued facts”.

Multi-valued facts

In the above table, you can see that there are two many-to-many relationships between Supplier / Product and “Supplier / Location (or in short multi-valued facts). In order for the above example to satisfy the fourth normal form, both the many-to-many relationships should go in different tables.

Normalized to Fourth Normal form.

Fifth Normal Form

Note: - UUUHHH if you get this question after joining the company, do ask him if he himself really uses it?
Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.
Example: Dealers sell Product which can be manufactured by various Companies. Dealers in order to sell the Product should be registered with the Company. So these three entities have a mutual relationship within them.

Not in Fifth Normal Form.

The above table shows some sample data. If you observe closely, a single record is created using lot of small information. For instance: JM Associate can sell sweets under the following two conditions:

• JM Associate should be an authorized dealer of Cadbury
• Sweets should be manufactured by Cadbury company

These two smaller bits of information form one record of the above given table. So in order for the above information to be “Fifth Normal Form” all the smaller information should be in three different places. Below is the complete fifth normal form of the database.

 Complete Fifth Normal Form

 

What is the Difference between SQL SERVER 2000 and 2005?

Answer:
Note: - This question will be one of the favorites during SQL SERVER interviews. I have marked the points which should be mentioned by developers as PG and DBA for Database Administrator.
Following are some major differences between the two versions:

• (PG) The most significant change is the .NET integration with SQL SERVER 2005. Stored procedures, user-defined functions, triggers, aggregates, and user-defined types can now be written using your own favorite .NET language (VB.NET, C#, J# etc.). This support was not there in SQL SERVER 2000 where the only language was T-SQL. In SQL 2005, you have support for two languages T-SQL and .NET.
• (PG) SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000. It was a separate installation for SQL Server 2000.
• (PG) SQL SERVER 2005 has introduced two new data types varbinary (max) and XML. If you remember in SQL SERVER 2000, we had image and text data types. Problem with image and text data types is that they assign the same amount of storage irrespective of what the actual data size is. This problem is solved using varbinary (max) which acts depending on amount of data. One more new data type is included XML which enables you to store XML documents and does schema verification. In SQL SERVER 2000, developers used varchar or text data type and all validation had to be done programmatically.
• (PG) SQL SERVER 2005 can now process direct incoming HTTP request without IIS Web server. In addition, stored procedure invocation is enabled using the SOAP protocol.
• (PG) Asynchronous mechanism is introduced using server events. In Server event model the server posts an event to the SQL Broker service, later the client can come and retrieve the status by querying the broker.
• For huge databases, SQLSERVER has provided a cool feature called “Data partitioning”. In data partitioning, you break a single database object such as a table or an index into multiple pieces. But for the client application accessing the single database object, “partitioning” is transparent.
• In SQL SERVER 2000, if you rebuilt clustered indexes even the non-clustered indexes where rebuilt. But in SQL SERVER 2005 building the clustered indexes does not build the non-clustered indexes.
• Bulk data uploading in SQL SERVER 2000 was done using BCP (Bulk copy program’s) format files. Now in SQL SERVER 2005 bulk, data uploading uses XML file format.
• In SQL SERVER 2000 there were maximum 16 instances, but in 2005 you can have up to 50 instances.
• SERVER 2005 has support of “Multiple Active Result Sets” also called as “MARS”. In previous versions of SQL SERVER 2000 in one connection, you could only have one result set. Now in one SQL connection, you can query and have multiple results set.
• In previous versions of SQL SERVER 2000, system catalog was stored in the master database. In SQL SERVER 2005, it’s stored in a resource database which is stored as sys object. You cannot access the sys object directly as in the older version we were accessing the master database.
• This is one of the hardware benefits which SQL SERVER 2005 has over SQSERVER 2000 – support of hyper threading. WINDOWS 2003 supports hyper threading; SQL SERVER 2005 can take advantage of the feature unlike SQL SERVER 2000 which did not support hyper threading.
  Note: Hyper threading is a technology developed by INTEL which creates two logical processors on a single physical hardware processor.
• SMO will be used for SQL Server Management.
• AMO (Analysis Management Objects) to manage Analysis Services servers, data sources, cubes, dimensions, measures, and data mining models. You can mapm AMO in old SQL SERVER with DSO (Decision Support Objects).
• Replication is now managed by RMO (Replication Management Objects).
  Note: SMO, AMO and RMO are all using .NET Framework.
• SQL SERVER 2005 uses current user execution context to check rights rather than ownership link chain, which was done in SQL SERVER 2000.
  Note: There is a question on this later see for execution context questions.
• In previous versions of SQL SERVER the schema and the user name was same, but in current, the schema is separated from the user. Now the user owns schema.
  Note: There are questions on this, refer “Schema” later.
  Note: Ok below are some GUI changes.
• Query analyzer is now replaced by query editor.
• Business Intelligence development studio will be used to create Business intelligence solutions.
• OSQL and ISQL command line utility is replaced by SQLCMD utility.
• SQL SERVER Enterprise manager is now replaced by SQL SERVER Management studio.
• SERVER Manager which was running in system tray is now replaced by SQL Computer manager.
• Database mirror concept is supported in SQL SERVER 2005, which was not present in SQL SERVER 2000.
• In SQL SERVER 2005 Indexes can be rebuilt online when the database is in actual production. If you look back in SQL SERVER 2000, you cannot do insert, update, and delete operations when you are building indexes.
• (PG) Other than Serializable, Repeatable Read, Read Committed, and Read Uncommitted isolation levels, there is one more new isolation level “Snapshot Isolation level”.
  Note: We will see “Snapshot Isolation level” in detail in the coming questions.

Summarizing: -  The major significant difference between SQL SERVER 2000 and SQL SERVER 2005 is in terms of support of .NET Integration, Snap shot isolation level, Native XML support, handling HTTP request, Web service support and Data partitioning. You do not have to really say all the above points during an interview. A sweet summary and you will rock.

What is the Main Difference between ACCESS and SQL SERVER?

Answer:
As mentioned before, Access fulfills all the CODD rules and behaves as a true RDBMS. But there’s a huge difference from an architecture perspective, due to which many developers prefer to use SQL SERVER as the major database rather than Access. Following is the list of architecture differences between them:

• Access uses file server design and SQL SERVER uses the Client / Server model. This forms the major difference between SQL SERVER and ACCESS.
  
  Note: Just to clarify what is client server and file server I will make a quick description of widely accepted architectures. There are three types of architectures:
• Main frame architecture (This is not related to the above explanation but just mentioned as it can be useful during an interview and also for comparing with other architectures)
• File sharing architecture (Followed by ACCESS)
• Client Server architecture (Followed by SQL SERVER).

In Main Frame architecture, all the processing happens on central host server. User interacts through a dumb terminal that only sends keystrokes and information to the host. All the main processing happens on the central host server. So the advantage in such type of architecture is that you need least configuration clients. But the disadvantage is that you need a robust central host server like Main Frames.
In File sharing architecture, which is followed by Access database, all the data is sent to the client terminal and then processed. For instance, if you want to see customers who stay in India, in File Sharing architecture all customer records will be sent to the client PC regardless whether the customer belongs to India or not. On the client PC customer records from India are sorted/filtered out and displayed, in short all processing logic happens on the client PC. Therefore, in this architecture, the client PC should have heavy configuration and it increases network traffic as a lot of data is sent to the client PC. However, the advantage of this architecture is that your server can be of a low configuration.

Figure 1.2: File Server Architecture of Access

In client server architecture, the above limitation of the file server architecture is removed. In client server architecture, you have two entities, client and the database server. File server is now replaced by database server. Database server takes up the load of processing any database related activity and the client does any validation aspect of database. As the work is distributed between the entities it increases scalability and reliability. Second, the network traffic also comes down as compared to file server. For example if you are requesting customers from India, database server will sort/ filter and send only Indian customer details to the client, thus bringing down the network traffic tremendously. SQL SERVER follows the client-server architecture.

 

Figure 1.3: Client Server Architecture of SQL SERVER

• The second issue comes in terms of reliability. In Access, the client directly interacts with the Access file, in case there is some problem in the middle of a transaction, there are chances that an Access file can get corrupt. But in SQL SERVER, the engine sits in between the client and the database, so in case of any problems in the middle of a transaction, it can revert back to its original state.
  Note: SQL SERVER maintains a transaction log by which you can revert back to your original state in case of any crash.
• When your application has to cater to a huge load demand, highly transactional environment and high concurrency, then its better to go for SQL SERVER or MSDE.
• But when it comes to cost and support, Access stands better than SQL SERVER. In case of SQL SERVER, you have to pay for per client license, but Access runtime is free.

Summarizing: - SQL SERVER gains points in terms of network traffic, reliability and scalability whereas Access gains points in terms of cost factor.

What are CODD Rules? OR Does SQL SERVER support all the twelve CODD rules?

Answer:
Note: - This question can only be asked on two conditions when the interviewer is expecting you to be at a DBA job or you are complete fresher, yes and not to mention the last one he treats CODD rules as a religion. We will try to answer this question from the perspective of SQL SERVER.
In 1969, Dr. E. F. Codd laid down 12 rules, which a DBMS should adhere to in order to get the logo of a true RDBMS.

Rule 1: Information Rule

"All information in a relational database is represented explicitly at the logical level and in exactly one way - by values in tables."
In SQL SERVER, all data exists in tables and are accessed only by querying the tables.

Rule 2: Guaranteed Access Rule

"Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name."
In flat files, we have to parse and know the exact location of field values. But if a DBMS is truly an RDBMS, you can access the value by specifying the table name, field name, for instance Customers.Fields [‘Customer Name’].
SQL SERVER also satisfies this rule. In ADO.NET we can access field information using table name and field names.

Rule 3: Systematic Treatment of Null Values

"Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.”
In SQL SERVER, if there is no data existing, NULL values are assigned to it. Note NULL values in SQL SERVER do not represent spaces, blanks or a zero value; it is a distinct representation of missing information and thus satisfies rule 3 of CODD.

Rule 4: Dynamic On-line Catalog Based on the Relational Model

"The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data."
The Data Dictionary is held within the RDBMS. Thus, there is no need for off-line volumes to tell you the structure of the database.

Rule 5: Comprehensive Data Sub-language Rule

"A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all the following items:

• Data Definition
• View Definition
• Data Manipulation (Interactive and by program)
• Integrity Constraints
• Authorization
• Transaction boundaries ( Begin, commit and rollback)"

SQL SERVER uses SQL to query and manipulate data, which has a well-defined syntax and is being accepted as an international standard for RDBMS.
Note: According to this rule, CODD has only mentioned that some language should be present to support it, but not necessary that it should be SQL. Before the 80’s, different’s database vendors were providing their own flavor of syntax until in 1980, ANSI-SQL came in to standardize this variation between vendors. As ANSI-SQL is quite limited, every vendor including Microsoft introduced their additional SQL syntax in addition to the support of ANSI-SQL. You can see SQL syntax varying from vendor to vendor.

Rule 6: View-updating Rule

"All views that are theoretically updatable are also updatable by the system."
In SQL SERVER, not only views can be updated by the user, but also by SQL SERVER itself.

Rule 7: High-level Insert, Update and Delete

"The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data, but also to the insertion, update and deletion of data."
SQL SERVER allows you to update views that in turn affect the base tables.

Rule 8: Physical Data Independence

"Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representations or access methods."
Any application program (C#, VB.NET, VB6, VC++ etc) does not need to be aware of where the SQL SERVER is physically stored or what type of protocol it is using, the database connection string encapsulates everything.

Rule 9: Logical Data Independence

"Application programs and terminal activities remain logically unimpaired when information-preserving changes of any kind that theoretically permit un-impairment are made to the base tables."
Application programs written in C# or VB.NET do not need to know about any structure changes in SQL SERVER database. Example: adding of new field etc.

Rule 10: Integrity Independence

"Integrity constraints specific to a particular relational database must be definable in the relational data sub-language and storable in the catalog, not in the application programs."
In SQL SERVER, you can specify data types (integer, nvarchar, Boolean etc.) which put in data type checks in SQL SERVER rather than through application programs.

Rule 11: Distribution Independence

"A relational DBMS has distribution independence."
SQL SERVER can spread across more than one physical computer and across several networks; but from application programs, it has not a big difference but just specifying the SQL SERVER name and the computer on which it is located.

Rule 12: Non-subversion Rule

"If a relational system has a low-level (single-record-at-a-time) language, that low level cannot be used to subvert or bypass the integrity Rules and constraints expressed in the higher level relational language (multiple-records-at-a-time)."
In SQL SERVER whatever integrity rules are applied on every record are also applicable when you process a group of records using application program in any other language (example: C#, VB.NET, J# etc.).
Readers can see from the above explanation that SQL SERVER satisfies all the CODD rules, some database gurus consider SQL SERVER as not truly being an RDBMS, but that’s a matter of debate.

What is the Difference between DBMS and RDBMS?

Answer:
As mentioned before, DBMS provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information. RDBMS also provides what DBMS provides, but above that, it provides relationship integrity. So in short, we can say:

RDBMS = DBMS + REFERENTIAL INTEGRITY

For example, in the above Figure 1.1, every person should have an Address. This is a referential integrity between Name and Address. If we break this referential integrity in DBMS and files, it will not complain, but RDBMS will not allow you to save this data if you have defined the relation integrity between person and addresses. These relations are defined by using “Foreign Keys” in any RDBMS.
Many DBMS companies claimed that their DBMS product was RDBMS compliant, but according to industry rules and regulations, if the DBMS fulfills the twelve CODD rules, it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc.) fulfill all the twelve CODD rules and are considered truly as RDBMS.
Note: - One of the biggest debates is whether Microsoft Access is an RDBMS? We will be answering this question in later section.

What is a Database or Database Management System (DBMS)? OR What is the difference between a file and a database? OR Can files qualify as a database?

Answer:
Note: - Probably these questions are too basic for experienced SQL SERVER guys. But from a fresher’s point of view, it can be a difference between getting a job and being jobless.

1. Database provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information.
2. Secondly, the information has to be persistent, that means even after the application is closed the information should be persisted.
3. Finally, it should provide an independent way of accessing data and should not be dependent on the application to access the information.

Ok, let me spend a few more sentences on explaining the third aspect. Below is a simple figure of a text file that has personal detail information. The first column of the information is Name, second Address and finally Phone Number. This is a simple text file, which was designed by a programmer for a specific application.

Non-Uniform Text File

It works fine in the boundary of the application. Now, some years down the line a third party application has to be integrated with this file. In order for the third party application to be integrated properly, it has the following options:

• Use the interface of the original application.
• Understand the complete details of how the text file is organized, example the first column is Name, then Address and finally Phone Number. After analyzing, write a code which can read the file, parse it etc. Hmm, lot of work, right.

That’s what the main difference is between a simple file and a database; database has an independent way (SQL) of accessing information while simple files do not (That answers my twisted question defined above). File meets the storing, managing and retrieving part of a database, but not the independent way of accessing data.
Note: - Many experienced programmers think that the main difference is that file cannot provide multi-user capabilities which a DBMS provides. But if you look at some old COBOL and C programs where files were the only means of storing data, you can see functionalities like locking, multi-user etc. provided very efficiently. So it’s a matter of debate. If some interviewers think of this as a main difference between files and database, accept it… going in to debate means probably losing a job.
(Just a note for fresher’s: Multi-user capabilities mean that at one moment of time more than one user should be able to add, update, view and delete data. All DBMS' provides this as in-built functionalities, but if you are storing information in files, it’s up to the application to write logic to achieve these functionalities).