Concurrency Control

In general, there are three common ways to manage concurrency in a database:
Pessimistic concurrency control: A row is unavailable to users from the time the record is fetched until it is updated in the database.
Optimistic concurrency control: A row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed results in a concurrency violation.
"Last in wins": A row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records.

ADO.NET and Visual Studio use optimistic concurrency, because the data architecture is based on disconnected data. Therefore, you need to add business logic to resolve issues with optimistic concurrency.
If you choose to use optimistic concurrency, there are two general ways to determine if changes have occurred: the version approach (true version numbers or date-time stamps) and the saving-all-values approach.
The Version Number Approach
In the version number approach, the record to be updated must have a column that contains a date-time stamp or version number. The date-time stamp or a version number is saved on the client when the record is read. This value is then made part of the update.
One way to handle concurrency is to update only if value in the WHERE clause matches the value on the record. The SQL representation of this approach is:


UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2
WHERE DateTimeStamp = @origDateTimeStamp
Alternatively, the comparison can be made using the version number:


UPDATE Table1 SET Column1 = @newvalue1, Column2 = @newvalue2
WHERE RowVersion = @origRowVersionValue
If the date-time stamps or version numbers match, the record in the data store has not changed and can be safely updated with the new values from the dataset. An error is returned if they don't match. You can write code to implement this form of concurrency checking in Visual Studio. You will also have to write code to respond to any update conflicts. To keep the date-time stamp or version number accurate, you need to set up a trigger on the table to update it when a change to a row occurs.
The Saving-All-Values Approach
An alternative to using a date-time stamp or version number is to get copies of all the fields when the record is read. The DataSet object in ADO.NET maintains two versions of each modified record: an original version (that was originally read from the data source) and a modified version, representing the user updates. When attempting to write the record back to the data source, the original values in the data row are compared against the record in the data source. If they match, it means that the database record has not changed since it was read. In that case, the changed values from the dataset are successfully written to the database.
Each data adapter command has a parameters collection for each of its four commands (DELETE, INSERT, SELECT, and UPDATE). Each command has parameters for both the original values, as well as the current (or modified) values.

Using STUFF

To format this data so that all phone numbers are stored in the following format:
(999) 999-9999
for US phone numbers.
phone
----------
6055552862
5615552700
9045555680
5805555371
2815558368
2545558430
3365552797
3365557233

that I want to convert to:
phone
----------
(605) 555-2862
(561) 555-2700
(904) 555-5680
(580) 555-5371
(281) 555-8368
(254) 555-8430
(336) 555-2797
(336) 555-7233
I decided to use STUFF. This is a string function that allows you to delete characters from a string and insert a string value into another string with control over the positioning of the insertion. The difference from REPLACE is that this function uses a position in the string to make replacement rather than a pattern.

The format for STUFF is as follows:
STUFF ( character_expression 1, start , length , character_expression 2)
where the parameters are:


Character_expression 1 - The string expression in which to insert data
Start - The start position for the insertion
Length - The number of characters in character_expression 1 to delete.
Character_expression 2 - the string to insert into character_expression 1


declare @d char( 14)
select @d = '6055552862'
select stuff( stuff( stuff( stuff( @d,1 ,0, '('), 5, 0, ')'), 6, 0, ' '), 10, 0, '-')

which yeilds
(605) 555-2862

Get Linked Server Configuration

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]

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

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'

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

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

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

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

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:

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;
}
}

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

@@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.

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 )

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])

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

Simple Stored Procedures

Syntax for Stored Procedure:
Create Procedure Procedurename
(
//Parameters
)
AS
Begin
// Set of Statements
End