Rectangular Star Script in T-SQL

Here this script display rectangle with @showingCharacter = '*'.


declare @i int
declare @j int
declare @k int
declare @totLine int
declare @res varchar(100)
declare @showingCharacter varchar(1)
set @showingCharacter = '*'
set @totLine = 10
set @i = 1
set @j = 1
set @k = 1
set @res = ''
while(@i <= @totLine)
begin
while(@k <= (@totLine) - @i)
begin
set @res = @res + ' '
set @k = @k + 1
end
while(@j <= @i)
begin
set @res = @res + @showingCharacter + ' '
set @j = @j + 1
end
set @k = 1
while(@k <= (@totLine) - @i)
begin
set @res = @res + ' '
set @k = @k + 1
end
print @res
set @res = ''
set @j = 1
set @k = 1
set @i = @i + 1
end

Output will be...
^
^ ^
^ ^ ^
^ ^ ^ ^
^ ^ ^ ^ ^
^ ^ ^ ^ ^ ^
^ ^ ^ ^ ^ ^ ^
^ ^ ^ ^ ^ ^ ^ ^
^ ^ ^ ^ ^ ^ ^ ^ ^
^ ^ ^ ^ ^ ^ ^ ^ ^ ^

Leap Year Calculation Script in T-SQL

--Here this script is check weather give year is leap year or not.

declare @year int
declare @isLeapYear bit
set @isLeapYear = 'false'
set @year = 2004
print 'Given year is : ' + cast(@year as varchar)

if(@year % 4 = 0)
set @isLeapYear = 'true'

if(@year % 100 = 0)
if(@year % 400 = 0)
set @isLeapYear = 'false'

if(@isLeapYear = 'true')
print cast(@year as varchar) + ' is a leap year.'
else
print cast(@year as varchar) + ' is not a leap year.'

Output will be...
Given year is : 2004
2004 is a leap year.

String Concatenation Script in T-SQL

Here this script is used for concatenates one or more string in to one string.


declare @str1 varchar(10)
declare @str2 varchar(10)
declare @str3 varchar(20)
declare @i int
set @i = 1
set @str1 = 'Mitesh'
set @str2 = 'Pupple'
set @str3 = ''
print 'string first : ' + @str1
print 'string second : ' + @str2
while(@i <= len(@str1))
begin
set @str3 = @str3 + substring(@str1,@i,1)
set @i = @i + 1
end
set @i = 1
while(@i <= len(@str2))
begin
set @str3 = @str3 + substring(@str2,@i,1)
set @i = @i + 1
end
print 'concatanation string : ' +@str3

Output will be...
string first : Mitesh
string second : Pupple
concatanation string : MiteshPupple

Fibonacci Series Script in T-SQL

Here this script is displying Fibonacci Series from 1 to 1000.


declare @a int
declare @b int
declare @c int
declare @res varchar(200)
set @a = 0
set @b = 1
set @c = 0
set @res = ' '

while(@c<=1000)
begin
set @a = @b
Set @b = @c
set @c = @a + @b
set @res = @res + cast(@c as varchar) + ','
end

print reverse(substring(reverse(@res),2,len(@res)))

Output will be...
1,1,2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597

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.