SELECT a.NAME,a.product,a.provider,a.data_source,a.catalog ,
f.[name],b.[uses_self_credential],b.[remote_name] FROM sys.[linked_logins] AS b
INNER JOIN sys.servers AS a
ON a.[server_id]=b.[server_id]
INNER JOIN sys.[server_principals] AS f
ON b.[local_principal_id]=f.[principal_id]
Wednesday, December 24, 2008
Deleting Duplicate Records
--Find duplicate records
--The result of this query is all the duplicate records whose Id is greater.
select a.* from Employees a
join Employees b on
a.[EmployeeNo] = b.[EmployeeNo]
AND a.[EmployeeID]= b.[EmployeeID]
AND a.Id>b.Id
--Delete duplicate records
--The result of this query is deletion of all the duplicate records whose Id is greater.
delete a.* from Employees a
join Employees b on
a.[EmployeeNo] = b.[EmployeeNo]
AND a.[EmployeeID]= b.[EmployeeID]
AND a.Id>b.Id
--The result of this query is all the duplicate records whose Id is greater.
select a.* from Employees a
join Employees b on
a.[EmployeeNo] = b.[EmployeeNo]
AND a.[EmployeeID]= b.[EmployeeID]
AND a.Id>b.Id
--Delete duplicate records
--The result of this query is deletion of all the duplicate records whose Id is greater.
delete a.* from Employees a
join Employees b on
a.[EmployeeNo] = b.[EmployeeNo]
AND a.[EmployeeID]= b.[EmployeeID]
AND a.Id>b.Id
Friday, December 12, 2008
sp_spaceused
sp_spaceused is a useful stored procedure for telling you how much physical space your database or database object consumes.
There are two obvious points with sp_spaceused.
It does not list the SQL2005 schema to which the object belongs
The data size values are text values to include the KB comment
There are two obvious points with sp_spaceused.
It does not list the SQL2005 schema to which the object belongs
The data size values are text values to include the KB comment
SQL Server 2005 Encryption types
SQL Server 2005 provides the following mechanism of encryption in order to encrypt the data.
ENCRYPTION by passphrase
ENCRYPTION by symmetric keys
ENCRYPTION by Asymmetric keys
ENCRYPTION by certificates
“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.
select EncryptedData = EncryptByPassPhrase('aad', 'abc' )
select convert(varchar(100),DecryptByPassPhrase('aad'
, 0x01000000563148628C8E8CCAC2114DB23AE4E525AAB518C57F3F51CA))
The result of the Encryptbypassphrase is different every time you execute the Encryptbypassphrase function. However, when you decrypt the data it would decrypt perfectly.
SQL Server generates NULL as the result when the password is wrong.
ENCRYPTION by passphrase
ENCRYPTION by symmetric keys
ENCRYPTION by Asymmetric keys
ENCRYPTION by certificates
“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.
select EncryptedData = EncryptByPassPhrase('aad', 'abc' )
select convert(varchar(100),DecryptByPassPhrase('aad'
, 0x01000000563148628C8E8CCAC2114DB23AE4E525AAB518C57F3F51CA))
The result of the Encryptbypassphrase is different every time you execute the Encryptbypassphrase function. However, when you decrypt the data it would decrypt perfectly.
SQL Server generates NULL as the result when the password is wrong.
Wednesday, December 10, 2008
Simple way to export SQL Server data to Text Files
One of the ways I have worked around this is by using bcp with xp_cmdshell. It's fast, easy and I can integrate it right into my code.
The first thing you have to do is make sure xp_cmdshell is enabled. You can do this in one of two ways.
1. You can use sp_configure and execute the following script.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
2. You can use the Surface Area Configuration Tool.
Select Surface Area Configuration for Features and check the Enable xp_cmdshell checkbox.
Here is a sample command that will export the results of the sysfiles table to a comma delimited file called bcptest.txt. Just copy and paste this into a query window and execute the query.
EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "C:\bcptest.txt" -T -c -t,'
The parameters that were used are:
. The queryout option allows you to specify a query to export. This could be as simple as the query we have are as complicated as you want. You can also create a view and select the data from a view.
. The file name where the results will be stored is placed after the queryout option.
. The -T parameter specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. You could use -P (password) and -U (user) if you want to use a SQL Server login.
. The -c specifies the character dataype will be used for each field.
. The -t parameter allows you to specify a field delimiter. The character after -t will be used to separate the data fields. If -t is removed, tab will be used as the default delimiter.
The first thing you have to do is make sure xp_cmdshell is enabled. You can do this in one of two ways.
1. You can use sp_configure and execute the following script.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
2. You can use the Surface Area Configuration Tool.
Select Surface Area Configuration for Features and check the Enable xp_cmdshell checkbox.
Here is a sample command that will export the results of the sysfiles table to a comma delimited file called bcptest.txt. Just copy and paste this into a query window and execute the query.
EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "C:\bcptest.txt" -T -c -t,'
The parameters that were used are:
. The queryout option allows you to specify a query to export. This could be as simple as the query we have are as complicated as you want. You can also create a view and select the data from a view.
. The file name where the results will be stored is placed after the queryout option.
. The -T parameter specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. You could use -P (password) and -U (user) if you want to use a SQL Server login.
. The -c specifies the character dataype will be used for each field.
. The -t parameter allows you to specify a field delimiter. The character after -t will be used to separate the data fields. If -t is removed, tab will be used as the default delimiter.
Data Types in SQL Server 2008
There are over thirty-five different Data Types in SQL Server 2008.
Exact Numbers, Approximate Numbers, Date and Times, Character Strings, Unicode Character Strings, Binary Stings, and Other data types.
There are two categories of numbers, Exact Numbers and Approximate Numbers.
Approximate number include the types Real and Float. In general terms, think of Approximate Numbers to be used when Scientific Notation is needed. Scientific Notation is a way to describe very large or very small numbers using powers of ten (also called Exponential Notation).
Exact Numbers include Decimals, Integers, and Money amounts.
An Integer is a counting number with no decimal point or fractional piece. All negative numbers, positive numbers, and zero are integers. SQL Server breaks integers into four sizes:
BigInt: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Int: -2,147,483,648 to 2,147,483,648
SmallInt: -32,768 to 32,767
TinyInt: 0 to 255
There are two primary reasons for trying to “right size” the Int type you select rather than just setting everything as a BigInt. The first is physical disk space. A BigInt takes up eight byes per row while a regular Int only uses two.
Exact Numbers with decimal places include the Data Types Decimal, Numeric, Money, and SmallMoney. The types Decimal and Numeric are functionally the same thing. Meaning, they will work, calculate and behave identically, the only difference is in the mathematical definition and not in the way SQL Server utilizes them. Most SQL Server applications I run into use Decimal. A Decimal can be up to 38 digits in length. When the Decimal is defined, its total length and maximum number of decimal places to the right are configured. The larger the number of digits defined the more physical disk space used on each row.
Money and SmallMoney are really Decimals with a fixed amount of four decimal places to the right. SmallMoney can be valued from - 214,748.3648 to 214,748.3647 while the range of Money is from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. One of the reasons for using Money rather than a Decimal includes the display option of dollar signs and commas after three digits.
Binary Stings
Binary stings are used for saving non-character data, such as images and audio. There are two data types used for this: Binary and VarBinary. They configure like Char and VarChar. VarBinary(Max) can be used to store files of unlimited size.
Strings
Char, VarChar, and Text. A Char (Character) Data Type can hold letters, numbers, and keyboard characters. When a Char is defined, its max length, up to 8,000, is also fixed. If the length of the data will vary from row to row, such as an email address, then use VarChar. A VarChar is a Char of variable (Var) length. When a VarChar is created, its max length is also defined. A main reason to use a VarChar rather than a Char is the amount of physical disk space used by each.Another useful feature of a VarChar is the ability to specify an unlimited maximum size. This is done with the “Max” key word, as in VarChar(Max). Max means the size may exceed 8,000 bytes. In addition, the size is unlimited.
The last Character String is the Data Type Text. Text was similar to a VarChar(Max) column.
Other Data Types
There are seven Other Data Types including Cursor, HierachyID, SQL Variant, Table, TimeStamp, UniqueIdentifier, and XML. The data type TimeStamp has been replaced by RowVersion. UniqueIdentifier is a unique GUID. The data type SQL Variant can be used when you don’t know what type of data to expect. It’s basically 8,000 bytes of anything goes storage. If you’re using XML, use the actual XML data type rather than a VarChar. The XML type allows binding to collections.
Exact Numbers, Approximate Numbers, Date and Times, Character Strings, Unicode Character Strings, Binary Stings, and Other data types.
There are two categories of numbers, Exact Numbers and Approximate Numbers.
Approximate number include the types Real and Float. In general terms, think of Approximate Numbers to be used when Scientific Notation is needed. Scientific Notation is a way to describe very large or very small numbers using powers of ten (also called Exponential Notation).
Exact Numbers include Decimals, Integers, and Money amounts.
An Integer is a counting number with no decimal point or fractional piece. All negative numbers, positive numbers, and zero are integers. SQL Server breaks integers into four sizes:
BigInt: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Int: -2,147,483,648 to 2,147,483,648
SmallInt: -32,768 to 32,767
TinyInt: 0 to 255
There are two primary reasons for trying to “right size” the Int type you select rather than just setting everything as a BigInt. The first is physical disk space. A BigInt takes up eight byes per row while a regular Int only uses two.
Exact Numbers with decimal places include the Data Types Decimal, Numeric, Money, and SmallMoney. The types Decimal and Numeric are functionally the same thing. Meaning, they will work, calculate and behave identically, the only difference is in the mathematical definition and not in the way SQL Server utilizes them. Most SQL Server applications I run into use Decimal. A Decimal can be up to 38 digits in length. When the Decimal is defined, its total length and maximum number of decimal places to the right are configured. The larger the number of digits defined the more physical disk space used on each row.
Money and SmallMoney are really Decimals with a fixed amount of four decimal places to the right. SmallMoney can be valued from - 214,748.3648 to 214,748.3647 while the range of Money is from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. One of the reasons for using Money rather than a Decimal includes the display option of dollar signs and commas after three digits.
Binary Stings
Binary stings are used for saving non-character data, such as images and audio. There are two data types used for this: Binary and VarBinary. They configure like Char and VarChar. VarBinary(Max) can be used to store files of unlimited size.
Strings
Char, VarChar, and Text. A Char (Character) Data Type can hold letters, numbers, and keyboard characters. When a Char is defined, its max length, up to 8,000, is also fixed. If the length of the data will vary from row to row, such as an email address, then use VarChar. A VarChar is a Char of variable (Var) length. When a VarChar is created, its max length is also defined. A main reason to use a VarChar rather than a Char is the amount of physical disk space used by each.Another useful feature of a VarChar is the ability to specify an unlimited maximum size. This is done with the “Max” key word, as in VarChar(Max). Max means the size may exceed 8,000 bytes. In addition, the size is unlimited.
The last Character String is the Data Type Text. Text was similar to a VarChar(Max) column.
Other Data Types
There are seven Other Data Types including Cursor, HierachyID, SQL Variant, Table, TimeStamp, UniqueIdentifier, and XML. The data type TimeStamp has been replaced by RowVersion. UniqueIdentifier is a unique GUID. The data type SQL Variant can be used when you don’t know what type of data to expect. It’s basically 8,000 bytes of anything goes storage. If you’re using XML, use the actual XML data type rather than a VarChar. The XML type allows binding to collections.
Tuesday, December 9, 2008
Recursive function for calculating next business day
CREATE TABLE [holiday] (
[holidayDate] [smalldatetime] NOT NULL ,
CONSTRAINT [PK_holidayDate] PRIMARY KEY CLUSTERED
(
[holidayDate]
)
)
create function fnGetNextBusinessDay (@startDate smalldatetime,@numDays int)
returns smalldatetime as
Begin
Declare @nextBusDay smalldatetime
Declare @weekDay tinyInt
set @nextBusDay = @startDate
Declare @dayLoop int
set @dayLoop = 0
while @dayLoop < @numDays
Begin
set @nextBusDay = dateAdd(d,1,@nextBusDay) -- first get the raw next day
SET @weekDay =((@@dateFirst+datePart(dw,@nextBusDay)-2) % 7) + 1
-- always returns Mon=1 - can't use set datefirst in UDF
-- % is the Modulo operator which gives the remainder
-- of the dividend divided by the divisor (7)
-- this allows you to create repeating
-- sequences of numbers which go from 0 to 6
-- the -2 and +1 adjust the sequence start point (Monday) and initial value (1)
if @weekDay = 6 set @nextBusDay = @nextBusDay + 2 -- since day by day Saturday = jump to Monday
-- Holidays - function calls itself to find the next business day
select @nextBusDay = dbo.fnGetNextBusinessDay(@nextBusDay,1)
where exists (select holidayDate from Holiday where holidayDate=@nextBusDay)
-- next day
set @dayLoop = @dayLoop + 1
End
return @nextBusDay
End
[holidayDate] [smalldatetime] NOT NULL ,
CONSTRAINT [PK_holidayDate] PRIMARY KEY CLUSTERED
(
[holidayDate]
)
)
create function fnGetNextBusinessDay (@startDate smalldatetime,@numDays int)
returns smalldatetime as
Begin
Declare @nextBusDay smalldatetime
Declare @weekDay tinyInt
set @nextBusDay = @startDate
Declare @dayLoop int
set @dayLoop = 0
while @dayLoop < @numDays
Begin
set @nextBusDay = dateAdd(d,1,@nextBusDay) -- first get the raw next day
SET @weekDay =((@@dateFirst+datePart(dw,@nextBusDay)-2) % 7) + 1
-- always returns Mon=1 - can't use set datefirst in UDF
-- % is the Modulo operator which gives the remainder
-- of the dividend divided by the divisor (7)
-- this allows you to create repeating
-- sequences of numbers which go from 0 to 6
-- the -2 and +1 adjust the sequence start point (Monday) and initial value (1)
if @weekDay = 6 set @nextBusDay = @nextBusDay + 2 -- since day by day Saturday = jump to Monday
-- Holidays - function calls itself to find the next business day
select @nextBusDay = dbo.fnGetNextBusinessDay(@nextBusDay,1)
where exists (select holidayDate from Holiday where holidayDate=@nextBusDay)
-- next day
set @dayLoop = @dayLoop + 1
End
return @nextBusDay
End
Tuesday, December 2, 2008
Truncate All Tables
Script to removed all the constraints from a database to truncate all the tables.
Most of the time this approach is used to deleted all records from a database or set staging tables from an existing database model.
How to used:
1.- Create table to hold constraints values ( part 2 explain how to revert the process)
2.-Populate table
3.- Create cursor to remove constraints
4.- truncate all data
Use DatabaseName
--Temporary table to hold constraints info most of the time at a different location or database
--This could be a temp table however set as static
IF EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')
truncate table T_FK_Xref
go
--Create Table to store constraint information
IF NOT EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')
Create table DatabaseName.dbo.T_FK_Xref (
ID int identity (1,1),
ConstraintName varchar (255),
MasterTable varchar(255),
MasterColumn varchar(255),
ChildTable varchar(255),
ChildColumn varchar(255),
FKOrder int
)
go
--Store Constraints
insert into DatabaseName.dbo.T_FK_Xref(ConstraintName,MasterTable,MasterColumn,ChildTable,ChildColumn,FKOrder)
SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
,sc2.name MasterColumn
,object_name(fkeyid) ChildTable
,sc1.name ChildColumn
,cast (sf.keyno as int) FKOrder
FROM sysforeignkeys sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno
go
use databaseName --Database to removed constraints
go
---Ready to remove constraints
declare @ConstraintName varchar (max) -- Name of the Constraint
declare @ChildTable varchar (max) -- Name of Child Table
declare @MasterTable varchar (max)--Name of Parent Table
declare @ChildColumn varchar (max)--Column of Child Table FK
declare @MasterColumn varchar (max)-- Parent Column PK
declare @FKOrder smallint -- Fk order
declare @sqlcmd varchar (max) --Dynamic Sql String
-- Create cursor to get constraint Information
declare drop_constraints cursor
fast_forward
for
SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
,sc2.name MasterColumn
,object_name(fkeyid) ChildTable
,sc1.name ChildColumn
,cast (sf.keyno as int) FKOrder
FROM sysforeignkeys sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno
open drop_constraints
fetch next from drop_constraints
into
@ConstraintName
,@MasterTable
,@MasterColumn
,@ChildTable
,@ChildColumn
,@FKOrder
while @@Fetch_status = 0
begin
-- Create Dynamic Sql to drop constraint
select @sqlcmd = 'alter table '+@ChildTable+' drop constraint '+@ConstraintName--+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'
If EXISTs (select object_name(constid) from sysforeignkeys where object_name(constid) = @ConstraintName)
exec (@sqlcmd)
fetch next from drop_constraints
into
@ConstraintName
,@MasterTable
,@MasterColumn
,@ChildTable
,@ChildColumn
,@FKOrder
end
close drop_constraints
deallocate drop_constraints
go
--Removed CHECK Constraint-------------------------
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' --NOCHECK Constraints
print 'All Constraints Disable'
go
--truncate All tables if trying to empty the database
--- Ensure the T_X_ref database is located on a different database
------------- Truncate All Tables from Model ----------------
-----To limit tables a table with sub model tables must be created and used joins-----
EXEC sp_MSForEachTable 'truncate TABLE ? '
print 'All tables truncated'
Most of the time this approach is used to deleted all records from a database or set staging tables from an existing database model.
How to used:
1.- Create table to hold constraints values ( part 2 explain how to revert the process)
2.-Populate table
3.- Create cursor to remove constraints
4.- truncate all data
Use DatabaseName
--Temporary table to hold constraints info most of the time at a different location or database
--This could be a temp table however set as static
IF EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')
truncate table T_FK_Xref
go
--Create Table to store constraint information
IF NOT EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')
Create table DatabaseName.dbo.T_FK_Xref (
ID int identity (1,1),
ConstraintName varchar (255),
MasterTable varchar(255),
MasterColumn varchar(255),
ChildTable varchar(255),
ChildColumn varchar(255),
FKOrder int
)
go
--Store Constraints
insert into DatabaseName.dbo.T_FK_Xref(ConstraintName,MasterTable,MasterColumn,ChildTable,ChildColumn,FKOrder)
SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
,sc2.name MasterColumn
,object_name(fkeyid) ChildTable
,sc1.name ChildColumn
,cast (sf.keyno as int) FKOrder
FROM sysforeignkeys sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno
go
use databaseName --Database to removed constraints
go
---Ready to remove constraints
declare @ConstraintName varchar (max) -- Name of the Constraint
declare @ChildTable varchar (max) -- Name of Child Table
declare @MasterTable varchar (max)--Name of Parent Table
declare @ChildColumn varchar (max)--Column of Child Table FK
declare @MasterColumn varchar (max)-- Parent Column PK
declare @FKOrder smallint -- Fk order
declare @sqlcmd varchar (max) --Dynamic Sql String
-- Create cursor to get constraint Information
declare drop_constraints cursor
fast_forward
for
SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
,sc2.name MasterColumn
,object_name(fkeyid) ChildTable
,sc1.name ChildColumn
,cast (sf.keyno as int) FKOrder
FROM sysforeignkeys sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno
open drop_constraints
fetch next from drop_constraints
into
@ConstraintName
,@MasterTable
,@MasterColumn
,@ChildTable
,@ChildColumn
,@FKOrder
while @@Fetch_status = 0
begin
-- Create Dynamic Sql to drop constraint
select @sqlcmd = 'alter table '+@ChildTable+' drop constraint '+@ConstraintName--+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'
If EXISTs (select object_name(constid) from sysforeignkeys where object_name(constid) = @ConstraintName)
exec (@sqlcmd)
fetch next from drop_constraints
into
@ConstraintName
,@MasterTable
,@MasterColumn
,@ChildTable
,@ChildColumn
,@FKOrder
end
close drop_constraints
deallocate drop_constraints
go
--Removed CHECK Constraint-------------------------
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' --NOCHECK Constraints
print 'All Constraints Disable'
go
--truncate All tables if trying to empty the database
--- Ensure the T_X_ref database is located on a different database
------------- Truncate All Tables from Model ----------------
-----To limit tables a table with sub model tables must be created and used joins-----
EXEC sp_MSForEachTable 'truncate TABLE ? '
print 'All tables truncated'
Deleting Batches of Rows with TOP
CREATE TABLE tab1
( col1 INT
, col2 CHAR(1)
)
INSERT INTO tab1 VALUES (1, 'A')
INSERT INTO tab1 VALUES (1, 'B')
INSERT INTO tab1 VALUES (1, 'C')
INSERT INTO tab1 VALUES (1, 'D')
INSERT INTO tab1 VALUES (1, 'E')
INSERT INTO tab1 VALUES (8, 'F')
INSERT INTO tab1 VALUES (9, 'G')
----------------------------
BEGIN
DECLARE @N INT -- Number of rows to delete per batch
DECLARE @cnt decimal(18,2) -- Total count of rows matching specified criterion
DECLARE @loops INT -- Number of times the DELETE statement should loop to delete all relevent records
SET @N = 2
SELECT @cnt = COUNT(*) FROM tab1 WHERE col1 = 1
SET @loops = CEILING(@cnt/@N)
WHILE @loops > 0
BEGIN
DELETE TOP (@N) FROM tab1 WHERE col1 = 1
SET @loops = @loops - 1
END
END
-----------------
SELECT * FROM tab1
DROP TABLE tab1
( col1 INT
, col2 CHAR(1)
)
INSERT INTO tab1 VALUES (1, 'A')
INSERT INTO tab1 VALUES (1, 'B')
INSERT INTO tab1 VALUES (1, 'C')
INSERT INTO tab1 VALUES (1, 'D')
INSERT INTO tab1 VALUES (1, 'E')
INSERT INTO tab1 VALUES (8, 'F')
INSERT INTO tab1 VALUES (9, 'G')
----------------------------
BEGIN
DECLARE @N INT -- Number of rows to delete per batch
DECLARE @cnt decimal(18,2) -- Total count of rows matching specified criterion
DECLARE @loops INT -- Number of times the DELETE statement should loop to delete all relevent records
SET @N = 2
SELECT @cnt = COUNT(*) FROM tab1 WHERE col1 = 1
SET @loops = CEILING(@cnt/@N)
WHILE @loops > 0
BEGIN
DELETE TOP (@N) FROM tab1 WHERE col1 = 1
SET @loops = @loops - 1
END
END
-----------------
SELECT * FROM tab1
DROP TABLE tab1
stored procedure to generate customized passwords
CREATE PROCEDURE dbo.uspCreatePassword(
@UpperCaseItems SMALLINT
, @LowerCaseItems SMALLINT
, @NumberItems SMALLINT
, @SpecialItems SMALLINT)
AS
SET NOCOUNT ON
DECLARE @UpperCase VARCHAR(26)
, @LowerCase VARCHAR(26)
, @Numbers VARCHAR(10)
, @Special VARCHAR(13)
, @Temp VARCHAR(8000)
, @Password VARCHAR(8000)
, @i SMALLINT
, @c VARCHAR(1)
, @v TINYINT
-- Set the default items in each group of characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, @LowerCase = 'abcdefghijklmnopqrstuvwxyz'
, @Numbers = '0123456789'
, @Special = '!@#$%&*()_+-='
, @Temp = ''
, @Password = ''
-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
SET @UpperCaseItems = 20
IF @LowerCaseItems > 20
SET @LowerCaseItems = 20
IF @NumberItems > 20
SET @NumberItems = 20
IF @SpecialItems > 20
SET @SpecialItems = 20
-- Get the Upper Case Items
SET @i = ABS(@UpperCaseItems)
WHILE @i > 0 AND LEN(@UpperCase) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1
, @c = SUBSTRING(@UpperCase, @v, 1)
, @UpperCase = CASE
WHEN @UpperCaseItems < 0
THEN STUFF(@UpperCase, @v, 1, '')
ELSE @UpperCase
END
, @Temp = @Temp + @c
, @i = @i - 1
-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems)
WHILE @i > 0 AND LEN(@LowerCase) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1
, @c = SUBSTRING(@LowerCase, @v, 1)
, @LowerCase = CASE
WHEN @LowerCaseItems < 0
THEN STUFF(@LowerCase, @v, 1, '')
ELSE @LowerCase
END
, @Temp = @Temp + @c
, @i = @i - 1
-- Get the Number Items
SET @i = ABS(@NumberItems)
WHILE @i > 0 AND LEN(@Numbers) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1
, @c = SUBSTRING(@Numbers, @v, 1)
, @Numbers = CASE
WHEN @NumberItems < 0
THEN STUFF(@Numbers, @v, 1, '')
ELSE @Numbers
END
, @Temp = @Temp + @c
, @i = @i - 1
-- Get the Special Items
SET @i = ABS(@SpecialItems)
WHILE @i > 0 AND LEN(@Special) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1
, @c = SUBSTRING(@Special, @v, 1)
, @Special = CASE
WHEN @SpecialItems < 0
THEN STUFF(@Special, @v, 1, '')
ELSE @Special
END
, @Temp = @Temp + @c
, @i = @i - 1
-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1
, @Password = @Password + SUBSTRING(@Temp, @v, 1)
, @Temp = STUFF(@Temp, @v, 1, '')
SELECT @Password
@UpperCaseItems SMALLINT
, @LowerCaseItems SMALLINT
, @NumberItems SMALLINT
, @SpecialItems SMALLINT)
AS
SET NOCOUNT ON
DECLARE @UpperCase VARCHAR(26)
, @LowerCase VARCHAR(26)
, @Numbers VARCHAR(10)
, @Special VARCHAR(13)
, @Temp VARCHAR(8000)
, @Password VARCHAR(8000)
, @i SMALLINT
, @c VARCHAR(1)
, @v TINYINT
-- Set the default items in each group of characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
, @LowerCase = 'abcdefghijklmnopqrstuvwxyz'
, @Numbers = '0123456789'
, @Special = '!@#$%&*()_+-='
, @Temp = ''
, @Password = ''
-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
SET @UpperCaseItems = 20
IF @LowerCaseItems > 20
SET @LowerCaseItems = 20
IF @NumberItems > 20
SET @NumberItems = 20
IF @SpecialItems > 20
SET @SpecialItems = 20
-- Get the Upper Case Items
SET @i = ABS(@UpperCaseItems)
WHILE @i > 0 AND LEN(@UpperCase) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@UpperCase) + 1
, @c = SUBSTRING(@UpperCase, @v, 1)
, @UpperCase = CASE
WHEN @UpperCaseItems < 0
THEN STUFF(@UpperCase, @v, 1, '')
ELSE @UpperCase
END
, @Temp = @Temp + @c
, @i = @i - 1
-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems)
WHILE @i > 0 AND LEN(@LowerCase) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@LowerCase) + 1
, @c = SUBSTRING(@LowerCase, @v, 1)
, @LowerCase = CASE
WHEN @LowerCaseItems < 0
THEN STUFF(@LowerCase, @v, 1, '')
ELSE @LowerCase
END
, @Temp = @Temp + @c
, @i = @i - 1
-- Get the Number Items
SET @i = ABS(@NumberItems)
WHILE @i > 0 AND LEN(@Numbers) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Numbers) + 1
, @c = SUBSTRING(@Numbers, @v, 1)
, @Numbers = CASE
WHEN @NumberItems < 0
THEN STUFF(@Numbers, @v, 1, '')
ELSE @Numbers
END
, @Temp = @Temp + @c
, @i = @i - 1
-- Get the Special Items
SET @i = ABS(@SpecialItems)
WHILE @i > 0 AND LEN(@Special) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Special) + 1
, @c = SUBSTRING(@Special, @v, 1)
, @Special = CASE
WHEN @SpecialItems < 0
THEN STUFF(@Special, @v, 1, '')
ELSE @Special
END
, @Temp = @Temp + @c
, @i = @i - 1
-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0
SELECT @v = ABS(CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT)) % LEN(@Temp) + 1
, @Password = @Password + SUBSTRING(@Temp, @v, 1)
, @Temp = STUFF(@Temp, @v, 1, '')
SELECT @Password
Differences Between SQL Server 2000 and SQL Server 2005
The Administrative Differences
Sure we use Management Studio instead of Enterprise Manager, but that's not really a big deal.
Security is one area that is a very nice improvement. The separation of the schema from the owner makes administrative changes easier.
Another big security change is the ability to secure your web services using certificates instead of requiring authentication using a name and password. Add to that the capability to encrypt data, and manage the keys, can make a big difference in the overall security of your data.
There are also online indexes, online restores, and fast recovery in the Enterprise Edition that can help ensure that you take less downtime. Fast recovery especially can be an important feature, allowing the database to be accessed as the undo operations start.
With SQL Server 2000, you were limited to using 2GB of RAM and 4 CPUs on the Standard Edition. The number of CPUs hasn't changed, but you can now use as much RAM as the OS allows. There also is no limit to the database size, not that the 1,048,516 TB in SQL Server 2000.
Summary
Security
In 2000 Owner = Schema, hard to remove old users at times
In 2005, Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates
Encryption
In 2000, No options built in, expensive third party options with proprietary skills required to implement properly.
In 2005, Encryption and key management build in.
High Availability
In 2000, Clustering or Log Shipping require Enterprise Edition. Expensive hardware.
In 2005, Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.
Scalability
2000, Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.
2005 4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.
Sure we use Management Studio instead of Enterprise Manager, but that's not really a big deal.
Security is one area that is a very nice improvement. The separation of the schema from the owner makes administrative changes easier.
Another big security change is the ability to secure your web services using certificates instead of requiring authentication using a name and password. Add to that the capability to encrypt data, and manage the keys, can make a big difference in the overall security of your data.
There are also online indexes, online restores, and fast recovery in the Enterprise Edition that can help ensure that you take less downtime. Fast recovery especially can be an important feature, allowing the database to be accessed as the undo operations start.
With SQL Server 2000, you were limited to using 2GB of RAM and 4 CPUs on the Standard Edition. The number of CPUs hasn't changed, but you can now use as much RAM as the OS allows. There also is no limit to the database size, not that the 1,048,516 TB in SQL Server 2000.
Summary
Security
In 2000 Owner = Schema, hard to remove old users at times
In 2005, Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates
Encryption
In 2000, No options built in, expensive third party options with proprietary skills required to implement properly.
In 2005, Encryption and key management build in.
High Availability
In 2000, Clustering or Log Shipping require Enterprise Edition. Expensive hardware.
In 2005, Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.
Scalability
2000, Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.
2005 4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.
Deleting Duplicate batches of rows
if object_id('tempdb.dbo.##Employee') IS NOT NULL
DROP TABLE dbo.##Employee
if object_id('tempdb.dbo.##Emp1') IS NOT NULL
DROP TABLE dbo.##Emp1
create table dbo.##Employee
(
EmpName varchar(30)
)
insert into ##Employee(EmpName) values('Abc')
insert into ##Employee(EmpName) values('Abc')
insert into ##Employee(EmpName) values('George')
insert into ##Employee(EmpName) values('Thomas')
insert into ##Employee(EmpName) values('Thomas')
insert into ##Employee(EmpName) values('Thomas')
select empname,(count(1)-1) cnt
into ##Emp1
from ##Employee
group by empname
having count(1) > 1
DECLARE @empname varchar(30), @cnt int
DECLARE cur CURSOR FOR select empname,cnt from ##Emp1
OPEN cur
FETCH NEXT FROM cur INTO @empname, @cnt
WHILE @@FETCH_STATUS = 0
BEGIN
delete top (@cnt) from ##Employee where empname = @empname
FETCH NEXT FROM cur INTO @empname, @cnt
END
CLOSE cur
DEALLOCATE cur
select * from ##Employee
DROP TABLE dbo.##Employee
if object_id('tempdb.dbo.##Emp1') IS NOT NULL
DROP TABLE dbo.##Emp1
create table dbo.##Employee
(
EmpName varchar(30)
)
insert into ##Employee(EmpName) values('Abc')
insert into ##Employee(EmpName) values('Abc')
insert into ##Employee(EmpName) values('George')
insert into ##Employee(EmpName) values('Thomas')
insert into ##Employee(EmpName) values('Thomas')
insert into ##Employee(EmpName) values('Thomas')
select empname,(count(1)-1) cnt
into ##Emp1
from ##Employee
group by empname
having count(1) > 1
DECLARE @empname varchar(30), @cnt int
DECLARE cur CURSOR FOR select empname,cnt from ##Emp1
OPEN cur
FETCH NEXT FROM cur INTO @empname, @cnt
WHILE @@FETCH_STATUS = 0
BEGIN
delete top (@cnt) from ##Employee where empname = @empname
FETCH NEXT FROM cur INTO @empname, @cnt
END
CLOSE cur
DEALLOCATE cur
select * from ##Employee
Using the CASE expression instead of dynamic SQL in SQL Server
The CASE expression is a really powerful tool that can you use to solve your SQL Server query problems. You're probably familiar with its use in mimicking if/else processing when issuing SELECT statements. However, its use is not confined strictly to this kind of processing.
Among the ways I've leveraged the CASE expression in my code:
To eliminate a cursor loop when updating rows
To perform specialized processing when using aggregate functions
To create dynamic ORDER BY and WHERE clauses without using dynamic SQL
Ex:
UPDATE dbo.Customer
SET stateDescription = CASE WHEN statecode = 'MA' THEN 'Massachusetts'
WHEN statecode = 'VA' THEN 'Virginia'
WHEN statecode = 'PA' THEN 'Pennsylvania'
ELSE NULL
END
CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4)
AS
SET nocount ON
SELECT customerid, firstname, lastname, statecode, statedescription, totalsales
FROM dbo.Customer
ORDER BY
CASE @sortdirection
WHEN 'asc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
ASC,
CASE @sortdirection
WHEN 'desc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
DESC
GO
Among the ways I've leveraged the CASE expression in my code:
To eliminate a cursor loop when updating rows
To perform specialized processing when using aggregate functions
To create dynamic ORDER BY and WHERE clauses without using dynamic SQL
Ex:
UPDATE dbo.Customer
SET stateDescription = CASE WHEN statecode = 'MA' THEN 'Massachusetts'
WHEN statecode = 'VA' THEN 'Virginia'
WHEN statecode = 'PA' THEN 'Pennsylvania'
ELSE NULL
END
CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4)
AS
SET nocount ON
SELECT customerid, firstname, lastname, statecode, statedescription, totalsales
FROM dbo.Customer
ORDER BY
CASE @sortdirection
WHEN 'asc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
ASC,
CASE @sortdirection
WHEN 'desc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
DESC
GO
Friday, November 28, 2008
Query hints in SQL
Query hints, also referred to as optimizer hints, can dramatically affect the execution of a query, its index choices, as well as its locking behavior. In general SQL Server 2000, will choose the query plan that provides the best possible performance, so using optimizer hints should not be exercised "just in case". Instead, it is recommended that you troubleshoot and examine the query execution, then see if you can modify any of your table / index design to optimize the query. Then, as the last resort of a very troubled query, you can evaluate and test using query hints.
Thursday, November 20, 2008
SQL 2008 Features
1. Plug-in model for SSMS
2. Intellisense
3. Processing of delimited strings
4. Compression.
5. Resource governor
6. Plan freezing
7. C like math syntax
8. Auditing.
9. Inline variable assignment.
2. Intellisense
3. Processing of delimited strings
4. Compression.
5. Resource governor
6. Plan freezing
7. C like math syntax
8. Auditing.
9. Inline variable assignment.
disabling right click
var isnn,isie
if(navigator.appName=='Microsoft Internet Explorer') //check the browser
{ isie=true }
if(navigator.appName=='Netscape')
{ isnn=true }
function right(e) //to trap right click button
{
if (isnn && (e.which == 3 || e.which == 2 ))
return false;
else if (isie && (event.button == 2 || event.button == 3))
{
alert("Sorry, you do not have permission to right click on this page.");
return false;
}
return true;
}
function key(k)
{
if(isie) {
if(event.keyCode==17 || event.keyCode==18 || event.keyCode==93) {
alert("Sorry, you do not have permission to press this key.")
return false;
}
}
if(isnn){
alert("Sorry, you do not have permission to press this key.")
return false; }
}
if (document.layers) window.captureEvents(Event.KEYPRESS);
if (document.layers) window.captureEvents(Event.MOUSEDOWN);
if (document.layers) window.captureEvents(Event.MOUSEUP);
document.onkeydown=key;
document.onmousedown=right;
document.onmouseup=right;
window.document.layers=right;
Put this code in a file called security.js and reference it between the HEAD tags of any html or asp page using:
if(navigator.appName=='Microsoft Internet Explorer') //check the browser
{ isie=true }
if(navigator.appName=='Netscape')
{ isnn=true }
function right(e) //to trap right click button
{
if (isnn && (e.which == 3 || e.which == 2 ))
return false;
else if (isie && (event.button == 2 || event.button == 3))
{
alert("Sorry, you do not have permission to right click on this page.");
return false;
}
return true;
}
function key(k)
{
if(isie) {
if(event.keyCode==17 || event.keyCode==18 || event.keyCode==93) {
alert("Sorry, you do not have permission to press this key.")
return false;
}
}
if(isnn){
alert("Sorry, you do not have permission to press this key.")
return false; }
}
if (document.layers) window.captureEvents(Event.KEYPRESS);
if (document.layers) window.captureEvents(Event.MOUSEDOWN);
if (document.layers) window.captureEvents(Event.MOUSEUP);
document.onkeydown=key;
document.onmousedown=right;
document.onmouseup=right;
window.document.layers=right;
Put this code in a file called security.js and reference it between the HEAD tags of any html or asp page using:
Email Validation
function emailCheck(str) {
var at="@"
var dot="."
var lat=str.indexOf(at)
var lstr=str.length
var ldot=str.indexOf(dot)
if (str.indexOf(at)==-1){
alert("Invalid E-mail ID")
return false
}
if (str.indexOf(at)==-1 || str.indexOf(at)==0 || str.indexOf(at)==lstr){
alert("Invalid E-mail ID")
return false
}
if (str.indexOf(dot)==-1 || str.indexOf(dot)==0 || str.indexOf(dot)==lstr){
alert("Invalid E-mail ID")
return false
}
if (str.indexOf(at,(lat+1))!=-1){
alert("Invalid E-mail ID")
return false
}
if (str.substring(lat-1,lat)==dot || str.substring(lat+1,lat+2)==dot){
alert("Invalid E-mail ID")
return false
}
if (str.indexOf(dot,(lat+2))==-1){
alert("Invalid E-mail ID")
return false
}
if (str.indexOf(" ")!=-1){
alert("Invalid E-mail ID")
return false
}
return true
}
var at="@"
var dot="."
var lat=str.indexOf(at)
var lstr=str.length
var ldot=str.indexOf(dot)
if (str.indexOf(at)==-1){
alert("Invalid E-mail ID")
return false
}
if (str.indexOf(at)==-1 || str.indexOf(at)==0 || str.indexOf(at)==lstr){
alert("Invalid E-mail ID")
return false
}
if (str.indexOf(dot)==-1 || str.indexOf(dot)==0 || str.indexOf(dot)==lstr){
alert("Invalid E-mail ID")
return false
}
if (str.indexOf(at,(lat+1))!=-1){
alert("Invalid E-mail ID")
return false
}
if (str.substring(lat-1,lat)==dot || str.substring(lat+1,lat+2)==dot){
alert("Invalid E-mail ID")
return false
}
if (str.indexOf(dot,(lat+2))==-1){
alert("Invalid E-mail ID")
return false
}
if (str.indexOf(" ")!=-1){
alert("Invalid E-mail ID")
return false
}
return true
}
allow only one space between to words
function checkSpace()
{
var flg=1;
var txt = document.getElementById('TextBox1').value;
var strArr = new Array();
strArr = txt.split(" ");
for(var i = 0; i < strArr.length ; i++)
{
if(strArr[i] == "")
{
alert("Only one space is allowed between words");
flg=0;
break;
}
}
{
var flg=1;
var txt = document.getElementById('TextBox1').value;
var strArr = new Array();
strArr = txt.split(" ");
for(var i = 0; i < strArr.length ; i++)
{
if(strArr[i] == "")
{
alert("Only one space is allowed between words");
flg=0;
break;
}
}
Friday, September 26, 2008
Stored Procedures
A stored procedure is a procedure (like a subprogram in a regular computing language) that is stored (in the database). Correctly speaking, MySQL supports "routines" and there are two kinds of routines: stored procedures which you call, or functions whose return values you use in other SQL statements the same way that you use pre-installed MySQL functions like pi().
A stored procedure has a name, a parameter list, and an SQL statement, which can contain many more SQL statements. There is new syntax for local variables, error handling, loop control, and IF conditions. Here is an example of a statement that creates a stored procedure.
CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF parameter1 = 17 THEN /* start of IF */
SET variable1 = 'birds'; /* assignment */
ELSE
SET variable1 = 'beasts'; /* assignment */
END IF; /* end of IF */
INSERT INTO table1 VALUES (variable1);/* statement */
END /* end of block */
Stored procedures are something new for MySQL, so naturally you'll approach them with some caution. Stored procedures are fast! What we can say is that the MySQL server takes some advantage of caching, just as prepared statements do. There is no compilation, so an SQL stored procedure won't work as quickly as a procedure written with an external language such as C. The main speed gain comes from reduction of network traffic. If you have a repetitive task that requires checking, looping, multiple statements, and no user interaction, do it with a single call to a procedure that's stored on the server.
Stored procedures are components! Suppose that you change your host language -- no
problem, the logic is in the database not the application.
Stored procedures are portable! When you write your stored procedure in SQL, you know that it will run on every platform that MySQL runs on, without obliging you to install an additional runtime-environment package, or set permissions for program execution in the operating system, or deploy different packages if you have different computer types. That's the advantage of writing in SQL rather than in an external language like Java or C or PHP.
Stored procedures are stored! If you write a procedure with the right naming
conventions, for example saying chequing_withdrawal for a bank transaction, then people who want to know about chequing can find your procedure. It's always available as 'source code' in the database itself. And it makes sense to link the data with the processes that operate on the data, as you might have heard in your programming-theory classes.
Stored procedures are migratory! MySQL adheres fairly closely to the SQL:2003
standard. Others (DB2, Mimer) also adhere. Others (Oracle, SQL Server) don't adhere
but I'll be providing tips and tools that make it easier to take code written for another DBMS and plunking it into MySQL.
CREATE PROCEDURE p1 () SELECT * FROM t;
CREATE PROCEDURE p2 ()
SELECT CURRENT_DATE, RAND() FROM t
Call Procedure
Now, to call a procedure, all you have to do is enter the word CALL and then the name of the procedure and then the parentheses. Again, the parentheses are compulsory.
CALL p1()
A stored procedure has a name, a parameter list, and an SQL statement, which can contain many more SQL statements. There is new syntax for local variables, error handling, loop control, and IF conditions. Here is an example of a statement that creates a stored procedure.
CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF parameter1 = 17 THEN /* start of IF */
SET variable1 = 'birds'; /* assignment */
ELSE
SET variable1 = 'beasts'; /* assignment */
END IF; /* end of IF */
INSERT INTO table1 VALUES (variable1);/* statement */
END /* end of block */
Stored procedures are something new for MySQL, so naturally you'll approach them with some caution. Stored procedures are fast! What we can say is that the MySQL server takes some advantage of caching, just as prepared statements do. There is no compilation, so an SQL stored procedure won't work as quickly as a procedure written with an external language such as C. The main speed gain comes from reduction of network traffic. If you have a repetitive task that requires checking, looping, multiple statements, and no user interaction, do it with a single call to a procedure that's stored on the server.
Stored procedures are components! Suppose that you change your host language -- no
problem, the logic is in the database not the application.
Stored procedures are portable! When you write your stored procedure in SQL, you know that it will run on every platform that MySQL runs on, without obliging you to install an additional runtime-environment package, or set permissions for program execution in the operating system, or deploy different packages if you have different computer types. That's the advantage of writing in SQL rather than in an external language like Java or C or PHP.
Stored procedures are stored! If you write a procedure with the right naming
conventions, for example saying chequing_withdrawal for a bank transaction, then people who want to know about chequing can find your procedure. It's always available as 'source code' in the database itself. And it makes sense to link the data with the processes that operate on the data, as you might have heard in your programming-theory classes.
Stored procedures are migratory! MySQL adheres fairly closely to the SQL:2003
standard. Others (DB2, Mimer) also adhere. Others (Oracle, SQL Server) don't adhere
but I'll be providing tips and tools that make it easier to take code written for another DBMS and plunking it into MySQL.
CREATE PROCEDURE p1 () SELECT * FROM t;
CREATE PROCEDURE p2 ()
SELECT CURRENT_DATE, RAND() FROM t
Call Procedure
Now, to call a procedure, all you have to do is enter the word CALL and then the name of the procedure and then the parentheses. Again, the parentheses are compulsory.
CALL p1()
Thursday, September 25, 2008
Cartesian product
Simply, it is a join without a Where clause. It gives you every row in the
first table, joined with every row in the second table.
SELECT * FROM AntiqueOwners, Orders;
first table, joined with every row in the second table.
SELECT * FROM AntiqueOwners, Orders;
schema
A schema is a logical set of tables, such as the Antiques database above...usually,
it is thought of as simply "the database", but a database can hold more than one schema. For example, a star schema is a set of tables where one large, central table holds all of the important information, and is linked, via foreign keys, to dimension tables which hold detail information, and can be used in a join to
create detailed reports.
it is thought of as simply "the database", but a database can hold more than one schema. For example, a star schema is a set of tables where one large, central table holds all of the important information, and is linked, via foreign keys, to dimension tables which hold detail information, and can be used in a join to
create detailed reports.
General tips you would give to make my SQL queries and databases better and faster
You should try, if you can, to avoid expressions in Selects, such as SELECT ColumnA +
ColumnB, etc. The query optimizer of the database, the portion of the DBMS that determines the best way to get the required data out of the database itself, handles expressions in such a way that would normally require more time to retrieve the data than if columns were normally selected, and the expression itself handled programmatically.
Minimize the number of columns included in a Group By clause.
If you are using a join, try to have the columns joined on (from both tables) indexed.
When in doubt, index.
Unless doing multiple counts or a complex query, use COUNT(*) (the number of rows
generated by the query) rather than COUNT(Column_Name)
ColumnB, etc. The query optimizer of the database, the portion of the DBMS that determines the best way to get the required data out of the database itself, handles expressions in such a way that would normally require more time to retrieve the data than if columns were normally selected, and the expression itself handled programmatically.
Minimize the number of columns included in a Group By clause.
If you are using a join, try to have the columns joined on (from both tables) indexed.
When in doubt, index.
Unless doing multiple counts or a complex query, use COUNT(*) (the number of rows
generated by the query) rather than COUNT(Column_Name)
Monday, September 22, 2008
SQl Limitation
The limitation for the total number of objects in a database is 2,147,483,647. This includes all the objects (Tables, views, functions, stored procedures etc).
COALESCE
Returns the first nonnull expression among its arguments.
COALESCE ( expression [ ,...n ] )
If all arguments are NULL, COALESCE returns NULL.
ISNULL() accepts exactly two parameters. If you want to take the first non-NULL among more than two values, you will need to nest your ISNULL() statements.
COALESCE(), on the other hand, can take multiple inputs.
SQL Server views a column created by COALESCE() as nullable, whereas one using ISNULL() is not.
Many uses of Coalesce
http://www.mssqltips.com/tip.asp?tip=1521
COALESCE ( expression [ ,...n ] )
If all arguments are NULL, COALESCE returns NULL.
ISNULL() accepts exactly two parameters. If you want to take the first non-NULL among more than two values, you will need to nest your ISNULL() statements.
COALESCE(), on the other hand, can take multiple inputs.
SQL Server views a column created by COALESCE() as nullable, whereas one using ISNULL() is not.
Many uses of Coalesce
http://www.mssqltips.com/tip.asp?tip=1521
Convert vbscript code to Javascript
http://slingfive.com/pages/code/scriptConverter/demo.html
http://slingfive.com/pages/code/scriptConverter/
http://slingfive.com/pages/code/scriptConverter/
How to get a list of all user defined functions (along with source code) existing in an SQL Server 2000 database using JavaScript
http://www.devarticles.com/c/a/JavaScript/Digging-More-into-SQL-Server-2000-Using-Clientside-JavaScript/
http://www.devarticles.com/c/a/JavaScript/Digging-More-into-SQL-Server-2000-Using-Clientside-JavaScript/1
http://www.devarticles.com/c/a/JavaScript/Digging-More-into-SQL-Server-2000-Using-Clientside-JavaScript/1
Sunday, August 24, 2008
@@TRANCOUNT
This variable returns the number of active transactions for the current connection.
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1.
ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT.
COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1.
ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT.
COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.
OPENQUERY command and what all parameters you pass
OpenQuery Executes the specified pass-through query on the given linked server, which is an OLE DB data source
OPENQUERY ( linked_server , 'query' )
Example: EXEC sp_addlinkedserver 'OracleSvr', 'Oracle 7.3', 'MSDAORA', 'ORCLDB'
GO
SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
OPENQUERY ( linked_server , 'query' )
Example: EXEC sp_addlinkedserver 'OracleSvr', 'Oracle 7.3', 'MSDAORA', 'ORCLDB'
GO
SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
Linked Servers
A linked server is a virtual server that has been defined to SQL Server with all the information needed to access an OLE DB data source.
A linked server name is defined using the sp_addlinkedserver system stored procedure. The linked server definition contains all the information needed to locate the OLE DB data source. Local SQL Server logins are then mapped to logins in the linked server using sp_addlinkedsrvlogin.
Remote tables can then be referenced by using the linked server name as the server name in a four-part name used as a table or view reference in a Transact-SQL statement. The other three parts of the name reference an object in the linked server that is exposed as a row set.
Remote tables can then be referenced by using the linked server as an input parameter to an OPENQUERY function. OPENQUERY sends the OLE DB provider a command to execute. The returned row set can then be used as a table or view reference in a Transact-SQL statement.
A linked server name is defined using the sp_addlinkedserver system stored procedure. The linked server definition contains all the information needed to locate the OLE DB data source. Local SQL Server logins are then mapped to logins in the linked server using sp_addlinkedsrvlogin.
Remote tables can then be referenced by using the linked server name as the server name in a four-part name used as a table or view reference in a Transact-SQL statement. The other three parts of the name reference an object in the linked server that is exposed as a row set.
Remote tables can then be referenced by using the linked server as an input parameter to an OPENQUERY function. OPENQUERY sends the OLE DB provider a command to execute. The returned row set can then be used as a table or view reference in a Transact-SQL statement.
Steps to avoid drop a table
DROP TABLE permissions default to the table owner, and are not transferable. However, members of the sysadmin fixed server role or the db_owner and db_dlladmin fixed database roles can drop any object by specifying the owner in the DROP TABLE statement.
If even the above should not be able to delete a table, make the database as read only
EXEC sp_dboption 'databse_name', 'read only', 'TRUE'
Remove all the users accounts from db_ddladmin role. Even this can’t stop members of sysadmin fixes server role to drop the table.
If even the above should not be able to delete a table, make the database as read only
EXEC sp_dboption 'databse_name', 'read only', 'TRUE'
Remove all the users accounts from db_ddladmin role. Even this can’t stop members of sysadmin fixes server role to drop the table.
Error handling in stored procedure
Using @@Error the error number of last transact SQL statement executed can be found. If the statement is executed successfully the value is set to 0. If any error occurred then @@ERROR returns the number of the error message.
Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.
Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.
Error handling in stored procedure
Using @@Error the error number of last transact SQL statement executed can be found. If the statement is executed successfully the value is set to 0. If any error occurred then @@ERROR returns the number of the error message.
Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.
Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.
Concept of NO LOCK
Nolock does not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
Concept of NO LOCK
Nolock does not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
Difference between EXEC and sp_executesql stored procedure
· sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation.
· Because the Transact-SQL statement itself remains constant and only the parameter values change, SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
· Because the Transact-SQL statement itself remains constant and only the parameter values change, SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
Difference between EXEC and sp_executesql stored procedure
· sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation.
· Because the Transact-SQL statement itself remains constant and only the parameter values change, SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
· Because the Transact-SQL statement itself remains constant and only the parameter values change, SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
Shared Lock
Shared locks allow concurrent transactions to read a resource. No other transactions can modify the data while shared locks exist on the resource. Shared locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared locks for the duration of the transaction. Used for operations that do not change or update data (read-only operations), such as a SELECT statement.
Concept of Locking
A lock is an object used to indicate that a user/process has some dependency on a resource. Placing a lock on a resource is called locking.
· Locking is used to ensure transactional integrity and database consistency.
· Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time.
· If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results.
· SQL Server locks are applied at various levels of granularity in the database. Locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases.
· SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement. The level at which locks are acquired can vary for different objects referenced by the same query.
Thursday, August 21, 2008
Get all the tablename and columnname of a database
Think that database name is dbEmplyee
use dbEmplyee
select table_name,column_name from INFORMATION_SCHEMA.COLUMNS
Hope this helps you
Kumar
use dbEmplyee
select table_name,column_name from INFORMATION_SCHEMA.COLUMNS
Hope this helps you
Kumar
Friday, August 15, 2008
Display More than one month in Windows Month Calender
The MonthCalender control of Windows form by default displays one month at a time . if the client require us to display 2 months by default, then what to do.
Simple we have to set the CalenderDimensions to the MonthCalender
The below example will show how to display 2 months at a time by default in the MonthCalender control
monthCalendar1.CalendarDimensions = new System.Drawing.Size(2, 1);
Hope this may help you
Kumar
Simple we have to set the CalenderDimensions to the MonthCalender
The below example will show how to display 2 months at a time by default in the MonthCalender control
monthCalendar1.CalendarDimensions = new System.Drawing.Size(2, 1);
Hope this may help you
Kumar
Thursday, August 14, 2008
How to check entered value in textbox is numeric or not
There are number of ways to check whether the string that has entered in the textbox is numeric or not
1. using int.Parse
Ex:
function bool stringIsNumericOrNot(string txtBoxValue)
{
bool isNumericOrNot = true;
try
{
int.Parse(TextBox1.Text);
}
catch
{
isNumericOrNot = false;
}
return isNumericOrNot;
}
2. Using Regular Expression
public bool IsNumberOrNot(String strNumber)
{
Regex regNumericPattern= new Regex("[^0-9]");
return !regNumericPattern.IsMatch(strNumber);
}
Hope this may help you
Kumar
1. using int.Parse
Ex:
function bool stringIsNumericOrNot(string txtBoxValue)
{
bool isNumericOrNot = true;
try
{
int.Parse(TextBox1.Text);
}
catch
{
isNumericOrNot = false;
}
return isNumericOrNot;
}
2. Using Regular Expression
public bool IsNumberOrNot(String strNumber)
{
Regex regNumericPattern= new Regex("[^0-9]");
return !regNumericPattern.IsMatch(strNumber);
}
Hope this may help you
Kumar
Replace the existing string with the new string
function replaceAllString( str, from, to )
{
var idx = str.indexOf( from );
while ( idx > -1 )
{
str = str.replace( from, to );
idx = str.indexOf( from );
}
}
if you want to replace all commas (,) in a string then the above function will work in javascript
example if the textbox value 1,24,573.00 and while storing into the database if should be just like 124573.00 then you have to replace all commas, so for that the user has to write
document.getElementById("txtBoxId").value = replaceAllStrings(document.getElementById("txtBoxId").value,",","");
Hope this helps you
Kumar
{
var idx = str.indexOf( from );
while ( idx > -1 )
{
str = str.replace( from, to );
idx = str.indexOf( from );
}
}
if you want to replace all commas (,) in a string then the above function will work in javascript
example if the textbox value 1,24,573.00 and while storing into the database if should be just like 124573.00 then you have to replace all commas, so for that the user has to write
document.getElementById("txtBoxId").value = replaceAllStrings(document.getElementById("txtBoxId").value,",","");
Hope this helps you
Kumar
Adding Commas to the Control javascript
function addingCommasToControl(stringVariable)
{
stringVariable += '';
k = stringVariable.split('.');
k1 = k[0];
k2 = k.length > 1 ? '.' + k[1] : '';
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) {
k1 = k1.replace(rgx, '$1' + ',' + '$2');
}
return k1 + k2;
}
The above function will add commas to the control
Example :
Name : txtPrice
Control : TextBox
so in the html or in design part of .net you can write as
onblur of the txtPrice textbox the user can able to call the addingCommasToControl() function
the parameter the user should pass is the amount
so when the user enters 124573.00
then on blur of the textbox the output will be displayed in the textbox is 1,24,573.00
Hope this may helps you
Kumar
{
stringVariable += '';
k = stringVariable.split('.');
k1 = k[0];
k2 = k.length > 1 ? '.' + k[1] : '';
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) {
k1 = k1.replace(rgx, '$1' + ',' + '$2');
}
return k1 + k2;
}
The above function will add commas to the control
Example :
Name : txtPrice
Control : TextBox
so in the html or in design part of .net you can write as
onblur of the txtPrice textbox the user can able to call the addingCommasToControl() function
the parameter the user should pass is the amount
so when the user enters 124573.00
then on blur of the textbox the output will be displayed in the textbox is 1,24,573.00
Hope this may helps you
Kumar
Tuesday, August 12, 2008
NameValueCollection Class
Defination : NameValueCollection Class is collection of keys and values that can be accessed either with keys or by index.
Namespace : System.Collections.Specialized
Description: It can able to store multiple string values under single key
Below Code Snippet will describe this clearly.
NameValueCollection keyValue = new NameValueCollection();
keyValue.Add("A","Apples");
keyValue.Add("B","Banana");
keyValue.Add("C","Cat") ;
keyValue.Add("A","Ant");
Response.Write("Key Value\n");
foreach(string strNameValue in keyValue.AllKeys)
{
Response.Write(strNameValue + " " + keyValue[strNameValue]);
Response.Write("\n");
}
//Output will be
Key Value
A Apple,Ant
B Banana
C Cat
Hope this article will help you
Kumar
Namespace : System.Collections.Specialized
Description: It can able to store multiple string values under single key
Below Code Snippet will describe this clearly.
NameValueCollection keyValue = new NameValueCollection();
keyValue.Add("A","Apples");
keyValue.Add("B","Banana");
keyValue.Add("C","Cat") ;
keyValue.Add("A","Ant");
Response.Write("Key Value\n");
foreach(string strNameValue in keyValue.AllKeys)
{
Response.Write(strNameValue + " " + keyValue[strNameValue]);
Response.Write("\n");
}
//Output will be
Key Value
A Apple,Ant
B Banana
C Cat
Hope this article will help you
Kumar
Recursively calculate Fibonacci series
static int calFibnocci(int k)
{
if(k<=1)
{
return 1
}
else
{
return calFibnocci(k-1) + calFibnocci(k-2);
}
}
how to call this function
calFibnocci(3)
Hope this help you
Kumar
{
if(k<=1)
{
return 1
}
else
{
return calFibnocci(k-1) + calFibnocci(k-2);
}
}
how to call this function
calFibnocci(3)
Hope this help you
Kumar
Wednesday, August 6, 2008
Arraylist
Arraylist implements from Ilist Interface using array and can be increased dynamically.
Arraylist uses the namespace System.Collections.
The Capacity property of Arraylist holds number of elements in the Arraylist. With the help of Add method you can able to add an item in the Array list.
C# Code
----------------------------------------------------
Arraylist arrList = new Arraylist();
arrList.Add("Kumar");
arrList.Add("Venky");
-----------------------------------------------------
As the elements are added to a Arraylist, the capacity is increased automatically through rellocation.It can be decreased with the help of TrimToSize()
Arraylist allows duplicate elements and accepts null value.
You must sort the Arraylist explicitly before performing operation such as Binary search for which sorting is needed.
IsFixedSize property gets a value indicating whether ArrayList object has a fixed size.
Don't mix the content of the arraylist. Below example helps you to understand
------------------
C# Code
ArrayList contentMixing = new ArrayList();
//Adding some numbers to the list
contentMixing.Add(10);
contentMixing.Add(20);
//Adding some text to the list
contentMixing.Add("Kumar");
contentMixing.Add("Venky");
//While looping gives error
foreach(string mixedText as contentMixing)
{
//Throws exception
}
------------------
Kumar
Arraylist uses the namespace System.Collections.
The Capacity property of Arraylist holds number of elements in the Arraylist. With the help of Add method you can able to add an item in the Array list.
C# Code
----------------------------------------------------
Arraylist arrList = new Arraylist();
arrList.Add("Kumar");
arrList.Add("Venky");
-----------------------------------------------------
As the elements are added to a Arraylist, the capacity is increased automatically through rellocation.It can be decreased with the help of TrimToSize()
Arraylist allows duplicate elements and accepts null value.
You must sort the Arraylist explicitly before performing operation such as Binary search for which sorting is needed.
IsFixedSize property gets a value indicating whether ArrayList object has a fixed size.
Don't mix the content of the arraylist. Below example helps you to understand
------------------
C# Code
ArrayList contentMixing = new ArrayList();
//Adding some numbers to the list
contentMixing.Add(10);
contentMixing.Add(20);
//Adding some text to the list
contentMixing.Add("Kumar");
contentMixing.Add("Venky");
//While looping gives error
foreach(string mixedText as contentMixing)
{
//Throws exception
}
------------------
Kumar
Sunday, July 27, 2008
Value type Vs Reference Type
Value Type
- It stores value directly.
- It consists of two categories, Structs, Enumerations
- It cannot assign to a null value.
- Each instance of value type separate memory is allocated.
- Stored in stack
- It stores reference to value.
- It consists of Class, interfaces, delegates.
- It can assign a null value.
- It utilizes the same memory location
- Stored in heap, by doing this it allows object to be allocated or deallocated in random order at the same time it has an overhead of a memory manager and garbage collector to remove the objects that has been not used for long time.
Techno Zone
Friday, June 13, 2008
Creating batch files in SQL
Using SQLCMD to execute script files easily in different environments
Step 1: Lets create few dummy script files for demo purpose.File1: 01TableCreation.sqlCreate table tblTest(Sno int identity,FName varchar(20))GoFile2: 02InsertRecords.sqlset nocount onInsert into tblTest (Fname) values ('alpha')Insert into tblTest (Fname) values ('beta')File3: 03StoredProcedures.sqlCreate proc usp_GetAllTblTestasSelect sno, fname from tblTestgo
Step 2: Create a batch file and call these .sql files in order.File4: DBInstallationScripts.batsqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\01TableCreation.sql"sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\02InsertRecords.sql"sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\03StoredProcedures.sql"
Step 3: Execute the batch fileFrom the command prompt (Start >> Run >> Cmd) do the following:c:> DBInstallationScripts DBUserName DBPassword DBServerName DatabasenamePlease note we are passing the database username, password, servername and the database to which we need to connect from the batch file. That way it would replace %1, %2, %3 and %4 in the batch file while executing.
Same set of scripts can be executed in different environments like 'Development', 'Testing', 'Production' etc., with ease.
Step 1: Lets create few dummy script files for demo purpose.File1: 01TableCreation.sqlCreate table tblTest(Sno int identity,FName varchar(20))GoFile2: 02InsertRecords.sqlset nocount onInsert into tblTest (Fname) values ('alpha')Insert into tblTest (Fname) values ('beta')File3: 03StoredProcedures.sqlCreate proc usp_GetAllTblTestasSelect sno, fname from tblTestgo
Step 2: Create a batch file and call these .sql files in order.File4: DBInstallationScripts.batsqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\01TableCreation.sql"sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\02InsertRecords.sql"sqlcmd -U %1 -P %2 -S %3 -d %4 -i "C:\Vadivel\SQL Related\Scripts\sqlcmd1\03StoredProcedures.sql"
Step 3: Execute the batch fileFrom the command prompt (Start >> Run >> Cmd) do the following:c:> DBInstallationScripts DBUserName DBPassword DBServerName DatabasenamePlease note we are passing the database username, password, servername and the database to which we need to connect from the batch file. That way it would replace %1, %2, %3 and %4 in the batch file while executing.
Same set of scripts can be executed in different environments like 'Development', 'Testing', 'Production' etc., with ease.
Thursday, June 5, 2008
Virtual Table
· Views are called virtual tables because the result set of a view is us not usually saved in the database.
· The result set for a view is dynamically incorporated into the logic of the statement and the result set is built dynamically at run time.
· What is stored in the database is a SELECT statement.
· The result set of the SELECT statement forms the virtual table returned by the view.
· A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.
· A view is used to do any or all of these functions:
Ø Restrict a user to specific rows in a table.
Ø Restrict a user to specific columns.
Ø Join columns from multiple tables so that they look like a single table.
Ø Aggregate information instead of supplying details.
· The result set for a view is dynamically incorporated into the logic of the statement and the result set is built dynamically at run time.
· What is stored in the database is a SELECT statement.
· The result set of the SELECT statement forms the virtual table returned by the view.
· A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.
· A view is used to do any or all of these functions:
Ø Restrict a user to specific rows in a table.
Ø Restrict a user to specific columns.
Ø Join columns from multiple tables so that they look like a single table.
Ø Aggregate information instead of supplying details.
Indexes
An index in a database is a list of values in a table with the storage locations of rows in the table that contain each value. In a database, an index allows the database program to find data in a table without scanning the entire table.Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees. An index contains an entry with one or more columns (the search key) from each row in a table. A B-tree is sorted on the search key, and can be searched efficiently on any leading subset of the search key. For example, an index on columns A, B, C can be searched efficiently on A, on A, B, and A, B, C. The performance benefits of indexes, however, do come with a cost. Tables with indexes require more storage space in the database. Also, commands that insert, update, or delete data can take longer and require more processing time to maintain the indexes.
When you design and create indexes, you should ensure that the performance benefits outweigh the extra cost in storage space and processing resources.
When you design and create indexes, you should ensure that the performance benefits outweigh the extra cost in storage space and processing resources.
Cursors, How to use them and when to use
· A cursor is a mechanism you can use to fetch rows one at a time.
· Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements.
· When writing code for a transaction where the result set includes several rows of data, you may declare and use a cursor.
· For example, if you write code that includes a SELECT statement or stored procedure that returns multiple rows, you must declare a cursor and associate it with the SELECT statement. Then, by using the FETCH statement, you can retrieve one row at a time from the result set.
The typical process for using a Transact-SQL cursor in a stored procedure or trigger is:
· Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column and with a data type that can be implicitly converted from the data type of the column.
· Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
· Use the OPEN statement to execute the SELECT statement and populate the cursor.
· Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.
· When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor's result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is de-allocated, you must issue a DECLARE statement to rebuild the cursor.
Disadvantages of cursors
· Each time a row is fetched from the cursor, it results in a network roundtrip; where as a normal SELECT query makes only one roundtrip, however large the result set is.
· Cursors are also costly because they require more resources and temporary storage (results in more IO operations).
· Further, there are restrictions on the SELECT statements that can be used with some types of cursors.
· Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts in which they make the contents of a result set available to other Transact-SQL statements.
· When writing code for a transaction where the result set includes several rows of data, you may declare and use a cursor.
· For example, if you write code that includes a SELECT statement or stored procedure that returns multiple rows, you must declare a cursor and associate it with the SELECT statement. Then, by using the FETCH statement, you can retrieve one row at a time from the result set.
The typical process for using a Transact-SQL cursor in a stored procedure or trigger is:
· Declare Transact-SQL variables to contain the data returned by the cursor. Declare one variable for each result set column. Declare the variables to be large enough to hold the values returned by the column and with a data type that can be implicitly converted from the data type of the column.
· Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement. The DECLARE CURSOR statement also defines the characteristics of the cursor, such as the cursor name and whether the cursor is read-only or forward-only.
· Use the OPEN statement to execute the SELECT statement and populate the cursor.
· Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable. Other Transact-SQL statements can then reference those variables to access the fetched data values. Transact-SQL cursors do not support fetching blocks of rows.
· When you are finished with the cursor, use the CLOSE statement. Closing a cursor frees some resources, such as the cursor's result set and its locks on the current row, but the cursor structure is still available for processing if you reissue an OPEN statement. Because the cursor is still present, you cannot reuse the cursor name at this point. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name. After a cursor is de-allocated, you must issue a DECLARE statement to rebuild the cursor.
Disadvantages of cursors
· Each time a row is fetched from the cursor, it results in a network roundtrip; where as a normal SELECT query makes only one roundtrip, however large the result set is.
· Cursors are also costly because they require more resources and temporary storage (results in more IO operations).
· Further, there are restrictions on the SELECT statements that can be used with some types of cursors.
Wednesday, June 4, 2008
@@DATEFIRST function in SQL Server
@@DATEFIRST in SQL Server will returns the current value, for the session, of SET DATEFIRST.SET DATEFIRST indicates the specified first day of each week.
The U.S. English default is 7, Sunday.
Language settings affect date information. In the following example, the language is first set to italian.
SELECT @@DATEFIRST returns 1.
The language is then set to us_english.
SELECT @@DATEFIRST returns 7.
The U.S. English default is 7, Sunday.
Language settings affect date information. In the following example, the language is first set to italian.
SELECT @@DATEFIRST returns 1.
The language is then set to us_english.
SELECT @@DATEFIRST returns 7.
Get the size of the database
SELECT table_schema "Database", sum( data_length + index_length ) / 1024 / 1024 "Size (MB)", sum( data_free )/ 1024 / 1024 "Free (MB)" FROM information_schema.TABLES GROUP BY table_schema ;
Check for Duplicate Records in SQL Server
SELECT email,COUNT(email) AS NumOccurrencesFROM usersGROUP BY emailHAVING ( COUNT(email) > 1 )
Date Functions
CURRENT_DATE ( ) Returns the current date.
CURRENT_TIME () Returns the current local time.
CURRENT_TIMESTAMP () Returns the current local date and local time as a timestamp value.
CURDATE ( ) Returns the current date.
CURTIME ( ) Returns the current local time.
DAYNAME (date_exp) Returns a character string containing the data source/specific name of the day (for example, Sunday through Saturday).
DAYOFMONTH (date_exp) Returns the day of the month based on the month field in date_exp as an integer value in the range of 1-31.
DAYOFWEEK (date_exp) Returns the day of the week based on the week field in date_exp as an integer value in the range of 1-7, where 1 represents Sunday.
DAYOFYEAR(date_exp) Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366.
EXTRACT (extract-field FROM extract-source) Returns the extract-field portion of the extract-source. The extract-source argument is a datetime or interval expression.
HOUR (time_exp) Returns the hour based on the hour field in time_exp as an integer value in the range of 0-23.
MINUTE (time_exp) Returns the minute based on the minute field in time_exp as an integer value in the range of 0-59.
MONTH (date_exp) Returns the month based on the month field in date_exp as an integer value in the range of 1-12.
MONTHNAME(date_exp) Returns a character string containing the data source/specific name of the month (for example, January through December). Currently only supports English locale.
NOW ( ) Returns current date and time as a timestamp value.
QUARTER(date_exp) Returns the quarter in date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31.
SECOND (time_exp) Returns the second based on the second field in time_exp as an integer value in the range of 0-59.
CURRENT_TIME () Returns the current local time.
CURRENT_TIMESTAMP () Returns the current local date and local time as a timestamp value.
CURDATE ( ) Returns the current date.
CURTIME ( ) Returns the current local time.
DAYNAME (date_exp) Returns a character string containing the data source/specific name of the day (for example, Sunday through Saturday).
DAYOFMONTH (date_exp) Returns the day of the month based on the month field in date_exp as an integer value in the range of 1-31.
DAYOFWEEK (date_exp) Returns the day of the week based on the week field in date_exp as an integer value in the range of 1-7, where 1 represents Sunday.
DAYOFYEAR(date_exp) Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366.
EXTRACT (extract-field FROM extract-source) Returns the extract-field portion of the extract-source. The extract-source argument is a datetime or interval expression.
HOUR (time_exp) Returns the hour based on the hour field in time_exp as an integer value in the range of 0-23.
MINUTE (time_exp) Returns the minute based on the minute field in time_exp as an integer value in the range of 0-59.
MONTH (date_exp) Returns the month based on the month field in date_exp as an integer value in the range of 1-12.
MONTHNAME(date_exp) Returns a character string containing the data source/specific name of the month (for example, January through December). Currently only supports English locale.
NOW ( ) Returns current date and time as a timestamp value.
QUARTER(date_exp) Returns the quarter in date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31.
SECOND (time_exp) Returns the second based on the second field in time_exp as an integer value in the range of 0-59.
Difference between two dates in T-SQL
for day datediff(days,[column_name1],[column_name2])
for month datediff(monts,[column_name1],[column_name2])
for year datediff(year,[column_name1],[column_name2])
for month datediff(monts,[column_name1],[column_name2])
for year datediff(year,[column_name1],[column_name2])
Index Optimization tips
• Every index increases the time in takes to perform INSERTS, UPDATESand DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.
• Keep your indexes as narrow as possible. This reduces the size ofthe index and reduces the number of reads required to read the index.
• Try to create indexes on columns that have integer values rather than character values.
• If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the left most of the key.
• If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.
• Create surrogate integer primary key (identity for example) if yourtable will not have many insert operations.
• Clustered indexes are more preferable than nonclustered, if you needto select by a range of values or you need to sort results set withGROUP BY or ORDER BY.
• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
• You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables inyour database may need indexes. This trace will show which tables arebeing scanned by queries instead of using an index.
• You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.
• Keep your indexes as narrow as possible. This reduces the size ofthe index and reduces the number of reads required to read the index.
• Try to create indexes on columns that have integer values rather than character values.
• If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the left most of the key.
• If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.
• Create surrogate integer primary key (identity for example) if yourtable will not have many insert operations.
• Clustered indexes are more preferable than nonclustered, if you needto select by a range of values or you need to sort results set withGROUP BY or ORDER BY.
• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.
• You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables inyour database may need indexes. This trace will show which tables arebeing scanned by queries instead of using an index.
• You can use sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.
SQL Optimization Tips
• Use views and stored procedures instead of heavy-duty queries.This can reduce network traffic, because your client will send toserver only stored procedure or view name (perhaps with someparameters) instead of large heavy-duty queries text. This can be usedto facilitate permission management also, because you can restrictuser access to table columns they should not see.
• Try to use constraints instead of triggers, whenever possible.Constraints are much more efficient than triggers and can boostperformance. So, you should use constraints instead of triggers,whenever possible.
• Use table variables instead of temporary tables.Table variables require less locking and logging resources thantemporary tables, so table variables should be used whenever possible.The table variables are available in SQL Server 2000 only.
• Try to use UNION ALL statement instead of UNION, whenever possible.The UNION ALL statement is much faster than UNION, because UNION ALLstatement does not look for duplicate rows, and UNION statement doeslook for duplicate rows, whether or not they exist.
• Try to avoid using the DISTINCT clause, whenever possible.Because using the DISTINCT clause will result in some performancedegradation, you should use this clause only when it is necessary.
• Try to avoid using SQL Server cursors, whenever possible.SQL Server cursors can result in some performance degradation incomparison with select statements. Try to use correlated sub-query orderived tables, if you need to perform row-by-row operations.
• Try to avoid the HAVING clause, whenever possible.The HAVING clause is used to restrict the result set returned by theGROUP BY clause. When you use GROUP BY with the HAVING clause, theGROUP BY clause divides the rows into sets of grouped rows andaggregates their values, and then the HAVING clause eliminatesundesired aggregated groups. In many cases, you can write your selectstatement so, that it will contain only WHERE and GROUP BY clauseswithout HAVING clause. This can improve the performance of your query.
• If you need to return the total table's row count, you can usealternative way instead of SELECT COUNT(*) statement.Because SELECT COUNT(*) statement make a full table scan to return thetotal table's row count, it can take very many time for the largetable. There is another way to determine the total row count in atable. You can use sysindexes system table, in this case. There isROWS column in the sysindexes table. This column contains the totalrow count for each table in your database. So, you can use thefollowing select statement instead of SELECT COUNT(*): SELECT rowsFROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So,you can improve the speed of such queries in several times.
• Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.This can reduce network traffic, because your client will not receivethe message indicating the number of rows affected by a T-SQL statement.
• Try to restrict the queries result set by using the WHERE clause.This can results in good performance benefits, because SQL Server willreturn to client only particular rows, not all rows from the table(s).This can reduce network traffic and boost the overall performance ofthe query.
• Use the select statements with TOP keyword or the SET ROWCOUNTstatement, if you need to return only the first n rows.This can improve performance of your queries, because the smallerresult set will be returned. This can also reduce the traffic betweenthe server and the clients.
• Try to restrict the queries result set by returning only theparticular columns from the table, not all table's columns.This can results in good performance benefits, because SQL Server willreturn to client only particular columns, not all table's columns.This can reduce network traffic and boost the overall performance ofthe query.
1.Indexes
2.avoid more number of triggers on the table
3.unnecessary complicated joins
4.correct use of Group by clause with the select list
5 In worst cases Denormalization
• Try to use constraints instead of triggers, whenever possible.Constraints are much more efficient than triggers and can boostperformance. So, you should use constraints instead of triggers,whenever possible.
• Use table variables instead of temporary tables.Table variables require less locking and logging resources thantemporary tables, so table variables should be used whenever possible.The table variables are available in SQL Server 2000 only.
• Try to use UNION ALL statement instead of UNION, whenever possible.The UNION ALL statement is much faster than UNION, because UNION ALLstatement does not look for duplicate rows, and UNION statement doeslook for duplicate rows, whether or not they exist.
• Try to avoid using the DISTINCT clause, whenever possible.Because using the DISTINCT clause will result in some performancedegradation, you should use this clause only when it is necessary.
• Try to avoid using SQL Server cursors, whenever possible.SQL Server cursors can result in some performance degradation incomparison with select statements. Try to use correlated sub-query orderived tables, if you need to perform row-by-row operations.
• Try to avoid the HAVING clause, whenever possible.The HAVING clause is used to restrict the result set returned by theGROUP BY clause. When you use GROUP BY with the HAVING clause, theGROUP BY clause divides the rows into sets of grouped rows andaggregates their values, and then the HAVING clause eliminatesundesired aggregated groups. In many cases, you can write your selectstatement so, that it will contain only WHERE and GROUP BY clauseswithout HAVING clause. This can improve the performance of your query.
• If you need to return the total table's row count, you can usealternative way instead of SELECT COUNT(*) statement.Because SELECT COUNT(*) statement make a full table scan to return thetotal table's row count, it can take very many time for the largetable. There is another way to determine the total row count in atable. You can use sysindexes system table, in this case. There isROWS column in the sysindexes table. This column contains the totalrow count for each table in your database. So, you can use thefollowing select statement instead of SELECT COUNT(*): SELECT rowsFROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 So,you can improve the speed of such queries in several times.
• Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.This can reduce network traffic, because your client will not receivethe message indicating the number of rows affected by a T-SQL statement.
• Try to restrict the queries result set by using the WHERE clause.This can results in good performance benefits, because SQL Server willreturn to client only particular rows, not all rows from the table(s).This can reduce network traffic and boost the overall performance ofthe query.
• Use the select statements with TOP keyword or the SET ROWCOUNTstatement, if you need to return only the first n rows.This can improve performance of your queries, because the smallerresult set will be returned. This can also reduce the traffic betweenthe server and the clients.
• Try to restrict the queries result set by returning only theparticular columns from the table, not all table's columns.This can results in good performance benefits, because SQL Server willreturn to client only particular columns, not all table's columns.This can reduce network traffic and boost the overall performance ofthe query.
1.Indexes
2.avoid more number of triggers on the table
3.unnecessary complicated joins
4.correct use of Group by clause with the select list
5 In worst cases Denormalization
Tuesday, June 3, 2008
Stored procedures name that are scheduled in a sql job
1. open the Sql server enterprise manager.
2. click on the management folder.
3. and select jobs.
4. right click on the jobs option and click new jobs.
5. type the name of the job.
6. select the category name (database name).
7. select the owner name.
8. type the description.
9. click on the step1 tab page.
10. and click on the new button.
11. write the step name like 'step1'.
12. select the type like 'Transact-sql script(TSQL)'.12. select the database name.
13. in command textbox type your uspname like exec usp_uspname.
14. click on the schedule tab page.
15. and click on the new schedule button.
16. type the name like 'abc'.
17. click on the recurring option button.
18. and click on the change button.
19. and click on the daily option button and also click on the occurs every option and select the time duration.
20. and after that click on the ok button.
2. click on the management folder.
3. and select jobs.
4. right click on the jobs option and click new jobs.
5. type the name of the job.
6. select the category name (database name).
7. select the owner name.
8. type the description.
9. click on the step1 tab page.
10. and click on the new button.
11. write the step name like 'step1'.
12. select the type like 'Transact-sql script(TSQL)'.12. select the database name.
13. in command textbox type your uspname like exec usp_uspname.
14. click on the schedule tab page.
15. and click on the new schedule button.
16. type the name like 'abc'.
17. click on the recurring option button.
18. and click on the change button.
19. and click on the daily option button and also click on the occurs every option and select the time duration.
20. and after that click on the ok button.
Monday, June 2, 2008
Relations in SQL
There are 3 types of relations between tables – One-To-Many, Many-To-Many and One-To-One.
The relation One-To-Many is the most common of the 3 types. In One-To-Many relation a row in one of the tables can have many matching rows in the second table, but a row the second table can match only one row in the first table.
In our example, each manufacturer (a row in the Manufacturer table) produces several different computer models (several rows in the Product table), but each particular product (a row in the Product table) has only one manufacturer (a row in the Manufacturer table).
The second type is the Many-To-Many relation. In this relation many rows from the first table can match many rows in the second and the other way around. To define this type of relation you need a third table whose primary key is composed of the 2 foreign keys from the other 2 table. To clarify this relation lets review the following example. We have a Article table (ArticleID is primary key) and Category (CategoryID is primary key) table.Every article published in the Article table can belong to multiple categories. To accommodate that, we create a new table called ArticleCategory, which has only 2 columns – ArticleID and CategoryID (these 2 columns form the primary key for this table). This new table called sometimes junction table defines the Many-To-Many relationship between the 2 main tables. One article can belong to multiple categories, and every category may contain more than one article.
In the One-To-One relation each row in the first table may match only one row in the second and the other way around. This relationship is very uncommon simply because if you have this type of relation you may as well keep all the info in one single table.
By dividing the data into 2 tables we successfully removed the redundant manufacturer details from the initial Product table adding an integer column referencing the new Manufacturer table instead.
The process of removing redundant data by creating relations between tables is known as Normalization. Normalization process uses formal methods to design the database in interrelated tables.
The relation One-To-Many is the most common of the 3 types. In One-To-Many relation a row in one of the tables can have many matching rows in the second table, but a row the second table can match only one row in the first table.
In our example, each manufacturer (a row in the Manufacturer table) produces several different computer models (several rows in the Product table), but each particular product (a row in the Product table) has only one manufacturer (a row in the Manufacturer table).
The second type is the Many-To-Many relation. In this relation many rows from the first table can match many rows in the second and the other way around. To define this type of relation you need a third table whose primary key is composed of the 2 foreign keys from the other 2 table. To clarify this relation lets review the following example. We have a Article table (ArticleID is primary key) and Category (CategoryID is primary key) table.Every article published in the Article table can belong to multiple categories. To accommodate that, we create a new table called ArticleCategory, which has only 2 columns – ArticleID and CategoryID (these 2 columns form the primary key for this table). This new table called sometimes junction table defines the Many-To-Many relationship between the 2 main tables. One article can belong to multiple categories, and every category may contain more than one article.
In the One-To-One relation each row in the first table may match only one row in the second and the other way around. This relationship is very uncommon simply because if you have this type of relation you may as well keep all the info in one single table.
By dividing the data into 2 tables we successfully removed the redundant manufacturer details from the initial Product table adding an integer column referencing the new Manufacturer table instead.
The process of removing redundant data by creating relations between tables is known as Normalization. Normalization process uses formal methods to design the database in interrelated tables.
Creating a Job in SQL
This topic describes how to use stored procedures to create a Microsoft SQL Server Agent job.
To create a SQL Server Agent job Execute sp_add_job to create a job.
- Execute sp_add_jobstep to create one or more job steps.
- Execute sp_add_schedule to create a schedule.
- Execute sp_attach_schedule to attach a schedule to the job.
- Execute sp_add_jobserver to set the server for the job.
Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly force SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last.
A job in SQL Server Agent consists of Job steps, an owner to provide the security context for the job, and one or more schedules for executing the job.
Steps for creating a new job is as follows:
1. Create a new job, give it a name, database context and add owner
2. Add one or more job steps to job
3. Specify a schedule to run the job (Optionally)
To create a new job, you need to expand SQL Server Agent node within the Object Explorere in SSMS
Right Click on Jobs node and choose new job. The New Job window opens. In this you can define various properties like Name, Category, Owner, Description.
The Job name can be upto 64 characters long. The Job name should clearly define purpose of the Job.
You can assign Job Category to group jobs together based on the types of action they perform. You can use the built-in type job categories that ship with SQL Server or you can create your own job categories.
A descriptive text enables to enter additional details about the job. If certain business rules govern a job, you should specify them in job description to facilitate job-maintenance actions.
On the New job window, in the general tab you specify the job owner, which defines user or group who manage the jobs. The job owner must be member of the sysadmin role to modify the job. If the user is not member of sysadmin role, you need to ensure the job owner should have acces to proxy accounts necessary to execute the step within job.
Create new Job Steps
The high level steps for defining a job are:
1. Create a new job step, specifying name and type.
2. Define the command you want to execute.
3. Define logging and notification Services.
After clicking new, you need to define the job step's properties. For each job step, you nedd to define a name which can be 64 characters. You need to specify the type for each job step. the most common type of job step is Transact-SQl. With this type, you define database context in which to run the T-SQl batch you specify. You can define a simple T-SQl statement to execute a job step but mostly we specify a call to Stored Procedure.Depending on the job step you select, you get configuration options. You can get these options by clicking on Advanced Page in New Job Step window. You can specify actions to be taken when the step completes successfully or when job step fails.
Advantage of Asp.net Web form Controls
- An ASP.NET Web Form first gets compiled into Intermediate Language (IL) and and then to the machine language. Not only the server side code gets compiled but every control and element in the page gets compiled. This enables the ASP.NET Web Form to be in any language that is compatible with Common Language Runtime (CLR) engine.
- The server side code getting translated to IL is that the user can use the application in any browser. In addition, you can reduce the processors and server’s load when you compile the ASP.NET Web Forms in ASP.NET, thereby, increasing the user’s productivity time. Thus ASP.NET allows you to create Web Forms that are platform independent, language independent, and browser independent.
- The entire page in ASP.NET is compiled when it is requested for the first time and when you make subsequent requests the page is not compiled but shown directly in the browser.
- Even though ASP.NET web form on the server can contain client side script such as javascript or Jscript.
Hope this article helps you
Techzone
Wednesday, May 28, 2008
Search Stored Procedure passing parameters
CREATE PROCEDURE sps_empsearch
(
@empcode varchar(20),
@firstname varchar(50),
@lastname varchar(50)
)
AS
BEGIN TRY
if(@empcode='')
set @empcode=null
if(@firstname='')
set @firstname=null
if(@lastname='')
set @lastname=null
select emp_code,first_name,last_name,Department,designation from emp_main where first_name like isnull('%'+@firstname+'%','%') and last_name like isnull('%'+@lastname+'%','%') and Emp_code like isnull( '%'+@empcode+'%' , '%' )
END TRY
BEGIN CATCH
RAISERROR ('Retrieving Records Failed',16,1)
END CATCH
(
@empcode varchar(20),
@firstname varchar(50),
@lastname varchar(50)
)
AS
BEGIN TRY
if(@empcode='')
set @empcode=null
if(@firstname='')
set @firstname=null
if(@lastname='')
set @lastname=null
select emp_code,first_name,last_name,Department,designation from emp_main where first_name like isnull('%'+@firstname+'%','%') and last_name like isnull('%'+@lastname+'%','%') and Emp_code like isnull( '%'+@empcode+'%' , '%' )
END TRY
BEGIN CATCH
RAISERROR ('Retrieving Records Failed',16,1)
END CATCH
Simple Stored Procedures
Syntax for Stored Procedure:
Create Procedure Procedurename
(
//Parameters
)
AS
Begin
// Set of Statements
End
Create Procedure Procedurename
(
//Parameters
)
AS
Begin
// Set of Statements
End
MySQL Customer Meet on 29th May 2008 at Katriya Hotel & Towers, Hyderabad
Meet with Co-founder of MySQL, Director of Technology for MySQL and other MySQL representatives.
Location: Hyderabad
Date: Thursday, May 29, 2008
Time: 14:00-18:00 (Registration begins at 13:30)
Venue: Katriya Hotel and Towers
Email - mysqlhyderabad@sercononline.com
Topics covered:
Location: Hyderabad
Date: Thursday, May 29, 2008
Time: 14:00-18:00 (Registration begins at 13:30)
Venue: Katriya Hotel and Towers
Email - mysqlhyderabad@sercononline.com
Topics covered:
- Introduction to MySQL product line
- MySQL Enterprise Demo
- Storage Engines for MySQL
- Introduction to Understanding Replication Development Process
- MySQL Performance Tuning
- MySQL Case Studies
- Deep Technical Q&A
- Networking
Monday, May 19, 2008
Normalization
Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
Database normalization can essentially be defined as the practice of optimizing table structures. Optimization is accomplished as a result of a thorough investigation of the various pieces of data that will be stored within the database, in particular concentrating upon how this data is interrelated. An analysis of this data and its corresponding relationships is advantageous because it can result both in a substantial improvement in the speed in which the tables are queried, and in decreasing the chance that the database integrity could be compromised due to tedious maintenance procedures.
First Normal form (1NF): A relation is said to be in 1NF if it has only single valued attributes, neither repeating nor arrays are permitted.
Second Normal Form (2NF): A relation is said to be in 2NF if it is in 1NF and every non key attribute is fully functional dependent on the primary key.
Third Normal Form (3NF): We say that a relation is in 3NF if it is in 2NF and has no transitive dependencies.
Boyce-Codd Normal Form (BCNF): A relation is said to be in BCNF if and only if every determinant in the relation is a candidate key.
Fourth Normal Form (4NF): A relation is said to be in 4NF if it is in BCNF and contains no multi valued attributes.
Fifth Normal Form (5NF): A relation is said to be in 5NF if and only if every join dependency in relation is implied by the candidate keys of relation.
Database normalization can essentially be defined as the practice of optimizing table structures. Optimization is accomplished as a result of a thorough investigation of the various pieces of data that will be stored within the database, in particular concentrating upon how this data is interrelated. An analysis of this data and its corresponding relationships is advantageous because it can result both in a substantial improvement in the speed in which the tables are queried, and in decreasing the chance that the database integrity could be compromised due to tedious maintenance procedures.
First Normal form (1NF): A relation is said to be in 1NF if it has only single valued attributes, neither repeating nor arrays are permitted.
Second Normal Form (2NF): A relation is said to be in 2NF if it is in 1NF and every non key attribute is fully functional dependent on the primary key.
Third Normal Form (3NF): We say that a relation is in 3NF if it is in 2NF and has no transitive dependencies.
Boyce-Codd Normal Form (BCNF): A relation is said to be in BCNF if and only if every determinant in the relation is a candidate key.
Fourth Normal Form (4NF): A relation is said to be in 4NF if it is in BCNF and contains no multi valued attributes.
Fifth Normal Form (5NF): A relation is said to be in 5NF if and only if every join dependency in relation is implied by the candidate keys of relation.
CRUD Matrix
SQL is consists of only 4 statements, sometimes referred to as CRUD :-
Create - INSERT - to store new data
Read - SELECT - to retrieve data
Update - UPDATE - to change or modify data.
Delete - DELETE - delete or remove data
A CRUD matrix is a table showing the Functions in an application containing SQL statement affecting parts of a database.
The CRUD Matrix is an excellent technique to identify the Tables in a Database which are used in any User interaction with a Web Site.
CRUD means ‘Create, Read, Update or Delete’, and the CRUD Matrix identifies the Tables involved in any CRUD operation.
It is very valuable to combine a CRUD Matrix with the analysis of possible User Scenarios for the Web Site.
The analysis helps to identify any Tables which are not used, and any Tables which are used heavily, and may therefore be a performance bottleneck.
Create - INSERT - to store new data
Read - SELECT - to retrieve data
Update - UPDATE - to change or modify data.
Delete - DELETE - delete or remove data
A CRUD matrix is a table showing the Functions in an application containing SQL statement affecting parts of a database.
The CRUD Matrix is an excellent technique to identify the Tables in a Database which are used in any User interaction with a Web Site.
CRUD means ‘Create, Read, Update or Delete’, and the CRUD Matrix identifies the Tables involved in any CRUD operation.
It is very valuable to combine a CRUD Matrix with the analysis of possible User Scenarios for the Web Site.
The analysis helps to identify any Tables which are not used, and any Tables which are used heavily, and may therefore be a performance bottleneck.
Replication
Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:
1. Snapshot replication - It distributes data exactly as it appears at a specific moment in time and doesn’t monitor for updates. It can be used when data changes are infrequent. It is often used for browsing data such as price lists, online catalog, or data for decision support where the current data is not required and data is used as read only.
2. Merge replication - It is the process of distributing the data between publisher and subscriber, it allows the publisher and subscriber to update the data while connected or disconnected, and then merging the updates between the sites when they are connected.
3. Transactional replication (with immediate updating subscribers, with queued updating subscribers) - With this an initial snapshot of data is applied, and whenever data modifications are made at the publisher, the individual transactions are captured and propagated to the subscribers.
1. Snapshot replication - It distributes data exactly as it appears at a specific moment in time and doesn’t monitor for updates. It can be used when data changes are infrequent. It is often used for browsing data such as price lists, online catalog, or data for decision support where the current data is not required and data is used as read only.
2. Merge replication - It is the process of distributing the data between publisher and subscriber, it allows the publisher and subscriber to update the data while connected or disconnected, and then merging the updates between the sites when they are connected.
3. Transactional replication (with immediate updating subscribers, with queued updating subscribers) - With this an initial snapshot of data is applied, and whenever data modifications are made at the publisher, the individual transactions are captured and propagated to the subscribers.
Wednesday, May 14, 2008
PHP tutorials
Hi,
You can find php tutorials from http://in2.php.net/tut.php
and it is very useful for the beginners.
Techno Zone
You can find php tutorials from http://in2.php.net/tut.php
and it is very useful for the beginners.
Techno Zone
Intro to SQL
SQL is short for Structured Query Language and is a widely used database language, providing means of data manipulation (store, retrieve, update, delete) and database creation.
The SQL SELECT clause selects data from one or more database tables and/or views. In its basic form the SQL SELECT syntax looks like this:
SELECT ColumnName1, ColumnName2, …FROM Table1
Ex:
"SELECT fname from Employee"
If we want to select all the data (all columns) from the Employee
"SELECT *FROM Employee"
The SQL INSERT INTO clause facilitates the process of inserting data into a SQL table.
Ex: "INSERT INTO Weather (City, Date)VALUES (Delhi '05/10/2005') "
SQL WHERE clause works in conjunction with other SQL clauses like SELECT, INSERT and UPDATE to specify a search condition for these statements.
Ex: "SELECT * FROM Employee Where City = 'New Delhi' "
SQL DISTINCT command used along with the SELECT keyword retrieves only unique data entries depending on the column list you have specified after it.
Ex: "SELECT DISTINCT CityFROM Users "
SQL UPDATE clause serves to update data in database table. The Syntax is
"UPDATE Table1SET Column1 = Value1, Column2 = Value2, … "
SQL DELETE clause is used to delete data from a database table. The basic Syntax is
"DELETE FROM Table1"
SQL TRUNCATE TABLE clause deletes all rows from a database table. Syntax is
"TRUNCATE TABLE tablename"
SQL ORDER BY clause defines in what order to return a data set retrieved with a SQL SELECT statement.
"SELECT * FROM employee ORDER BY City "
The SQL GROUP BY clause is used along with the SQL aggregate functions and specifies the groups where selected rows are placed. WHEN one or more aggregate functions are presented in the SQL SELECT column list, the SQL GROUP BY clause calculates a summary value for each group.
The SQL SELECT clause selects data from one or more database tables and/or views. In its basic form the SQL SELECT syntax looks like this:
SELECT ColumnName1, ColumnName2, …FROM Table1
Ex:
"SELECT fname from Employee"
If we want to select all the data (all columns) from the Employee
"SELECT *FROM Employee"
The SQL INSERT INTO clause facilitates the process of inserting data into a SQL table.
Ex: "INSERT INTO Weather (City, Date)VALUES (Delhi '05/10/2005') "
SQL WHERE clause works in conjunction with other SQL clauses like SELECT, INSERT and UPDATE to specify a search condition for these statements.
Ex: "SELECT * FROM Employee Where City = 'New Delhi' "
SQL DISTINCT command used along with the SELECT keyword retrieves only unique data entries depending on the column list you have specified after it.
Ex: "SELECT DISTINCT CityFROM Users "
SQL UPDATE clause serves to update data in database table. The Syntax is
"UPDATE Table1SET Column1 = Value1, Column2 = Value2, … "
SQL DELETE clause is used to delete data from a database table. The basic Syntax is
"DELETE FROM Table1"
SQL TRUNCATE TABLE clause deletes all rows from a database table. Syntax is
"TRUNCATE TABLE tablename"
SQL ORDER BY clause defines in what order to return a data set retrieved with a SQL SELECT statement.
"SELECT * FROM employee ORDER BY City "
The SQL GROUP BY clause is used along with the SQL aggregate functions and specifies the groups where selected rows are placed. WHEN one or more aggregate functions are presented in the SQL SELECT column list, the SQL GROUP BY clause calculates a summary value for each group.
Welcome to Techno Zone
Hi Friends
Welcome to Techno Zone
This is a place where you can discuss about the .Net, SQL Server, PHP,MySQL and Web Designing technologies.
You can post your doubts, share the latest trends in the related technologies and make this blog a One Stop forum for all our Friends.
Techno Zone
Welcome to Techno Zone
This is a place where you can discuss about the .Net, SQL Server, PHP,MySQL and Web Designing technologies.
You can post your doubts, share the latest trends in the related technologies and make this blog a One Stop forum for all our Friends.
Techno Zone
Subscribe to:
Posts (Atom)