Password in SQL

DECLARE @PWD varbinary(128)
SELECT @PWD = PWDENCRYPT ( 'password' )
SELECT @PWD
-- Correct
SELECT PWDCOMPARE('password', @Pwd )  -- 1
-- Failed
SELECT PWDCOMPARE('bla-bla-bla', @Pwd )  -- 0

Shrink DB log file

USE dbname;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE dbname
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE dbname
SET RECOVERY FULL;
GO

Thanks to Amit kulkarni hubli

Schedule Daily backup of database in MS-SQL Server 2008

1. Open Management Studio.
2. Under "Mangement"  select Maintenance Plans.
3. Right click  and choose New Plan (it will ask for name).
4. Double click on Subplan_1 and change it to your name.
5. Drag from toolbox "Back Up Database Task" control.
6. Right click on it and choose Edit.
7. Choose Backup Type, Databases  and change more fields.
8.OK
9. Click on Job Schedule (in end of row) and set backup date/time.
10. Save plan.
11. Under "Mangement" right click on your job and choose "Execute" for validation.


Enjoy!

Trim leading zeros

REPLACE(LTRIM(REPLACE([Column], '0', ' ')), ' ', '0')
Thanks, 

How to prevent duplicates in 2 columns in one table

We know how to define column as unique, but what to do where you need to prevent duplicate in 2 columns?
This is a state:

CREATE TABLE #tmp
(
Id INT,
Wine VARCHAR(50),
Country VARCHAR(50)
)

INSERT INTO #tmp VALUES 
(1,'Merlot','France')
INSERT INTO #tmp VALUES
(2,'Merlot','Argentina')
INSERT INTO #tmp VALUES
(3,'Malbec','Argentina')

SELECT * FROM #tmp
DROP TABLE #tmp

A result is:

Id |  Wine    |  Country
----------- ------------------
1 |  Merlot   |  France
2 |  Merlot   |  Argentina
3 |  Malbec |  Argentina

A target to prevent:

Id |  Wine    |  Country
----------- ------------------

1 |  Merlot   |  France
2 |  Merlot   |  Argentina
3 |  Malbec |  Argentina

4 |  Merlot   |  France
5 |  Caberne   |  France

That means table allow duplicate Wines and duplicate Countries, but must prevent double rows.
A solution is to add next row after table creation:

ALTER TABLE  #tmp      ADD UNIQUE(Wine, Country)

Enjoy

LINQ and #Temporary tables in Stored Procedure do not have return values

I have Stored Procedure which include #tmp table. I tried to drag it to DBML but every time i got message "bla-bla-bla ... do not have return values...".After countless attempts was found solution! (Thanks to Marc Gravell )
Replace the #tmp in variable, a meaning:

CREATE TABLE #tmp (Item in)
Replace it to:
DECLARE @tmp TABLE (Item int)

A using in query is:
SELECT yt.*, t.Item
FROM YUOR_TABLE yt, @tmp t


Enjoy!

How to encrypt and decrypt password in SQL Server

Here you can find nice and full solution to encrypt and decrypt password for use in SQL Server.

Using:

declare @value VARCHAR(8000)
--Encrypt
set @value =  dbo.fnEncDecRc4('Magic Word','password')
--Decrypt
select dbo.fnEncDecRc4('Magic Word',@value)

Enjoy!

10 Tips to Improve your LINQ to SQL Application Performance

Full arcticle

Get column info in MS SQL

Existing some ways how to get column information, but Joe Webb offered very simple and effective query:

SELECT 
   ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,CHARACTER_MAXIMUM_LENGTH
  ,IS_NULLABLE
  ,COLUMN_DEFAULT
FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE   
  TABLE_NAME ='TABLE_NAME' 
ORDER BY 
  ORDINAL_POSITION ASC; 

Find all tables, which includes column name

Get all table names for a specific column name

SELECT sysobj.name as Table_Name
FROM sysobjects sysobj
INNER JOIN syscolumns syscol
 ON sysobj.id= syscol.id
WHERE  syscol.name = 'COLUMN_NAME_FOR_SEARCH'

-------------------------------------------

Get all columns and table names for a 'like column_name' query

SELECT syscol.[name] as Column_Name,
 sysobj.name as Table_Name 
FROM sysobjects sysobj
INNER JOIN syscolumns syscol 
ON sysobj.id=syscol.id
WHERE syscol.name like '%COLUMN_NAME%'

SQL Server 2005, Clean your Database Records & reset Identity Columns, all in 6 lines

Well, I had a small issue regarding writing a script to clean a database we have and reset its identity columns in all tables. Although the database wasn't huge one (less than 100 tables) I had to trace relations to be able to delete child table's records before parent's ones because of the foreign key constraints. The solution is disable the foreign keys and delete records with no fear of any errors then enables the constraints again.
Well, I found a solution to disable all constraints without the need to go on each table and disable it manually. and I was happy to know that I can use this solution in deleting all records from all tables. Not only this I was able to use the same solution to reset identity columns in all tables.
The solution was to use this built in stored procedure sp_MSforeachtable. For help about this proc search for it in Books online or use this sp_helptext sp_MSForeachtable.
Now back to my 6 lines, bellow is how I re-zeroed my Database:

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable
     'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 
     'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable
      'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable
     'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable
      'IF OBJECTPROPERTY(OBJECT_ID(''?''),
      ''TableHasIdentity'') = 1 
      BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

Via Moses on DotNetSlackers

SQL Function to Parse AlphaNumeric Characters from String

CREATE FUNCTION dbo.ParseAlphaCharsByString
(
    @text VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE    @IncorrChachIndex SMALLINT

SET @IncorrChachIndex = PATINDEX('%[^0-9A-Za-z]%', @text)
WHILE @IncorrChachIndex > 0
BEGIN
SET @text = STUFF(@text, @IncorrChachIndex, 1, '')
SET @IncorrChachIndex = PATINDEX('%[^0-9A-Za-z]%', @text)
END
RETURN @text
END
GO

Debugging Stored Procedures in Visual Studio 2005

Debugging SQL Server 2005 Stored Procedures in Visual Studio

Intellisense for SQL Query Analyzer

SQL Prompt™ is freeware tool which is а intellisense for SQL Query Analyzer.

HomePage

SqlDependency in VS.NET 2005

SqlCommand command = new SqlCommand("Select * from Table");

// Clear any existing notifications

 command.Notification = null;

.

.

.

// Create the dependency for this command

 SqlDependency dependency = new SqlDependency(commmand);

// Add the event handler

dependency.OnChange += new OnChangeEventHandler(OnChange);

voidOnChange(object sender, SqlNotificationEventArgs e)

{

    SqlDependency dependency = sender as SqlDependency;

    dependency.OnChange += OnChange;

    //Handler for Object

    OnAnyEvent();

}

Split in SQL

Split list of numbers ('1, 2, 3, 4, 67, 87') to table.

Input: : List of numbers (0;1;5;45;...)

Ouput:

0

1

5

45

...

A using: SELECT * FROM Split('0;1;5;45;',';')

CREATE FUNCTION Split
(

@list varchar(8000), -- List of integer with any delemiter

@delimiter CHAR(1) -- Delimetr

)
RETURNS
@list_int TABLE (item int) --list of integer

AS
   BEGIN
  DECLARE @position int, @itemvarchar(20)

   WHILE @list <> ''
   BEGIN
     SET @position = CHARINDEX(@delimiter, @list)
     IF @position = 0
     SET @position = LEN(@list) + 1

     SET @item=LEFT(@list, @position - 1)
     INSERT INTO @list_int VALUES(CAST(@itemAS int))
     SET @list = STUFF(@list, 1, @position, '')
    END
RETURN
END