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]

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

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

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.

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.

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.

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

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.

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