CREATE

CREATE TABLE (Transact-SQL) IDENTITY (Property)

Creates a user-defined aggregate function whose implementation is defined in a class of an assembly in the .NET Framework. 

CREATE AGGREGATE (Transact-SQL)

CREATE APPLICATION ROLE (Transact-SQL)

CREATE ASSEMBLY (Transact-SQL)

CREATE ASYMMETRIC KEY (Transact-SQL)

CREATE AVAILABILITY GROUP (Transact-SQL)

CREATE BROKER PRIORITY (Transact-SQL)

CREATE CERTIFICATE (Transact-SQL)

CREATE COLUMNSTORE INDEX (Transact-SQL)

CREATE COLUMN ENCRYPTION KEY (Transact-SQL)

CREATE COLUMN MASTER KEY (Transact-SQL)

CREATE CONTRACT (Transact-SQL)

CREATE CREDENTIAL (Transact-SQL)

CREATE CRYPTOGRAPHIC PROVIDER (Transact-SQL)

CREATE DATABASE (SQL Server Transact-SQL)

CREATE DATABASE (Azure SQL Database)

CREATE DATABASE (Azure SQL Data Warehouse)

CREATE DATABASE (Parallel Data Warehouse)

CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)

CREATE DATABASE ENCRYPTION KEY (Transact-SQL)

CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)

CREATE DEFAULT (Transact-SQL)

CREATE ENDPOINT (Transact-SQL)

CREATE EVENT NOTIFICATION (Transact-SQL)

CREATE EVENT SESSION (Transact-SQL)

CREATE EXTERNAL DATA SOURCE (Transact-SQL)

CREATE EXTERNAL FILE FORMAT (Transact-SQL)

CREATE EXTERNAL RESOURCE POOL (Transact-SQL)

CREATE EXTERNAL TABLE (Transact-SQL)

CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)

CREATE FULLTEXT CATALOG (Transact-SQL)

CREATE FULLTEXT INDEX (Transact-SQL)

CREATE FULLTEXT STOPLIST (Transact-SQL)

CREATE FUNCTION (Transact-SQL)

CREATE FUNCTION (SQL Data Warehouse)

CREATE INDEX (Transact-SQL)

CREATE LOGIN (Transact-SQL)

CREATE MASTER KEY (Transact-SQL)

CREATE MESSAGE TYPE (Transact-SQL)

CREATE PARTITION FUNCTION (Transact-SQL)

CREATE PARTITION SCHEME (Transact-SQL)

CREATE PROCEDURE (Transact-SQL)

CREATE QUEUE (Transact-SQL)

CREATE REMOTE SERVICE BINDING (Transact-SQL)

CREATE REMOTE TABLE AS SELECT (Parallel Data Warehouse)

CREATE RESOURCE POOL (Transact-SQL)

CREATE ROLE (Transact-SQL)

CREATE ROUTE (Transact-SQL)

CREATE RULE (Transact-SQL)

CREATE SCHEMA (Transact-SQL)

CREATE SEARCH PROPERTY LIST (Transact-SQL)

CREATE SECURITY POLICY (Transact-SQL)

CREATE SELECTIVE XML INDEX (Transact-SQL)

CREATE SEQUENCE (Transact-SQL)

CREATE SERVER AUDIT (Transact-SQL)

CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)

CREATE SERVER ROLE (Transact-SQL)

CREATE SERVICE (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE STATISTICS (Transact-SQL)

CREATE SYMMETRIC KEY (Transact-SQL)

CREATE SYNONYM (Transact-SQL)

CREATE TABLE (Transact-SQL)

CREATE TABLE (Azure SQL Data Warehouse)

CREATE TABLE AS SELECT (Azure SQL Data Warehouse)

CREATE TRIGGER (Transact-SQL)

CREATE TYPE (Transact-SQL)

CREATE USER (Transact-SQL)

CREATE VIEW (Transact-SQL)

CREATE WORKLOAD GROUP (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

CREATE XML INDEX (Selective XML Indexes)

CREATE XML SCHEMA COLLECTION (Transact-SQL)

https://en.wikipedia.org/wiki/Merge_(SQL)

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether condition matches. It was officially introduced in the SQL:2003 standard, and expanded in theSQL:2008 standard.

http://www.midnightdba.com/Jen/2010/10/tip-over-and-partition-by/

OCTOBER 5, 2010 JEN MCCOWN 20 COMMENTS

Here’s a quick summary of OVER and PARTITION BY (new in SQL 2005), for the uninitiated or forgetful…

OVER

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, this query:

SELECT SUM(Cost) OVER () AS Cost
, OrderNum
FROM Orders

Will return something like this:

Cost  OrderNum
10.00 345
10.00 346
10.00 347
10.00 348

Quick translation:

• SUM(cost) – get me the sum of the COST column
• OVER – for the set of rows….
• () – …that encompasses the entire result set.

OVER(PARTITION BY)

OVER, as used in our previous example, exposes the entire resultset to the aggregation…”Cost” was the sum of all [Cost]  in the resultset.  We can break up that resultset into partitions with the use of PARTITION BY:

SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
, OrderNum
, CustomerNo
FROM Orders

My partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”….I’ll get the sum of cost for Customer 1, and then the sum for Customer 2:

Cost  OrderNum   CustomerNo
8.00 345        1
8.00 346        1
8.00 347        1
2.00 348        2

The translation here is:

• SUM(cost) – get me the sum of the COST column
• OVER – for the set of rows….
• (PARTITION BY CustomerNo) – …that have the same CustomerNo.

اضافه کردن کلید خارجی

USE [DB_Name]

ALTER TABLE Foreign_Key_Table

ADD CONSTRAINT FK_ForiegnKeyTable_PrimaryKeyTable FOREIGN KEY (ForeignKeyColumn)

    REFERENCES PrimaryKeyTable(PrimaryKeyTable_PrimaryKeyColumn);

تفاوت های توابع lenو datalength در tsql

یک تفاوت مهم  این است که datalength تعداد بایت و len تعداد کاراکتر را می شمارد.

http://sqlhints.com/tag/sql-len-vs-datalength/

سورس کد تابع SQL برای تعیین صحت کد ملی

--تابع بررسی معتبر بودن کد ملی --tsql

ALTER FUNCTION [dbo].[Is_A_Valid_National_ID](@NationalID CHAR(10))  

RETURNS BIT   

AS   

BEGIN   

   DECLARE @National_ID_Validation_Result BIT = 1--true;  

IF LEN(@NationalID)>10

BEGIN

SET @National_ID_Validation_Result = 0--false;

RETURN @National_ID_Validation_Result; 

END

--تکمیل کمبود احتمالی صفرهای سمت چپ کد ملی

SET @NationalID=REPLICATE('0',10-LEN(@NationalID))+@NationalID

-- اندازه کد ملی نباید بیشتر از ده کاراکتر باشد

-- کد ملی تنها باید شامل ارقام 0 تا 9 باشد

IF ISNUMERIC(@NationalID+'.0e0')=0 

BEGIN

SET @National_ID_Validation_Result = 0--false;

RETURN @National_ID_Validation_Result; 

END

--بیش از دو صفر در سمت چپ کد ملی معتبر نیست

IF  SUBSTRING(@NationalID, 1, 1)=0 AND  SUBSTRING(@NationalID, 2, 1)=0 AND SUBSTRING(@NationalID, 3, 1)=0

BEGIN

SET @National_ID_Validation_Result = 0--false;

RETURN @National_ID_Validation_Result; 

END

--تمام ارقام کد ملی نمی‏تواند یکسان باشد

IF (@NationalID='0000000000' OR @NationalID='1111111111' OR @NationalID='2222222222' OR @NationalID='3333333333'

 OR @NationalID='4444444444'   OR @NationalID='5555555555'  OR @NationalID='6666666666'  OR @NationalID='7777777777'

 OR @NationalID='8888888888' OR @NationalID='9999999999' )

BEGIN

SET @National_ID_Validation_Result = 0--false;

RETURN @National_ID_Validation_Result;  

END

--مقدار خانه کنترل باید صحیح باشد

-- خانه کنترل رقم سمت راست می‏باشد. در صورتی که باقیمانده مجموع ضرب موقعیت در مقدار

-- خانه‏ها کنتر از دو باشد همین مقدار باقیمانده باید در خانه کنترل قرار گرفته باشد و در غیر اینصورت

-- تفاضل یازده با باقیمانده فوق مقدار خانه کنترل را تشکیل می دهد 

BEGIN

DECLARE @b AS INT;

    SET @b=((10*SUBSTRING(@NationalID, 1, 1)+9*SUBSTRING(@NationalID, 2, 1)+8*SUBSTRING(@NationalID, 3, 1)+7*SUBSTRING(@NationalID, 4, 1)+6*SUBSTRING(@NationalID, 5, 1)+5*SUBSTRING(@NationalID, 6, 1)+4*SUBSTRING(@NationalID, 7, 1)+3*SUBSTRING(@NationalID, 8, 1)+2*SUBSTRING(@NationalID,9, 1))%11)

DECLARE @ControlBit AS TINYINT=SUBSTRING(@NationalID,10,1)

    IF @b<2

BEGIN

IF @ControlBit!=@b SET @National_ID_Validation_Result = 0--false;

END

IF @b>=2

BEGIN

IF @ControlBit!=11-@b SET @National_ID_Validation_Result = 0;

END

END

    RETURN @National_ID_Validation_Result;  

END   

GO 

-- شیوه تست تابع

DECLARE @code AS CHAR(10)

SET @code='123456789'--این مقدار را به دلخواه خود تنظیم نمایید.

PRINT dbo.Is_A_Valid_National_ID(@code)

The TRY…CATCH construct cannot be used in a user-defined function.

The TRY…CATCH construct cannot be used in a user-defined function.

• Error handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @ERROR or RAISERROR.

یافتن اندازه فیلد

USE AdventureWorks2012;  
GO  
SELECT length = DATALENGTH(Name), Name  
FROM Production.Product  
ORDER BY Name;  
GO  

پیدا کردن فیلدهای خالی و غیر خالی

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

تلفیق داده ها از چند منبع- گزارش گیری از یک پایگاه داده و وارد کردن آن به پایگاه داده دیگر

insert into [Ali_Safarnavadeh_Test_Database].[dbo].[tbFamilyMember] (FirstName, LastName)

select personfirstname, personlastname

from [ُOriginal_Database].[dbo].[person]