Full Text Search

Here first of all assign Full text search on ur database by select database than select property by right click than select File options exist in left side and than select check box for Use full text indexing.

Than u have to add that tables for which u want to give Full text search by this select table than right click than select Full Text Index than select Define Full Text Index and complite the wizard. Here in this wizard u have to add those field which u want to search by Full text search.

After completion to assign all table in full text catelog u have to use four function which are used in Full text search....

contains()
freetext()
containstable()
freetexttable()

this four function give proper result by Full text search...

Replication

Database replication is the creation and maintenance of multiple copies of the same database. In most implementations of database replication, one database server maintains the master copy of the database and additional database servers maintain slave copies of the database. Database writes are sent to the master database server and are then replicated by the slave database servers. Database reads are divided among all of the database servers, which results in a large performance advantage due to load sharing. In addition, database replication can also improve availability because the slave database servers can be configured to take over the master role if the master database server becomes unavailable.

Trigger

A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

There are two classes of triggers, they are either "row triggers" or "statement triggers". With row triggers you can define an action for every row of a table, while statement triggers occur only once per INSERT, UPDATE, or DELETE statement. Triggers cannot be used to audit data retrieval via SELECT statements.

Each class can be of several types. There are "BEFORE triggers" and "AFTER triggers" which identifies the time of execution of the trigger. There is also an "INSTEAD OF trigger" which is a trigger that will execute instead of the triggering statement.

There are typically three triggering events that cause triggers to 'fire':

INSERT event (as a new record is being inserted into the database).
UPDATE event (as a record is being changed).
DELETE event (as a record is being deleted).
The trigger is used to automate DML condition process.

The major features and effects of database triggers are that they:

do not accept parameters or arguments (but may store affected-data in temporary tables)
cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)
can cause mutating table errors, if they are poorly written.

Indexing

Clustered Indexes
A printed phone directory is a great example of a clustered index. Each entry in the directory represents one row of the table. A table can have only one clustered index. That is because a clustered index is the actual table sorted in order of the cluster key. At first glance, you might think that inserting a new row into the table will require all the rows after the inserted row to be moved on the disk. Luckily, this is not the case. The row will have to be inserted into the correct data page, and this might require a page split if there is not enough room on the page for the new row. A list of pointers maintains the order between the pages, so the rows in other pages will not have to actually move.

The primary key of the phone directory is the phone number. Usually the primary key is used as the clustering key as well, but this is not the case in our example. The cluster key in the phone directory is a combination of last name and first name. How would you find a friend's phone number if you knew the last and first name? Easy, open the book approximately to the section of the book that contains the entry. If your friend's last name starts with an "F", you will search near the beginning of the book, if an "S", you will search towards the back. You can use the names printed at the top of the page to quickly locate the page with the listing. You then drill down to the section of the correct page till you find the last name you are looking for. Now you can use the first name to choose the correct listing. The phone number is right there next to the name. It probably takes more time describe the process than to actually do it. Using the last name plus first name to find the number is called a clustered index seek.

Lets say you wanted to find all the people who have the last name of "Smith" for a family reunion. How quickly could you locate all the names? Of course, it would take a matter of seconds to find all of them grouped together, possibly over a few pages. What if you wanted to locate everyone with the first name of "Jeff" in the book? Could it be done? Of course it could, but you would have to look at every entry in the entire book because the first name is the second column in our cluster key. This is called a clustered index scan, a very expensive operation on a big table.

Here is an example using one of the tables in AdventureWorks. The Sales.SalesOrderDetail table has a clustered index on SalesOrderID plus SalesOrderDetailID. Take a look at the graphical estimated execution plan (Figure 2) of the batch paying particular attention to the Query cost when either the first or second column in the cluster key is used.

Prime No Script in T-SQL

Here this script is check weather no from 1 to 100 is Prime or not if no is Prime than print it.

SET NOCOUNT on
go
declare @val bigint
declare @tval bigint
declare @oval bigint
declare @rem bigint
declare @res bigint
declare @divby int
declare @fg bit


set @res = 0
set @val = 7
set @tval = @val
set @divby = 2
set @fg = 'true'
--

declare @inputTable table(_val int)
declare @i int
set @i = 1

while(@i<=100) begin insert into @inputTable values(@i) set @i = @i + 1 end declare ibuffer cursor fast_forward for select _val from @inputTable open ibuffer fetch next from ibuffer into @val while (@@fetch_status != -1) BEGIN set @divby = 2 set @fg = 'true' set @tval = @val set @oval = @val set @res = 0 while(@divby > 1 and @divby <= @val -1) begin set @rem = @tval % @divby if(@rem = 0) begin set @fg = 'false' break end set @divby = @divby + 1 end if(@fg = 'true') begin print 'Given no is prime --- > ' + cast(@oval as varchar)
end


fetch next from ibuffer into @val
End
deallocate ibuffer

Output will be...
Given no is prime --- > 1
Given no is prime --- > 2
Given no is prime --- > 3
Given no is prime --- > 5
Given no is prime --- > 7
Given no is prime --- > 11
Given no is prime --- > 13
Given no is prime --- > 17
Given no is prime --- > 19
Given no is prime --- > 23
Given no is prime --- > 29
Given no is prime --- > 31
Given no is prime --- > 37
Given no is prime --- > 41
Given no is prime --- > 43
Given no is prime --- > 47
Given no is prime --- > 53
Given no is prime --- > 59
Given no is prime --- > 61
Given no is prime --- > 67
Given no is prime --- > 71
Given no is prime --- > 73
Given no is prime --- > 79
Given no is prime --- > 83
Given no is prime --- > 89
Given no is prime --- > 97

Digit Sum Script in T-SQL

Here this script it used for check which no from 1 to 100 have sum of digit

--Here this script it used for check which no from 1 to 100 have sum of digit
--is equal to @outputTot.
--Example if @outputTot set to 5 means
-- first 5 = digit sum is 5.
-- than 14 = 1 + 4 = digit sum is 5.
-- than 23 = 2 + 3 = digit sum is 5.
-- than 32,41,50
-- than 59 = 5 + 9 = 14 than 1 + 4 = digit sum is 5.
-- above example is calculate digit sum untill sum is between 1 to 9.

SET NOCOUNT on
go
declare @val bigint
declare @tval bigint
declare @oval bigint
declare @rem bigint
declare @res bigint
declare @outputTot bigint

set @res = 0
set @val = 43
set @tval = @val
set @outputTot = 3


declare @inputTable table(_val int)
declare @i int
set @i = 1

while(@i<=100) begin insert into @inputTable values(@i) set @i = @i + 1 end declare ibuffer cursor fast_forward for select _val from @inputTable open ibuffer fetch next from ibuffer into @val while (@@fetch_status != -1) BEGIN set @tval = @val set @oval = @val set @res = 0 while(@val > 9)
begin
set @res = 0
while(@tval > 0)
begin
set @rem = @tval % 10
set @res = @res + @rem
set @tval = @tval / 10
end

set @val = @res
set @tval = @val

end
if(@res = @outputTot)
begin
print 'Total digit sum is '+ cast(@outputTot as varchar) +' of ' + cast(@oval as varchar)
end


fetch next from ibuffer into @val
End
deallocate ibuffer

Output will be...
Total digit sum is 3 of 12
Total digit sum is 3 of 21
Total digit sum is 3 of 30
Total digit sum is 3 of 39
Total digit sum is 3 of 48
Total digit sum is 3 of 57
Total digit sum is 3 of 66
Total digit sum is 3 of 75
Total digit sum is 3 of 84
Total digit sum is 3 of 93

Sorting Script in T-SQL

Here i create temporary table for sorting columns.

SET NOCOUNT on
go
declare @tempTbl table(i int,strName varchar(20))

insert into @tempTbl values(1,'Manish')
insert into @tempTbl values(2,'Supratim')
insert into @tempTbl values(3,'Anil')
insert into @tempTbl values(4,'Pranav')
insert into @tempTbl values(5,'Chirag')
insert into @tempTbl values(6,'Paresh')
insert into @tempTbl values(7,'Nilesh')
insert into @tempTbl values(8,'Bhavesh')
insert into @tempTbl values(9,'Hitesh')
insert into @tempTbl values(10,'Subhash')
insert into @tempTbl values(11,'Anish')
insert into @tempTbl values(12,'Ania')


declare @i int
declare @j int
declare @pos int
declare @strName varchar(20)
set @i = 0
set @j = 1

declare ibuffer cursor fast_forward for

select i,strName from @tempTbl


open ibuffer
fetch next from ibuffer into @pos,@strName
while (@@fetch_status != -1)
begin
declare @ipos int
declare @istrName varchar(20)
declare iibuffer cursor fast_forward for

select i,strName from @tempTbl
where i > @pos
open iibuffer
fetch next from iibuffer into @ipos,@istrName
while (@@fetch_status != -1)
begin
if(@strName > @istrName)
begin
update @tempTbl
set strName = @strName
where i = @ipos

update @tempTbl
set strName = @istrName
where i = @pos

set @strName = @istrName
print '------Updated------'
end

fetch next from iibuffer into @ipos,@istrName
end
deallocate iibuffer
fetch next from ibuffer into @pos,@strName
end
deallocate ibuffer

select strName from @tempTbl

Output will be...
Ania
Anil
Anish
Bhavesh
Chirag
Hitesh
Manish
Nilesh
Paresh
Pranav
Subhash
Supratim

T-SQL Scripts for Table

----------------------------------------------------------------------------------------------
Here in many cases we have to first find table contain record or not.
If table contains record than update that row is going on. If table not contain record than insert new row in table.
So in this case we first find that table contains records or not and than we decide to take which operation have to perform on that table.
Like…….
DECLARE @COLUMNID INT
DECLARE @NEWVALUE
DECLARE @cnt INT
SELECT @cnt = COUNT(COLUMNNAME) FROM TABLENAME WHERE COLUMNID = @COLUMNID

IF(@cnt > 0)
BEGIN
UPDATE TABLENAME
SET COLUMNNAME = @NEWVALUE
WHERE COLUMNID = @COLUMNID
END
ELSE
BEGIN
INSERT INTO TABLENAME
(COMUMNNAME)
VALUES(@NEWVALUE)
END

This will done shortly with the use of exists() function that return ‘true’ if select query return records
Otherwise return ‘false’
Like……..

IF EXISTS(SELECT COLUMNNAME FROM TABLENAME WHERE COLUMNID = @COLUMNID)
BEGIN
UPDATE TABLENAME
SET COLUMNNAME = @NEWVALUE
WHERE COLUMNID = @COLUMNID
END
ELSE
BEGIN
INSERT INTO TABLENAME
(COMUMNNAME)
VALUES(@NEWVALUE)
END
----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------
Here sql statements for
1.creating table
2.inserting record
3.updating record
4.deleting record
5.fetching records
6.droping table

For example i take Student as a entity and perform above statements
for it.


1.Creating table

CREATE TABLE [dbo].[Student](
[studentID] [bigint] IDENTITY(1,1) NOT NULL,
[studentName] [varchar](50) NOT NULL,
[studentAge] [int] NOT NULL,
[studentSex] [bit] NOT NULL,
[studentBirthDate] [datetime] NOT NULL,
[studentAddress] [varchar](200) NULL,
CONSTRAINT [PK_StudentMaster] PRIMARY KEY CLUSTERED
(
[studentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


2.Inserting record

INSERT INTO [dbo].[Student]
([studentName]
,[studentAge]
,[studentSex]
,[studentBirthDate]
,[studentAddress])
VALUES
('Paresh Patel'
,25
,'true'
,'1982-06-23 00:00:00.000'
,'Ahmedabad')


3.Updating record

UPDATE [dbo].[Student]
SET [studentName] = 'Nirmit Oza'
,[studentAge] = 25
,[studentSex] = 'false'
,[studentBirthDate] = '1982-06-23 00:00:00.000'
,[studentAddress] = 'Mehsana'
WHERE [studentID] = 1


4.Deleting record

DELETE FROM [dbo].[Student]
WHERE [studentID] = 2


5.Fetching records

SELECT [studentID]
,[studentName]
,[studentAge]
,[studentSex]
,[studentBirthDate]
,[studentAddress]
FROM [dbo].[Student]


6.Droping table

DROP TABLE [dbo].[Student]
----------------------------------------------------------------------------------------------