SQL SERVER Questions And Answers -1

Q 1.How many maximum column can be in a table in sql server 2008?
Ans: Max row size is 8,060 bytes so its depends on column type in a table.

Q 2. How many maximum foreign key table reference per table?
Ans : 253

Q.3 How many maximum nested sub quires/store procedure /triggers ?
Ans: 32

Q.4 How many maximum parameters in user function / store procedure?
Ans : 2100

Q.5 How many maximum number of columns in per SELECT/INSERT/UPDATE statement?
Ans : 4096

Q.6 How can get all the table names with additional information?
SELECT * FROM sys.tables
OR
SELECT * FROM INFORMATION_SCHEMA.TABLES

Q.7 How can get all table columns with additional information
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Q.8 How can get all View information in database
SELECT * FROM INFORMATION_SCHEMA.VIEWS

Q.9 How can set trigger fire sequence on table?
Exec sp_settriggerorder @triggername=’trg_name’, @order=’FIRST|LAST|NONE’,
@stmttype=’INSERT|UPDATE|DELETE|CREATE_INDEX,ALTER_INDEX’,
@namespace=’DATABASE|SERVER|NULL’

Q.10 How can create comma seprated list of names in a table?
DECLARE @name varchar(max)
SELECT @name = COALESCE(@name+’,’,”)+name FROM Employee
PRINT @name

Advertisements

Using the DATE and TIME data type in SQL Server 2008

In SQL Server 2008 we get two new DATE and TIME  Data Type.

1. DATE  Data Type

  • Its range from  0001-01-01 to 9999-12-31.
  • Its default format YYYY-MM-DD
  • Its storage size 3 bytes compare to DateTime 8 bytes.
  • Its allow to store date without Time.

Example


DECLARE @startDate date = '2012-09-14'/* Declartion and Initilization variable*/
DECLARE @endDate date = GetDate()/*Assign current date to new declare variable*/
SET @endDate = DATEADD(DAY,6,@endDate)/*Added 6 days to date variable*/
SELECT DATEDIFF(Day,@startDate,@endDate) As [No Of Days]/*Get No of days difference*/

OUTPUT:

2. TIME  Data Type

  • Its range from 00:00:00.0000000 to 23:59:59.9999999
  • Its default format hh:mm:ss[.nnnnnnn]
  • Its storage size 3 to 5 bytes means TIME(0) = 3 Bytes and TIME(7) = 5 Bytes.
  • Its use to store time without date.
  • We can define from zero to seven places to the right of the decimal.

Example


DECLARE @startTime time = '02:24:56.180'/* Declartion and Initilization  variable*/
DECLARE @endTime time(1) = '02:24:56.1'
SET @endTime = DATEADD(HOUR,4,@endTime)/*Added 4 hours to time variable*/
SELECT DATEDIFF(HOUR,@startTime,@endTime) As [No Of Hours]/*Get No of hours difference*/

OUTPUT

Trigger in Sql Server

Triggers are executed by the database when specific types of data manipulation commands are performed on specific tables. Such commands may include inserts, updates and deletes. Updates of specific columns may also be used a triggering events.

A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level.

Illustration with an example

There are two database tables one  for stock of items that contain stock of items and another for sale of items. Both have relationship.

(A) Table Stock (tbl_stock) :

Column Name Data Type Constraint/Description
stock_id int Identity(1,1) primary key
Item_id int Foreign key
Stock int Total stock of items

(B) Table Sale(tbl_sale) :

Column Name Data Type Constraint/Description
Sale_id int Identity(1,1) primary key
stock_id int Foreign key
sale int Total sale of items

(1)INSERT : Trigger on table tbl_sale when new sale is enter in table tbl_sale then update existing stock in table tbl_stock

create trigger trginsert
on tbl_sale
after insert
as
declare @sale int
declare @id int
select @sale=sale,@id=stock_id from inserted
update tbl_stock set stock=stock-@sale where stock_id=@id

(2)DELETE: Trigger on table tbl_sale when existing sale is delete then update existing stock of table tbl_stock

create trigger trgdelete
on tbl_sale
after delete
as
declare @sale int
declare @id int
select @sale=sale,@id=stock_id from deleted
update tbl_stock set stock=stock+@sale where stock_id=@id

(3)UPDATE:Trigger on table tbl_sale when existing sale is update in table tbl_sale then update existing stock in table tbl_stock

create trigger trgupdate
on tbl_sale
after update
as
declare @sale int
declare @saleold int
declare @id int
select @saleold=sale from deleted
select @sale=sale,@id=stock_id from inserted
update tbl_stock set stock=(stock+@saleold)-@sale where stock_id=@id

COMMIT and ROLLBACK in Sql Server

SQL provides many transaction management features. SQL commands COMMIT, ROLLBACK and SAVEPOINT helps in managing the transaction.

The COMMIT command is the transactional command used to save changes made by a transaction to the database.The COMMIT command will save all changes to the database since the last COMMIT or ROLLBACK command. Frequent commits in the case of transaction involving large amount of data is recommended. But too many commits can affect performance. In many implementations, an implicit commit of all the changes is done, if user logs off from the database.

The ROLLBACK command is the transactional control command to undo the transactions that have not already been committed to the database. The ROLLBACK command can be issued to undo the changes since the last COMMIT or ROLLBACK.

Illustration with an Example

There are two relational tables one for user registration information (tbl_userinfo) and another for user login information(tbl_login). Both tables have primary key foreign key relationship.

(A).User registration Information(tbl_userinfo)

Column Name Data Type Constraint/Description
user_id Int Identity(1,1) Primary Key
name Varchar(50) Name of User
email Varchar(50) Email of User

(B).user login information(tbl_login)

Column Name Data Type Constraint/Description
login_id Int Identity(1,1) Primary Key
user_id Int Foreign Key with tbl_userinfo
user_name Varchar(50) User Name for login
password Varchar(50) User Password for login

(A)General Transaction: In this transaction userreg , one table tbl_userinfo is affected and one row is entered and another table tbl_login is not affected. Here first insert query is successful executed but second insert query is not successful executed due to table tbl_login where column login_id is primary key and auto increment so we can’t pass value
and CATCH block will be executed.

begin try
begin tran userreg
declare @userid int
insert into tbl_userinfo(name, email)
values(‘sandeep’,’sandeep.shekhawat88@gmail.com’)
select @userid=max(user_id) from tbl_userinfo
insert into tbl_login(login_id, user_id, user_name, password)
values(2,@userid,’singh’,’singh’)
end try

begin catch
print ‘ónly one table entery’
end catch

(B)Implementation of ROLLBACK command: In this transaction userreg , one table tbl_userinfo is affected and one row is entered and another table tbl_login is not affected. Here first insert query is successful executed but second insert query is not successful executed due to table tbl_login where column login_id is primary key and auto increment so we can’t pass value. So CATCH block is executed here and ROLLBACK Command executed. After ROLLBACK Command execution our database change are undo means here tbl_userinfo row entry remove and both tables have no row .

begin try
begin tran userreg
declare @userid int
insert into tbl_userinfo(name, email)
values(‘sandeep’,’sandeep.shekhawat88@gmail.com’)
select @userid=max(user_id) from tbl_userinfo

insert into tbl_login(login_id, user_id, user_name, password)
values(2,@userid,’singh’,’singh’)
end try

begin catch
rollback tran userreg
end catch

(C)Implementation of COMMIT command: In this transaction userreg, both Insert queries successful executed. After successfully execution of queries, transaction userreg is committed and permanently save database changes.

begin try
begin tran userreg
declare @userid int
insert into tbl_userinfo(name, email)
values(‘sandeep’,’sandeep.shekhawat88@gmail.com’)
select @userid=max(user_id) from tbl_userinfo

insert into tbl_login( user_id, user_name, password)
values(@userid,’singh’,’singh’)
commit tran userreg
end try

begin catch
rollback tran userreg
end catch