=======================
MS SQL Usage
=======================

.. contents::

Check if database/sp/table exist?
-------------------------------------

.. code-block:: sql
	
	if db_id('dbname') is not null
	if object_id('object_name', 'U') is not null -- for table
	if object_id('object_name', 'P') is not null -- for SP

These are much easier to read and use than queries against system tables/views.

SET NOCOUNT { ON | OFF } 
--------------------------------------
使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息 
语法：SET NOCOUNT { ON | OFF } 
默认情况下为 OFF

#. 当 SET NOCOUNT 为 ON 时，不返回计数（表示受Transact-SQL 语句影响的行数）。 
#. 当 SET NOCOUNT 为 OFF 时，返回计数。

即使当 SET NOCOUNT 为 ON 时，也更新 @@ROWCOUNT 函数。

当 SET NOCOUNT 为 ON 时，将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft® SQL Server™ 提供的实用工具执行查询时，在 Transact-SQL 语句（如 SELECT、INSERT、UPDATE 和 DELETE）结束时将不会在查询结果中显示\ **"nn rows affected"**\ 。

Information_Schema View Listing
---------------------------------------
List of all MSSQL INFORMATION_SCHEMA views:

.. code-block:: none

	CHECK_CONSTRAINTS  Holds information about constraints in the database 

	COLUMN_DOMAIN_USAGE  Identifies which columns in which tables are user-defined datatypes 

	COLUMN_PRIVILEGES  Has one row for each column level permission granted to or by the current user 

	COLUMNS  Lists one row for each column in each table or view in the database 

	CONSTRAINT_COLUMN_USAGE  Lists one row for each column that has a constraint defined on it 

	CONSTRAINT_TABLE_USAGE  Lists one row for each table that has a constraint defined on it 

	DOMAIN_CONSTRAINTS  Lists the user-defined datatypes that have rules bound to them 

	DOMAINS  Lists the user-defined datatypes 

	KEY_COLUMN_USAGE  Lists one row for each column that's defined as a key 

	PARAMETERS  Lists one row for each parameter in a stored procedure or user-defined function 

	REFERENTIAL_CONSTRAINTS  Lists one row for each foreign constraint 

	ROUTINES  Lists one row for each stored procedure or user-defined function 

	ROUTINE_COLUMNS  Contains one row for each column returned by any table-valued functions 

	SCHEMATA  Contains one row for each database 

	TABLE_CONSTRAINTS  Lists one row for each constraint defined in the current database 

	TABLE_PRIVILEGES  Has one row for each table level permission granted to or by the current user 

	TABLES  Lists one row for each table or view in the current database 

	VIEW_COLUMN_USAGE  Lists one row for each column in a view including the base table of the column where possible 

	VIEW_TABLE_USAGE  Lists one row for each table used in a view 

	VIEWS Lists one row for each view 


.. code-block:: sql
	
	USE EVENT_SERVICE
	SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'registrations'


sys.objects type
---------------------------------------
Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object.

.. code-block:: none

	Object type. Can be one of these object types:
	C = CHECK constraint
	D = Default or DEFAULT constraint
	F = FOREIGN KEY constraint
	L = Log
	FN = Scalar function
	IF = Inlined table-function
	P = Stored procedure
	PK = PRIMARY KEY constraint (type is K)
	RF = Replication filter stored procedure 
	S = System table
	TF = Table function
	TR = Trigger
	U = User table
	UQ = UNIQUE constraint (type is K)
	V = View
	X = Extended stored procedure



Add one column in one table
---------------------------------------

.. code-block:: sql

	USE [event_service];
	GO
	 
	BEGIN TRANSACTION
	 
	IF NOT EXISTS(SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'registrations' AND COLUMN_NAME = 'fundraiser_id')
	ALTER TABLE [dbo].[registrations]
	        ADD fundraiser_id BIGINT
	 
	COMMIT TRANSACTION
	GO


Update one column in one table
---------------------------------------

.. code-block:: sql

	USE [form_service]
	BEGIN TRANSACTION
	 
	IF EXISTS ( SELECT  *
	           FROM    sys.columns
	           WHERE   object_id = OBJECT_ID('form_entries')
	                   AND name = 'label' )
	   ALTER TABLE [dbo].[form_entries]
	   ALTER COLUMN [label] NVARCHAR(600) NULL                 
                  
	COMMIT TRANSACTION


Cross APPLY usage
------------------------------------------

.. code-block:: sql

	select o.*, rs.RunningSum, rs.SameCode
	from Order o
	cross apply 
	 (
	   select 
	     sum(Amount) as RunningSum, 
	     sum(case when p.OrderCode = o.OrderCode then Amount else 0 end) as SameCode 
	   from Order P 
	   where P.OrderDate <= O.OrderDate
	 ) rs


Reference
----------------------------

#. http://msdn.microsoft.com/zh-cn/library/ms177563.aspx
#. http://msdn.microsoft.com/en-us/library/aa260447(v=sql.80).aspx
#. http://msdn.microsoft.com/en-us/library/ms189783.aspx