What is PRIMARY KEY Constraint in SQL Server | What is UNIQUE KEY Constraint | What is FOREIGN KEY Constraint | What is CHECK Constraint | What is NOT NULL Constraint in SQL Server ?

What is PRIMARY KEY Constraint in SQL Server?

Primary key constraint is used to uniquely identify each record in database table. It won’t allow repetition or duplication of data. Each table is having only one primary key constraint and it contains only unique values. Primary key constraint doesn’t accept null values (Read more Here)

 

What is UNIQUE KEY Constraint in SQL Server?

Unique key constraint is same as Primary key Constraint it doesn’t allow duplication or repetition of data in column and we can uniquely identify records in table. The main difference is Primary Key constraint won’t allow null values but unique key constraint allows null values. We have a chance to define only one primary key on table but we can define many unique key constraints on table (Read more Here)

What is FOREIGN KEY Constraint in SQL Server?

A Foreign key in one table point to primary key in another table. The foreign key constraint is used to prevent the actions that would destroy the links between two tables (Read more Here)

What is CHECK Constraint in SQL Server? 

The check constraint is used to limit the value range that can be placed in a column. If we set Check constraint property on particular column the values in particular column must satisfy condition set by check constraint (Read more Here)

What is NOT NULL Constraint in SQL Server? 

If we set Not Null constraint property on any column in table that column won’t accept NULL or Empty values. If you want enforce any column not to accept NULL or empty value just set Not NULL Constraint property for that particular column (Read more Here)

http://pramadha.com/

What is the Difference between a HAVING clause and a WHERE clause in SQL Server | What is Connection Pooling and why it is used | What are the Authentication Modes in SQL Server | What is the difference between UNION and UNION ALL | What is Identity in SQL Server

What is the Difference between a HAVING clause and a WHERE clause in SQL Server? 

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

 

What is Connection Pooling and why it is used?

To minimize the cost of opening and closing connections, ADO.NET uses an optimization technique called connection pooling.

The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

What are the Authentication Modes in SQL Server?

Windows mode and Mixed Mode – SQL & Windows.

What is the difference between UNION and UNION ALL in SQL Server? 

The Union operator is used to return the result set of two or more select statements. It will return only distinct values from the tables (Read more Here).

Union ALL operator is used to return the result set of two or more select statements including duplication values (Read more Here)

What is Identity in SQL Server?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed

 

http://pramadha.com/

What is an Index in SQL Server | What is the Difference between Clustered and a Non-clustered Index in SQL Server | What is Collation | What are Primary Keys and Foreign Keys in SQL Server | What’s the Difference between a Primary Key and a Unique Key in SQL Server

What is an Index in SQL Server?

An index is a physical structure containing pointers to the data. Indexes are created in an existing table to locate the data more efficiently and quickly (Read more Here)

 

What is the Difference between Clustered and a Non-clustered Index in SQL Server?

The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db. (Read more Here)

What is Collation?

Collation refers to a set of rules that determine how the data is sorted and compared (Read more Here)

What are Primary Keys and Foreign Keys in SQL Server?

Primary key is used to identify records in table uniquely and it won’t allow null values and duplication of data. Only one primary key is allowed for each table ()

Foreign key in one table points to primary key in another table. Foreign key is used to prevent the actions that would destroy the link between two tables. 

What’s the Difference between a Primary Key and a Unique Key? 

Primary key is used to identify records in table uniquely and it won’t allow null values and duplication of data. Only one primary key is allowed for each table.

Unique key is same as primary key only the difference is unique key allow null values.

 

http://pramadha.com/

What are the Different Types of Triggers | What is a View | What is the Difference between a Function and a Stored Procedure | What are the Advantages of Using Stored Procedures in SQL Server

What are the Different Types of Triggers?

After triggers and Instead of triggers 

After triggers are the triggers which are raised before the transaction committed or postback.

Instead of triggers are used to update the database tables which are associated with view instead of updating the view directly.

 

What is a View?

A view is virtual table based on result set of sql statement which contains rows and columns just like real table. The fields in view are fields from real tables in the databases.

Ex: Create VIEW [view_name] as select Column_Name from table_name where condtion

A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in underlying tables.

A view also serves as mechanism to simply query execution. Complex queries can be stored in the form as a view and data from the view can be extracted using simple queries 

What is the Difference between a Function and a Stored Procedure? 

Stored procedure returns one or more values where as function can return one value that is mandatory

Stored procedure can have input and output parameters where as function is having only input parameters.

Functions can be called from procedures but procedures cannot be called from functions

Exceptions can be handled by try-catch block in procedures whereas try-catch block cannot be used in function. (Read more Here)

What are the Advantages of Using Stored Procedures?

1) Stored Procedures reduce the network traffic and increase the performance 

2) Stored Procedures allows faster execution

3) Stored Procedures allows modular programming

4) Stored Procedures provides better security to our data

 

http://pramadha.com/

What is Normalization | What is De Normalization | What is a Stored Procedure | What is a Trigger in SQL Server

What is Normalization? 

In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.There are three main normal forms, each with increasing levels of normalization:

 

• First Normal Form (1NF): Each field in a table contains different information. For example, in an employee list, each table would contain only one birthdate field.
• Second Normal Form (2NF): Each field in a table that is not a determiner of the contents of another field must itself be a function of the other fields in the table.
• Third Normal Form (3NF): No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflecting in all tables that link to the birthdate table.

There are additional normalization levels, such as Boyce Codd Normal Form (BCNF), fourth normal form (4NF) and fifth normal form (5NF). While normalization makes databases more efficient to maintain, they can also make them more complex because data is separated into so many different tables.

(2) In data processing, a process applied to all data in a set that produces a specific statistical property. For example, each expenditure for a month can be divided by the total of all expenditures to produce a percentage.

(3) In programming, changing the format of a floating-point number so the left-most digit in the mantissa is not a zero.

What is De-normalization?             

As the name indicates, de-normalization is the reverse process of normalization. It’s the controlled introduction of redundancy in to the database design.
It helps improve the query performance as the number of joins could be reduced.

What is a Stored Procedure?

Stored Procedure is a group of sql statements that has been created once and stored in server database. Stored procedures will accept input parameters so that single stored procedure can be used over network by multiple clients using different input data. Stored procedures will reduce network traffic and increase the performance. (Read more Here)

What is a Trigger?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder

Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, and DELETE) happens on the table on which they are defined.

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also.

 

http://pramadha.com/

What is the difference between char, varchar, nvarchar in sql server?

Char DataType

Char datatype which is used to store fixed length of characters. Suppose if we declared char(50) it will allocates memory for 50 characters. Once we declare char(50) and insert only 10 characters of word then only 10 characters of memory will be used and other 40 characters of memory will be wasted.

 

varchar DataType

Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. Suppose if we declared varchar(50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar(50) and insert only 10 characters of word it will allocate memory for only 10 characters.

nvarchar DataType

nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

So if we are not using other languages then it’s better to use varchar datatype instead of nvarchar

How to handle exceptions in SQL Server?

To handle exceptions in SQL Server we can use TRY…… CATCH blocks. To use TRY…… CATCH blocks in stored procedure we need to write the query like as shown below

 

BEGIN TRY —Write Your Code END TRY BEGIN CATCH —Write Code to handle errors END CATCH

In TRY block we will write our queries and in CATCH block we will write code to handle exceptions. In our SQL statements if any error occurs automatically it will move to CATCH block in that we can handle error messages. To handle error messages we have defined Error Functions in CATCH block those are

ERROR_LINE() – This function will return error line number of SQL query which cause to raise error.

ERROR_NUMBER() – This function will return error number which is unique and assigned to it.

ERROR_SEVERITY() – This function will return severity of error which indicates how serious the error is. The values are between 1 and 25.

ERROR_STATE() – This function will return state number of error message which cause to raise error.

ERROR_PROCEDURE() – This function will return name of the procedure where an error occurred.

ERROR_MESSAGE() – This function will return the complete text of the error message which cause to raise error.

Check below sample query to handle errors in stored procedure

BEGIN TRY SELECT 300/0 END TRY BEGIN CATCH SELECT ErrorNumber = ERROR_NUMBER(), ErrorSeverity = ERROR_SEVERITY(), ErrorState =ERROR_STATE(), ErrorProcedure = ERROR_PROCEDURE(), ErrorLine = ERROR_LINE(), ErrorMessage =ERROR_MESSAGE() END CATCH

What is the difference between view and stored procedure in sql server?

View in SQL Server

A view represents a virtual table. By using view we can join multiple tables and present the data as coming from a single table.

For example consider we have two tables

      1)    UserInformation table with columns userid, username

      2)    SalaryInformation table with columns salid, userid, salary

Create VIEW by joining above two tables

CREATE VIEW VW_UserInfo AS BEGIN SELECT a.userid,a.username,b.salary from UserInformation a INNER JOIN SalaryInformation b ONa.userid=b.userid END

By using above view we can get username or salary based on userid for that we need to create procedure like as shown below

CREATE PROCEDURE GetUserInfo @uid INT AS BEGIN SELECT username from VW_UserInfo WHERE userid=@uid END

If you observe above procedure it’s like getting username from single table (VW_UserInfo) by passing userid

Stored Procedure

A stored procedure is a group of sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure

Sample of creating Stored Procedure

USE AdventureWorks2008R2; GO CREATE PROCEDURE dbo.sp_who AS     SELECT FirstName, LastName FROM Person.Person; GO EXEC sp_who; EXEC dbo.sp_who; GO DROP PROCEDURE dbo.sp_who; GO

http://pramadha.com/

What is the difference between stored procedure and function in sql server?

Stored Procedre:

Stored Procedure is a group of sql statements that has been created once and stored in server database. It’s pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. Stored procedures will accept input parameters so that single stored procedure can be used over network by multiple clients using different input data. Stored procedures will reduce network traffic and increase the performance. (Read more Here)

Function:

Function is not pre-compiled object it will execute every time whenever it was called.

Difference between Stored Procedure and Function

1) Procedure can return zero or n values whereas function can return one value which is mandatory (Read more Here).

2) Procedures can have input, output parameters for it whereas functions can have only input parameters.

3) Procedure allows select as well as DML(INSERT/UPDATE/DELETE) statements in it whereas function allows only select statement in it.

4) Functions can be called from procedure whereas procedures cannot be called from function.

5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6) We can go for transaction management in procedure whereas we can’t go in function.

7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

What is function in sql server and uses of it?

Generally functions are used to reduce redundancy of queries. I have two tables QuestionDetails andUserDetails will be like as shown below

QuestionDetails

QuestionId	Subject	Description	CreatedBy
1	Test	simple desc	1
2	Welcome to asp	check forums site	1
3	I have a problem	ssms not working	2

UserDetails

Userid	Username	Location	Designation
1	Sureshdasari	Chennai	SSE
2	Rohinidasari	Chennai	agbsc

Now from QuestionDetails table we need to get question details with createdBy name. To getcreatedby name we can join these two tables but to improve query performance we need to avoid using joins in sql server. To get username instead of using joins we can create function like as shown below

CREATE FUNCTION fn_getusername (@userid int) RETURNS VARCHAR(64) AS BEGIN DECLARE @name VARCHAR(64) SELECT @name=UserName FROM UserDetails WHERE UserId=@userid RETURN @name END

Once we written this function now we need to write the query like as shown below to get question details with username

Select QuestionId, Subject, Description, username=dbo.fn_getusername(CreatedBy) fromQuestionDetails

http://pramadha.com/