Execute a SQL function

USE AdventureWorks2012;
GO
-- Declares a variable and sets it to zero.
-- This variable is used to return the results of the function.
DECLARE @ret nvarchar(15)= NULL; 

-- Executes the dbo.ufnGetSalesOrderStatusText function.
--The function requires a value for one parameter, @Status. 
EXEC @ret = dbo.ufnGetSalesOrderStatusText @Status= 5; 
--Returns the result in the message tab.
PRINT @ret;

SQL - select bit field queries

Suppose you have a null-able bit field in your table and you have rows with true(0), false (1) and NULL values in that column.

To get all true values, the query is
SELECT * FROM Table_1 where IsActive=1

To get all false values, the query is
SELECT * FROM Table_1 where IsActive=0

To get all NULL values, the query is
SELECT * FROM Table_1 where IsActive IS NULL

To get all rows where IsActive is not true,
the query
SELECT * FROM Table_1 where IsActive<>1 will not work.
the query SELECT * FROM Table_1 where (IsActive = 0 or IsActive IS NULL) works.

Update identity column in SQL Server

USE [sales]
GO

/****** Object:  Table [dbo].[Table_1]    Script Date: 11/19/2014 17:36:49 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U'))
DROP TABLE [dbo].[Table_1]
GO

USE [sales]
GO

/****** Object:  Table [dbo].[Table_1]    Script Date: 11/19/2014 17:36:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [txt] [nvarchar](50) NULL
) ON [PRIMARY]

GO
insert into table_1 values('a')
insert into table_1 values('b')
insert into table_1 values('c')
insert into table_1 values('d')
insert into table_1 values('e')
insert into table_1 values('f')
insert into table_1 values('g')
insert into table_1 values('h')
GO
-------------------------------------------------------------------------------------------------------------------
SET IDENTITY_INSERT dbo.table_1 ON
go
UPDATE dbo.table_1
 SET ID=0
 WHERE ID=1;
go
SET IDENTITY_INSERT dbo.table_1 OFF
go
--------------------------------------------------------------------------------------------------------------
--This attempt to update identity column ID fails.
--------------------------------------------------------------------------------------------------------------
Alternative:
USE [sales]
GO

/****** Object:  Table [dbo].[Table_2]    Script Date: 11/19/2014 17:36:49 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_2]') AND type in (N'U'))
DROP TABLE [dbo].[Table_2]
GO

USE [sales]
GO

/****** Object:  Table [dbo].[Table_2]    Script Date: 11/19/2014 17:36:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_2](
    [id] [int] NOT NULL,
    [txt] [nvarchar](50) NULL
) ON [PRIMARY]

GO
-----------------------------------------------------------------------------------------------------------
--The following statements will update the identity column in --table_1
-----------------------------------------------------------------------------------------------------------
ALTER TABLE table_1 SWITCH TO table_2;

UPDATE dbo.table_2
 SET ID=0
 WHERE ID=1;

ALTER TABLE table_2 SWITCH TO table_1;

ideas, concepts

A database can have a DMK - Database Master Key

@@IDENTITY - last inserted identity value

SCOPE_IDENTITY() - last inserted identity value within the scope

SQL Server has two kinds of keys: symmetric and asymmetric.

Symmetric keys use the same password to encrypt and decrypt data.
Asymmetric keys use one password to encrypt data (called the public key) and another to decrypt data (called the private key).

You cannot back up and restore individual asymmetric keys

http://www.dnnsoftware.com/community-blog/cid/154944/check-and-upgrade-sql-database-compatibility-level-before-upgrading-to-dnn-7x-using-dnn-sql

CROSS APPLY and OUTER APPLY

http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

search for a column name in a database

USE DataBaseName
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%column%'
ORDER BY schema_name, table_name;