SQL recovery model

First we see the recovery model of database .SQL server 2005 have three recovery model

1. Full

2. Bulk-logged

3. Simple

Full—This is what it says; everything is logged. Under this model, you should have no data

loss in the event of system failure assuming you had a backup of the data available and have all

transaction logs since that backup. If you are missing a log or have one that is damaged, then

you’ll be able to recover all data up through the last intact log you have available. Keep in mind,

However, that as keeping everything suggests, this can take up a fair amount of space in a system

that receives a lot of changes or new data.

Bulk-Logged—This is like “full recovery light.” Under this option, regular transactions are

logged just as they are with the full recovery method, but bulk operations are not. The result is

that, in the event of system failure, a restored backup will contain any changes to data pages

that did not participate in bulk operations (bulk import of data or index creation for example),

But any bulk operations must be redone. The good news on this one is that bulk operations perform

much better. This performance comes with risk attached, so your mileage may vary.

Simple—Under this model, the transaction log essentially exists merely to support transactions

as they happen. The transaction log is regularly truncated, with any completed or rolled back

transactions essentially being removed from the log (not quite that simple, but that is the effect).

This gives us a nice tight log that is smaller and often performs a bit better, but the log is of zero

use for recovery from system failure.

Decrease the log file Size:

Here we see how to decrease the log file we have lot of methods

Method 1:

First right click the database go to tasks and navigate to detach

And remove the old ldf file and again attach the same database again now ldf file created with minimum size of ldf file it have more or less 540 kb.

And again detach the database and copy and paste that ldf your normal path .again attach the database with specific ldf

Method 2:

Another one method is shrink the database

Right click the database go to tasks and choose shrink and navigate the database one pop-up window as below

then tick the reorganize files checkbox then click OK button .If you want to give the maximum size of free space the put it that box else leave it.

Except Operator in SQL Server 2005

EXCEPT operator will be used to determine which rows exist only in the left table of the query,TableA, and not in TableB:

SELECT EmployeeID,Name FROM TableA

EXCEPT

SELECT EmployeeID,Name FROM TableB

This Returns:

Emploteeid Name

-------------------- ------------------------------

1 Nancy

2 Andy

3 Janet

4 Margaret

5 Steven

This query have very less execution plan compare to not in operator.

SQL CHECK Constraint in SQL Server 2005

Check Constraint is used for check the value before inserting in to the table.
It is called validation process in software industry.

Below is Syntax for Check Constraint:

ALTER TABLE [TableName]
WITH
CHECK ADD CONSTRAINT [ConstraintName] CHECK
((Condition))

Example:

ALTER TABLE [dbo].[Employee]
WITH
CHECK ADD CONSTRAINT [CK_Employee] CHECK
(([EmployeeId]<>''))

This example is used for Employee table .This Constraint not allowed the '' value in to the Table.

How To use Table Type In SQL Server 2008

Here I give the T-SQL Statement for Table Datatype in SQL server 2008.

Use Below script you can create PeepsType as table and you can use it in anywhere in Storedprocedure, views and tables ,here i give sasmple procedure using table type

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int)

DECLARE @myPeeps PeepsType

INSERT @myPeeps SELECT 'murphy', 35

INSERT @myPeeps SELECT 'galen', 31

INSERT @myPeeps SELECT 'samuels', 27

INSERT @myPeeps SELECT 'colton', 42

exec sp_MySproc2 @myPeeps

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY)
as
begin

select * from @myPeeps

end

How to Get Noof Days in the month

Here I provide T-SQl Statement to get no of days in month using SQl server 2005

DECLARE @Dates datetime,@DaysInMonth int
set @dates='02/01/2009'
SET @DaysInMonth=Day(DateAdd (Month,1,@dates)-Day(DateAdd(Month,1,@dates)))
select @DaysInMonth

Order by clause for Dynamic Column in SQL

Your query have dynamic column you order the resultset by that Column,How can you
sort the resulset.
It is a interview question.
What is answer.
The answer is SQL allows the number in order by cluase
If your query is like this

"Select EmployeeId,EmployeeName,Address from Employee order by EmployeeName"
Our query is
"Select EmployeeId,EmployeeName,Address from Employee order by 2"
But this case only for Dynamic columns .Not for standard columns.

"select getdate(),id from table1 order by 1"
above is suitable for correct example

Get serial no in SQL Server

Here I provide simple SQl function
Row_Number() this function is used for get the sl no of that resultset

Here is example
select Row_Number() Over(Order by id desc)as SlNo,Id,firstName,address from employee
order by id desc
Below is output:

Send mail Using SQL CDOSYS in 2005

Here I create a procedure for send automatic mail to somebody using CDOsys with attachment.
I used BCP command for export data to file and i save it some location and I use in that procedure as attachment mail.We can create a job for this email then it will automatically send without our help.
Here the statement:
create proc [procname](@FromDate varchar(20),@ToDate varchar(20),@val varchar(20))
as
Begin
Begin
Try
declare @sql_waster varchar(8000),@sql_receiver varchar(8000),@FilenameWaster varchar(200),@FilenameReceiver varchar(200)
set

@FilenameWaster='D:\Somefolder'+CONVERT(VARCHAR(30),GETDATE(),112)+'.xls'
set

@FilenameReceiver='D:\Somefolder'+CONVERT(VARCHAR(30),GETDATE(),112) +'.xls'
set

@sql_receiver = 'bcp "Select query ('''+ @FromDate+''','''+
@ToDate
+''') " queryout '+ @FilenameReceiver + ' -S servername -U username -P password -c -T '
exec

master..xp_cmdshell @sql_receiver
set @sql_waster = 'bcp "Select query ('''+ @FromDate+''','''+ @ToDate
+''') " queryout '+ @FilenameReceiver + ' -S servername -U username -P password -c -T '
exec master..xp_cmdshell @sql_waster
Declare @iMsg int , @hr int, @source varchar(255),@description varchar(500),@output varchar(1000),@To varchar(100),@CC varchar(100),@BC varchar(100), @Subject varchar(100), @Body varchar(4000)
SET @Subject = 'put ur sub' + ' - '+ CONVERT(VARCHAR(11),cast(@FromDate as datetime),103) +' '+ 'to' + ' '+ CONVERT(VARCHAR(11),cast(@ToDate as datetime),103)
SET @Body = 'Sir ,'+ 'Please find the attached files '+''+
'Thanks & Regards, This Email is computer generated. Please dont reply.'
set @To='
' );
//-->\n
xxx@xyz.com
' );
//-->

This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->

'
set

@CC='
' );
//-->\n
yyy@xyz.com
' );
//-->

This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->

'
set

@BC ='
' );
//-->\n
zzz@xyz.com
' );
//-->

This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->

'



--***** Create the CDO.Message Object *****
EXEC

@hr = sp_OACreate 'CDO.Message', @iMsg OUT
if

@hr 0
print

'message object creation failed'

--*****Configuring the Message Object *****
-- This is to configure a remote SMTP server.
EXEC

@hr = sp_OASetProperty @iMsg, 'Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
if

@hr 0
print

'configuration failed'
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC

@hr = sp_OASetProperty @iMsg, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtpservername'----your smtp servername like bizmail.yahoo.com
if

@hr 0
print

'IP setting failed'
EXEC

@hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', ' mailid'--your smtp loginid
EXEC

@hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', 'pwd' --your smtp password
EXEC

@hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '1'
-- Save the configurations to the message object.
EXEC

@hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC

@hr = sp_OASetProperty @iMsg, 'To', @To
if

@hr 0
print

'to failed'
EXEC

@hr = sp_OASetProperty @iMsg, 'CC', @CC
EXEC

@hr = sp_OASetProperty @iMsg, 'BCC', @BC
EXEC

@hr = sp_OASetProperty @iMsg, 'From', '
' );
//-->\n
some@company.com
' );
//-->

This e-mail address is being protected from spambots. You need JavaScript enabled to view it
' );
//-->

'
if

@hr 0
print

'from failed'
EXEC

@hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC

@hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC

@hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @FilenameReceiver --this is attachment
EXEC

@hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @FilenameWaster
EXEC

@hr = sp_OAMethod @iMsg, 'Send', NULL
if

@hr 0
print

'Q Send failed'
IF

@hr 0
BEGIN
EXEC

@hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF

@hr = 0
BEGIN
SELECT

@output = ' Source: ' + @source
PRINT

@output
SELECT

@output = ' Description: ' + @description
PRINT

@output
END
ELSE
BEGIN
PRINT

' sp_OAGetErrorInfo failed.'
RETURN
END
END
EXEC

@hr = sp_OADestroy @iMsg
End

Try
Begin

catch

select Error_Message()

END CATCH
End