28.3.08

Magic Tables in SQL Server

Magic Table in SQL Server:
Magic tables are used to put all the deleted and updated rows. We can retrieve the
column values from the deleted rows using the keyword "deleted"

To project the deleted data into the deleted table we will use "output" clause
Below is the sample code to retrieve the deleted data.

Code Snippet

DECLARE @DeletedTable TABLE(DeletedTableID INT, DeletedData VARCHAR(20))

DELETE VENKATOutput
OUTPUT Deleted.KeyID, Deleted.Name INTO @DeletedTable WHERE KeyID > 3
SELECT * FROM @DeletedTable

Similarly, we can retrieve the updated data and old data too using the keyword "Inserted"

Provide me your valuable feedback regarding this.
Regards,
Venkatesan Prabu . J

Retrieve the number of Stored procedures in the Database

To find the total number of stored procedures:
Sysobjects table will store all the system objects and their properties. We can identify the system objects using the column XTYPE.
Below is the query used to retrieve the total number of stored procedures available
in the database.
Code Snippet

Use databasename
Select Count(*) from Sysobjects where xtype='P'

The above query will fetch all system related and user related stored procedures in
a database. If we need only user related stored procedures please proceed with the below query.
Code Snippet

Select * from Sysobjects where xtype='P' AND ID>1

Similarly to retrieve the triggers in the database, we can use the below query.
Code Snippet

Select * from Sysobjects where xtype='TR'

Happy Learning!!!

Regards,

Venkatesan Prabu . J

27.3.08

Retrieving Database records with day difference

As a sql developer, i faced situations like finding all the records for that specified date irrespective of time in the datetime column.

Digging sql server concepts, i found datediff function to implement this functionality. Below is the code to retrieve the records for the specific date irrespective of time.
Code Snippet

CREATE TABLE [dbo].[DateTimeTable](
[id] [int] ,
[Date] [datetime] )

INSERT INTO [DateTimeTable] VALUES(1,'2/3/2008 1:00:00 AM')
INSERT INTO [DateTimeTable] VALUES(2,'2/3/2008 1:00:00 AM')
INSERT INTO [DateTimeTable] VALUES(3,'2/5/2008 1:00:00 AM')
INSERT INTO [DateTimeTable] VALUES(4,'2/4/2008 1:00:00 AM')
INSERT INTO [DateTimeTable] VALUES(5,'2/3/2008 6:00:00 AM')
INSERT INTO [DateTimeTable] VALUES(6,'2/3/2008 5:00:00 AM')
INSERT INTO [DateTimeTable] VALUES(6,GETDATE())
INSERT INTO [DateTimeTable] VALUES(7,GETDATE())
INSERT INTO [DateTimeTable] VALUES(8,GETDATE())
INSERT INTO [DateTimeTable] VALUES(9,GETDATE())

SELECT * FROM [DateTimeTable]

SELECT * FROM [DateTimeTable] WHERE
DATEDIFF(DAY,0,DATE) = DATEDIFF(DAY,0,GETDATE())

Happy Learning!!!

Regards,

Venkatesan Prabu . J

To list all the tables in the database

I got a query from dotnet spider user to list all the tables in a particular database. Wish to write some information related to this, in my blog.
We can achieve it by two methods either by using,
1. Information schema tables or
2. Sysobjects table.

Below is the sample code to acheive this,

Code Snippet

Use DatabaseName

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

Code Snippet

select * from sysobjects where xtype='u'

Regards,

Venkatesan Prabu . J

25.3.08

Error Handling in SQL Stored Procedure

SQL Server 2005 have introduced "Try catch" loop for catching exception.
The exception can be caught and an error message can be displayed similar to other programming languages. The message can contain the following:
  • Exception message
  • Exception Error Number
  • Error severity
  • Error state
  • Procedure which returned the error
  • Line number where the occured

Pre-defined functions are available to retrieve the above specified contents of the error. These functions can be used in catch block to retrieve values as follows:

Code Snippet

CREATE PROCEDURE SAMPLEPROC
BEGIN

BEGIN TRY
-- Statements
END TRY

BEGIN CATCH
DECLARE @ErrMsg NVARCHAR(MAX)
DECLARE @ErrProc NVARCHAR(MAX)

SELECT @ErrMsg = ERROR_MESSAGE(),
SELECT @ErrProc = ERROR_LINE()
END CATCH

END

Regards,
Venkatesan Prabu . J

18.3.08

Triggers inside Stored procedure

Got a query from users like, “Is it possible to use trigger inside stored procedure. If not why? if yes how?” Its quite interesting question,

1. My answer is “No” we can’t use trigger inside stored procedure because trigger is an object which is binded with table objects.
2. The inherent property of the trigger is to fire automatically if any condition breaks and its associated with DML operations. But we are trying to create and initiate it from the stored procedure.
3. It will loose its property as a System object.

a. My answer is “Yes” oops confusing. To provide an alternative way, Microsoft provides us a new concept named CLR integration in sql server 2005.
b. We can create Managed trigger and wrap it in the stored procedure.

Regards,
Venkatesan Prabu . J

RowNumber in sql server 2000

In sql server 2000, we don’t have an inbuilt option to retrieve the data based on the rownumber. In sql server 2005, Rownumber() is used to implement such functionality.

In sql server 2000, we have to insert into a new table with identity column and after wards we have retrieve the result set based on the rownumber we want.
In the below code, I am inserting the rows into the table variable “@OutputTable” based on the result set order by address.

Code Snippet

DECLARE @VenkatTable TABLE (ID int, NAME varchar(50), ADDRESS varchar(50))
INSERT INTO @VENKATTABLE VALUES (1, 'Hari', 'Chennai')
INSERT INTO @VENKATTABLE VALUES (2, 'Ram', 'Hyderabad')
INSERT INTO @VENKATTABLE VALUES (3, 'Raj', 'Bombay')
INSERT INTO @VENKATTABLE VALUES (52, 'Arun', 'Delhi')
INSERT INTO @VENKATTABLE VALUES (53, 'Santhi', 'Dharmapuri')

DECLARE @OutputTable TABLE
(
RowID int identity primary key,
Name varchar(50),
Address varchar(50)
)
DECLARE @RowID int

INSERT INTO @ OutputTable (Name, Address)
SELECT Name, Address from @VenkatTable Order by Address
SELECT @RowID = RowID FROM @ OutputTable
WHERE Name = 'Arun';
SELECT * FROM @ OutputTable WHERE RowID = @RowID



Happy learning……

Regards,
Venkatesan Prabu . J

RowNumber in Sql Server 2005

Wish to share some useful information related to data retrieval.
I got a recent query in dotnetspider site like, how to retrieve the data using rownumber concept. The user is not interested to fetch based on the data stored in the table but he wants to fetch based on the result set.
Problem scenario:
Irrespective of order by clause on any column, the user needs to retrieve the 50th row values.
Solution:
We have to map the row number for the entire result set and identify the 50th row values.

In the below code snippet, I’ve tried to create common table expression with rownumber() function[New in sql server 2005] order by address column. From the CTE, I’ve fetched the rownumber for the person "Arun" and display appropriate information for that particular row number.

Code Snippet

DECLARE @VenkatTable TABLE (ID int, NAME varchar(50), ADDRESS varchar(50))
INSERT INTO @VENKATTABLE VALUES (1, 'Hari', 'Chennai')
INSERT INTO @VENKATTABLE VALUES (2, 'Ram', 'Hyderabad')
INSERT INTO @VENKATTABLE VALUES (3, 'Raj', 'Bombay')
INSERT INTO @VENKATTABLE VALUES (52, 'Arun', 'Delhi')
INSERT INTO @VENKATTABLE VALUES (53, 'Santhi', 'Dharmapuri')

select * from @VenkatTable

DECLARE @RowID int
;WITH VenkatTableCTE AS
(SELECT ROW_NUMBER() OVER(ORDER BY Address) AS RowID, Name, Address
FROM @VenkatTable)
SELECT @RowID = RowID FROM VenkatTableCTE
WHERE Name = 'Arun';

SELECT * from VenkatTableCTE WHERE RowID = @RowID


Provide me your valuable feedback regarding this.
Regards,
Venkatesan Prabu . J

13.3.08

Deadlocks in SQL Server

Deadlock occurs if multiple users tried to access the resources holding some resources with them to finish the task. Dead lock can be avoided by using Lock methods in sql server.Locks can be classified in various types like

1. Rowlevel locking,
2. Page level locking,
3. Table level locking.

Row level locking: A specific row in the table will be locked by the user and meantime no one can access those rows.
Page level locking: In this case, we used to lock a particular page which hold 8 KB of data.
Table level lock: Here, entire table will be locked.

Conceptually there are various types of locks like,

Shared lock : The lock is shared by the users. One person can update the data meantime other persons can select the data.
Exclusive lock: No one can access the data until the concerned person releases the lock.

Regards,
Venkatesan Prabu. J

SQL Server MSN group

My sql server MSN group,

http://groups.msn.com/SqlMSUG/messages.msnw

Please Join this group to share your knowledge with us.

Regards,
Venkatesan Prabu. J
Moderator

My articles in MSDN

As a part of msdn community, i used to post articles and feedbacks on MSDN content.

http://msdn2.microsoft.com/en-us/user-venkatesan%20prabu.aspx

New In SQL Server 2008 - File Stream Datatype

SQL server 2008 formally called Katmai aims at Data management, Self orgnaizing and self maintaining capabilities. Microsoft aims at high availability (towards zero downtime availability). It supports for structured and unstructured data. Structured data is nothing but your table or any other formatted one where as unstructured data includes file streams and video images.

SQL Server 2008 allows us to store XML, emails, files and spatial information. FileStream is a new datatype introduced in Katmai, its used to stored the data in the file system.

Details about the file like file type, file size, file name will be considered as structured data and its stored in the sql server database whereas the contents of the file is considered as an unstructured data and gets stored in the file system. The contents can be accessed either by Win32 API or by using T-SQL statements.

Steps to be followed in implementing File Stream Data type:
1. First, we have to configure the file stream property.

Belows is the syntax to configure the file stream property,

Code Snippet
sp_filestream_configure
[ [ @enable_level = ] level ] [ [ , @share_name = ] 'share_name' ]


The enable level should have a value from 0 to 3 indicating the authorization level like whether T-SQL access is allowed or not, Win32 API is required or not etc..,
2. Once executing the above command just check it out in the command prompt using the command NET SHARE.
3. The primary requirement to implement file stream property is to create the database with atleast one filegroup contains filestream property.
4. Create the table with column type as filestream. Unique constraint will be enforced on the table which has filestream type.
5. Insert the data into file stream datatype column and it gets stored in the file system.
Please provide me your valuable feedback. I will post the exact code in my next postings.

Regards,
Venkatesan Prabu .J

11.3.08

Primary Key Vs Unique Key

Unique Key constraints:
Unique key constraint will provide you a constraint like the column values should retain uniqueness. It will allow null value in the column. It will create non-clustered index by default Any number of unique constraints can be added to a table.


CREATE TABLE EMPLOYEETABLE (EMPID INT , IDVAL INT NOT NULL, FIRSTNAME VARCHAR(30) , LASTNAME VARCHAR(30) , CITY VARCHAR(30), JOININGDATE DATETIME)

I have created a table EmployeeTable and i am trying add a unique constriant on the column IDVAL.


ALTER TABLE EMPLOYEETABLE ADD CONSTRAINT UNIQUE_CONSTRAINT UNIQUE (IDVAL)

Primary Key: Primary key will create column data uniqueness in the table. Primary key will create clustered index by default Only one Primay key can be created for a table Multiple columns can be consolidated to form a single primary key It wont allow null values.


ALTER TABLE EMPLOYEETABLE ADD CONSTRAINT KEY_CONSTRAINT PRIMARY KEY (IDVAL)

Regards,

Venkatesan Prabu . J

sp_spaceused in sql server

Its a system stored procedure to check the free space in the database.
It provides details about the db size, free space, index_size, unused index_size, data size and reserved memory capacity.
Code Snippet

use master
sp_spaceused

The above code snippet will provide you two result sets
1. General information like dbname,dbsize and unallocated space
2. Second result set will provide you info about index related information and reserved data.

Below query will provide the above information as a single result set.
Code Snippet

sp_spaceused 'DatabaseName'


Regards,
Venkatesan Prabu. J

9.3.08

Merging relative rows in sql server

Suppose i want to merge two tables based on certain relativeness we can opt self join operation.
Self join operation is nothing but the usage of same table in different instances.

Actual Table

TextData        ObjectID        SPID        StartTime          EndTime
----------------------------------------------------------------------------------------------------
Select a       123       50       2007-03-06 11:19:51.250          NULL

Select a       123       50       NULL           2007-03-06 11:19:51.250
Now i want to merge these two rows as a single one. Below is the required output.
Resultant Table

TextData        ObjectID        SPID        StartTime          EndTime
----------------------------------------------------------------------------------------------------
Select a       123       50       2007-03-06 11:19:51.250          2007-03-06 11:19:51.250
Query to achieve the desired output is,
Code Snippet

DECLARE @VenkatTable TABLE(TextData nVARCHAR(10), ObjectID INT, SpID INT, StartTime DATETIME, Endtime DATETIME)

INSERT INTO @VenkatTable (TextData, ObjectID, SpID, StartTime, EndTime)

SELECT 'Select a', 123, 50,'2008-03-06 11:19:51.250',NULL UNION ALL

SELECT 'Select a', 123, 50, NULL,'2008-03-06 11:19:51.250'

select * from @VenkatTable

select a.TextData,a.ObjectID, a.SpID, a.StartTime, b.EndTime
from @VenkatTable a inner join @VenkatTable b on a.TextData = b.TextData and a.ObjectID = b.ObjectID
and a.SpID = b.SpID where a.StartTime is not null and b.EndTime is not null

8.3.08

New SequentialID in sql server

NewSequentialId() is a new function introduced in sql server 2005. It works similar to Newid() but produces consecutive id's.

Unique identifier generated were rely on time and not on space so it will take another millions of years to generate a duplicate id.
Code Snippet

DECLARE @VenkatTable table
( RowID int IDENTITY,
VenkatData varchar(20),
VenkatGUID uniqueidentifier DEFAULT newsequentialid() )


INSERT INTO @VenkatTable( VenkatData ) VALUES ('Venkatesan Data' )

SELECT * FROM @VenkatTable

Updating table in different database in sql server

Problem Statement:
I want to update DatabaseA.TableA.Col1 with reference to Database2.TableB.Col2
with condition TableA.ColA = TableB.colB

Code Snippet

Update A set A.Col1 = B.Col2
from DatabaseA.dbo.TableA inner join Database2.dbo.TableB
on A.ColA = B.ColB

Unique Identifier in sql server

Unique identifier is used to create an unique 32 bit column value in the table. NewID() is a system function used to generate unique identifier value. It will take millions of years to generate a duplicate Unique identifier value
I am using NewID() to insert the data,
Code Snippet

DECLARE @VenkatTableVar table
( RowGUID uniqueidentifier PRIMARY KEY,VenkatName sysname,Name varchar(20))


INSERT INTO @VenkatTableVar1
VALUES (newid(),'Venkat','VenkatName')


SELECT * FROM @VenkatTableVar

Tried with manual data into the unique identifier column,
Code Snippet

DECLARE @VenkatTableVar table
( RowGUID uniqueidentifier PRIMARY KEY,VenkatName sysname,Name varchar(20))


INSERT INTO @VenkatTableVar
VALUES ( cast( 'A8FB1E48-F129-44RC-82AE-001EAD5A8ADE' AS binary ), 'Venkat','VenkatName')


SELECT * FROM @VenkatTableVar

Collation in sql server

Its used to define and instruct compilers to recognize the query keywords.
Code Snippet

Create database VenkatDatabase collate Latin1_General_CS_AS_KS_WS

In the above code, i am trying to create a database with supporting language as latin.
It should support,
CS - Case sensitiveness
AS - Accent sensitiveness
KS - Kana sensitiveness
WS - Width sensitiveness
We can specify the insensitiveness using the CI,AI,DI,WI
Ex:
CI - Case insensitiveness

Replication types in sql server

Three types of replication available,
1. Snapshot replication : You have to take entire database at a stretch and update it in the subscription database
2. Transactional replication : Replication can be done for each transaction
3. Merge replication: Updation can be done on both sides and happened if the databases are connected.
Other way of segregating the replication technology is,
1. Push subscription - The publisher will initiate the process to push the data to subscriber
2. Pull subscription - The subscriber will request for the data from the publisher.

Table partitioning in sql server

Table partitioning is the concept introduced in sql server 2005. Its used to enhance faster database accessibility. It may be either Horizontal partitioning or Vertical partitioning.
Horizontal Partitioning:
In this type, the rows will be segregated as two tables (Old table and new table). Its a logical seperation placed in different file groups.
If i want to access most recent data frequently when compared to old data then horizontal partitioning paves the way for this. It's highly advisable to implement this concept in multi processor environment.
Schema remains the same in horizontal partitioning.
Vertical partitioning:
Its nothing but segregating the columns of the table, part of the column in one table and other part of the column in other table(in case of partitioning into two tables).

Truncate Vs Delete Statement

After deleting all the rows in the table, both will retain the table structure
* Truncate will delete all rows at a stretch where as delete statement will delete individual rows at a time.
* Truncate is comparatively faster than delete statement.
* Truncate is not a logged activity where as delete statement will create a log record in the LDF file.
* Truncate statement will remove the reference where as delete statement will delete the data itself.
* We cannot provide where condition in truncate statement where as its possible in delete statement.

My T-SQL works

Hi Friends,
I got a peculiar problem from MSDN users and try to resolve the same.
OOPS its takes more than 15 minutes to arrive the solution. I came with three solutions but two solutions were previously given by others. This is my solution for the problem.

Problem Faced

GID Value
------------------------------------
1 True
1 True
2 False
2 True
3 True

If any GID with false should return the result set as False. GID with only true should return "True"

Code Snippet

DECLARE @t TABLE (GID int, Value varchar(10))

INSERT @t (GID, Value) VALUES (1, 'True')
INSERT @t (GID, Value) VALUES (1, 'True')
INSERT @t (GID, Value) VALUES (1, 'True')
INSERT @t (GID, Value) VALUES (2, 'False')
INSERT @t (GID, Value) VALUES (2, 'True')
INSERT @t (GID, Value) VALUES (3, 'True')
INSERT @t (GID, Value) VALUES (4, 'False')
INSERT @t (GID, Value) VALUES (5, 'True')

(select distinct t1.GID,t1.Value from @t as t1 inner join @t as t2 on t1.GID=t2.GID
where t1.gid not in (select t3.gid from @t as t3 where t3.value ='false'))
union
(select distinct t3.GID,t3.Value from @t as t3 where t3.value ='false')
select * from @t

Listing the users in sql server

sp_who is used to list all the users connected to sql server
It will list out the current process id, login user name, process through which the user gets connected, connected time, duration, Details database accessed by the user, login user role.

Difference between sp_who and sp_who2:
Both will perform the same operation, sp_who2 will give additional performance related information like
CPU time
Disk I/O
SPID


There is an alternative way to retrieve the result set. We can achieve it by accessing the system tables too,
select* from master.dbo.syslogins
Regards,
Venkatesan Prabu . J

To find Index of white spaces in SQL Server

Charindex is used to check a particular character index in a word. I tried to pick the index of the white spaces and insert those details in a temporary table. Below is the code,
Code Snippet

declare @qry varchar(100), @StartLoc int

select @StartLoc = 1

select @qry = '1 df546 156 008 10/00 1203301985
BEY 112354 PaaaP 6785.00 012300-0110 011011011080 B O '

create table #VenkatTable(val int)
while @StartLoc <= len(@Qry)
begin set @StartLoc = charindex(' ',@qry,@StartLoc)
insert into #VenkatTable values (@StartLoc)
set @StartLoc = @StartLoc+1

end

select * from #VenkatTable

drop table #VenkatTable

Regards,
Venkatesan Prabu .J

To retrieve the query execution time

CURRENT_TIMESTAMP is used to retrieve the current time. By using this system function we can compare the time before and after the query execution.
Code Snippet

declare @qry varchar(100), @StartLoc int
DECLARE @StartTime datetime,
@EndTime datetime
SET @StartTime = CURRENT_TIMESTAMP;
-- **********QUERY TO BE CHECKED *************
SELECT *
FROM [Venkat]

-- *****************************************
SET @EndTime = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @StartTime, @EndTime)
GO

3.3.08

Row_Number in sql server 2005

ROW_NUMBER function is to find the row number for the column which is specified in Order by clause.Its considered as one of the ranking function.
Code Snippet
declare @qry varchar(100), @StartLoc int

create table venkitable(id int, names varchar(30),RELATIVENESS INT)

insert into venkitable values(101,'dfVenkat',1)
insert into venkitable values(23,'sdfVenkat2',1)
insert into venkitable values(323,'sdfVenkat3',1)
insert into venkitable values(4111,'sdfVenkat4',1)
insert into venkitable values(5345,'rtVenkat5',1)
-- ROW_NUMBER FUNCTION IS TO FIND THE ROW NUMBER OF A PARTICULAR-- COLUMN IF ITS SPECIFIED IN ORDER BY CLAUSE

SELECT ROW_NUMBER () OVER (ORDER BY names) AS venkatid, names ,id FROM venkitable

-- SHOULD DISPLAY AN ERROR INDICATING THAT ROW_NUMBER MUST HAVE AN ORDER BY CALUSE

SELECT ROW_NUMBER () OVER() AS venkatid, names ,id FROM venkitable

-- WILL INDICATE AN ERROR BECAUSE THE SUBQUERY WILL GIVE MORE THAN ONE VALUES

SELECT ROW_NUMBER () OVER (ORDER BY (SELECT ID FROM venkitable)) AS ID, NAMES FROM venkitable

-- THIS ONE WILL WORK EXACTLY BUT REFRAIN THE SORT OPERATION-- SUPPRESS THE ROW_NUMBER() FUNCTIONALITY

SELECT ROW_NUMBER () OVER (ORDER BY (SELECT ID FROM venkitable WHERE ID=1)) AS NAMES, NAMES FROM venkitable

My T-SQL Gallery @code.msdn.microsoft


Created my own T-SQL Gallery in Microsoft site. Do visit the same and share your feedback,

http://code.msdn.microsoft.com/VenkatSQLSample/Thread/List.aspx

Thanks and Regards,
Venkatesan Prabu .J

SQL Server Interview questions - Part 1

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.


The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables


What is DTS in SQL Server ?
Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.


What is the difference between SQL and Pl/Sql ?

Straight forward. SQL is a single statement to finish up our work.Considering, I need some data from a particular table. “Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Procedural Logic / SQL.

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

Select Statement in SQL Server

Select Statement in SQL Server

String Functions in sql server

String Functions in sql server
Substring/Len/replace/Ltrim/Rtrim

SQL Server Interview Question - Part 2

What is normalization?

Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.


What is a stored procedure:
Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.


What is the difference between UNION ALL Statement and UNION ?
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Example for Stored Procedure?
They are three kinds of stored procedures,1.System stored procedure – Start with sp_2. User defined stored procedure – SP created by the user.3. Extended stored procedure – SP used to invoke a process in the external systems.Example for system stored proceduresp_helpdb - Database and its propertiessp_who2 – Gives details about the current user connected to your system. sp_renamedb – Enable you to rename your database


What is a trigger?

Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.


What is a 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 specfic 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.


What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.


What are the types of indexes available with SQL Server?

There are basically two types of indexes that we use with the SQL ServerClustered -

1. It will format the entire table, inturn physically sort the table.

2. Only one clustered index can be created for a table.

3. Data will be located in the leaf level.

4. By default, primary key will create clustered index on the table.

Non-Clustered Index

1. It wont touch the structure of the table.

2. It forms an index table as reference to the exact data.

3. A reference to the data will be located in the leaf level.

4. For a table, we can create 249 non clustered index.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Interview question

Extent Vs Page?

Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.The size of the page is 8KB.

Eight consecutive pages will form an extent 8 * 8KB = 64KB.

Thus I/O level operation will be happening at pages level.The pages will hold a template information at the start of each page (header of the page).

They are,

1. page number,

2. page type,

3. the amount of free space on the page,

4. the allocation unit ID of the object that owns the page.

Extents will be classifed into two types,

1. Uniform extents

2. Mixed extents

Uniform Extents:It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.Mixed

Extents:Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Property of SQL Server :Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.

Herecomes, our fragmentation and reindexing concepts.



Best Joke - Enjoy it

Best Joke - Enjoy it