آیا میدانید اس کیو الی !؟!؟

[hamed_Arfaee]

تولید یک مقدار یکتا

کد:

NEWID ( )

برای تولید یک کلید یکتا و غیر تکراری میشه ازش استفاده کرد

کاربرد 1 : میخواهیین برای لیستی که ثبت میکنین یک کلید واحد و غیر تکراری بدین ولی عدد نباشه .

کاربرد 2 : میخواهییم از یک جدول 10 رکورده 3 رکورد بصورت تصادفی و غیر تکراری انتخاب کنیم :

کد:

SELECT TOP 3 NEWID() id, * FROM table ORDER BY id

شاید کاربرد های دیگه ای هم بشه براش پیدا کرد .

[Di Di]

من هم چند تا دستور يادگرفتم كه خيلي به دردم خوردن، مي زارم شايد براي بقيه دوستان هم مفيد باشه

كد زير نام تمام ديتابيس هاي بانك جاري رو بر مي گردونه

کد:

Select name from sysdatabases

اين كوئري هم نام و مشخصات تمام تيبل هاي بانك جاري رو بر مي گردونه

کد:

SELECT * FROM INFORMATION_SCHEMA.TABLES TABLES_1

اين كوئري هم مشخصات فيلدهاي هر تيبل رو مشخص مي كنه

کد:

SELECT     A.Table_name, b.Column_name, b.DATA_TYPE
   FROM         INFORMATION_SCHEMA.TABLES A INNER JOIN
    INFORMATION_SCHEMA.COLUMNS b ON b.TABLE_NAME = A.TABLE_NAME

در كوئري بالا نمي شه تعيين كرد كه آيا فيلدما Identity هست يا خير،‌براي اين كار بايد از تابع زير استفاده كرد :

کد:

COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as iden

چنانچه فيلدما Identity باشه اين تابع مقدار 1 رو بر مي گردونه،‌ در زير يك مثال هم آورردم :

کد:

SELECT COLUMNPROPERTY(object_id(b.TABLE_NAME), b.COLUMN_NAME, 'IsIdentity') as iden ,  b.*
            FROM         INFORMATION_SCHEMA.TABLES A INNER JOIN
            INFORMATION_SCHEMA.COLUMNS b ON b.TABLE_NAME = A.TABLE_NAME

همچينين INFORMATION_SCHEMA براي يافتن ديدها،‌تريگرها ،كليدهاي خارجي و بسياري كاربردهاي ديگر قابل استفاده است كه مي تونيد

از اينجا اونها رو مطالعه كنيد.

[hamed_Arfaee]

آقا مهدی دستت درد نکنه ، نکته قشنگی بود .

در تکمیل مطلب شما کوئری زیر اطلاعات جزئی دیتابیس رو براتون میاره از جمله :
نام جدول -توضیحات جدول - نام فیلد -توضیحات فیلد - نوع فیلد - طول فیلد - قابلیت نال بودن فیلد - کلید بودن فیلد و افزایشی بودن فیلد .

این کوئری شاید در نگاه اول کاربرد چندانی نداشته باشه ولی من با استفاده از این کوئری فرم بیلدر ساخته بودم که با توجه به خروجی این کوئری میتونستم فرم تولید کنم
به ازای هر جدول یه فرم (توضیح جدول میشه برچسب فرم) به ازای هر فیلد یه تکست باکس و توضیح هر فیلد میشه یه لیبل برای اون تکست باکس و براساس نوع هر فیلد محدودیت میشه تعریف کرد برای هر تکست باکسی .

کد:

SELECT sel1.Table_name,
       sel1.column_name,
       COALESCE(sel1.table_desc, '-')    AS [table_desc],
       COALESCE(sel1.column_desc, '-')   AS [column_desc],
       sel1.userdefined                  AS [xtype],
       sel1.lenght                       AS [xlenght],
       CAST(sel1.isnullable AS VARCHAR)  AS [is_nullable],
       CAST(COALESCE(sel2.is_primary, '0')AS VARCHAR) AS [is_primary],
       CAST(COALESCE(sel3.is_identity, '')AS VARCHAR) AS [is_identity]
FROM   (
           SELECT t.name    AS [Table_name],
                  td.value  AS [table_desc],
                  c.name    AS [column_name],
                  cd.value  AS [column_desc],
                  ty.name   AS [userdefined],
                  REPLACE(c.length, '-1', 'MAX') AS [lenght],
                  c.isnullable
           FROM   sysobjects t
                  INNER JOIN sysusers u
                       ON  u.uid = t.uid
                  LEFT OUTER JOIN sys.extended_properties td
                       ON  td.major_id = t.id
                       AND td.minor_id = 0
                       AND td.name = 'MS_Description'
                  INNER JOIN syscolumns c
                       ON  c.id = t.id
                  INNER JOIN sys.types ty
                       ON  ty.system_type_id = c.xtype
                  LEFT OUTER JOIN sys.extended_properties cd
                       ON  cd.major_id = c.id
                       AND cd.minor_id = c.colid
                       AND cd.name = 'MS_Description'
           WHERE  t.type = 'u'
                  AND ty.name <> 'sysname'
       )                                 AS sel1
       LEFT OUTER JOIN (
                SELECT b.TABLE_NAME,
                       a.COLUMN_NAME,
                       '1' AS [is_primary]
                FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS a
                       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS b
                            ON  a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
                WHERE  (b.CONSTRAINT_TYPE = 'Primary key')
            )                            AS sel2
            ON  sel1.Table_name = sel2.TABLE_NAME
            AND sel1.column_name = sel2.COLUMN_NAME
       LEFT OUTER JOIN (
                SELECT _o.name         AS [Table_name],
                       _i.name         AS [Colname],
                       _i.is_identity  AS is_identity
                FROM   sys.schemas _s
                       JOIN sys.sysobjects _o
                            ON  _o.uid = _s.schema_id
                       LEFT JOIN sys.identity_columns _i
                            ON  _o.id = _i.object_id
                WHERE  _o.xtype = 'U'
            )                            AS sel3
            ON  sel1.Table_name = sel3.Table_name
            AND sel1.column_name = sel3.Colname
ORDER BY
       sel1.Table_name

[Di Di]

بعضي وقتها ممكنه بخوايم يك كوئري سنگين رو اجرا كنيم ، مادامي كه كوئري اجرا نشده برنامه در همون خط باقي مي مونه و

چنانچه اجراي كوئري بيشتر از چند ثانيه طول بكشه برنامه Notresponding مي شه .

براي جلوگيري از اين اتفاق مي شه كاري كرد كه بعد از اجراي دستور Open يا Execute برنامه به كار خودش ادامه بده تا SQL مقدار

ركوردست ما رو برگردونه.

اين كار بوسيله تنظيم مقدار Options در هنگام اجراي كوئري امكان پذير هست

کد:

objRecordset.Open source,actconn,cursortyp,locktyp,opt

در دستور بالا مي توان مقدار Opt رو به 16 يا adAsyncExecute قرار داد.

مثال :

کد:

Dim adoCommand  As ADODB.Command
Dim rs          As ADODB.Recordset

Set adoCommand = New ADODB.Command
With adoCommand
    .ActiveConnection = ADOCn
    .CommandType = adCmdText
    .CommandText = strQuery
    Set rs = .Execute(, , adAsyncExecute)
    Do While .State = adStateExecuting
        DoEvents
    Loop
End With
Set adoCommand = Nothing

براي اطلاعات بيشتر به لينك هاي زير مراجعه كنيد

http://www.w3schools.com/ado/met_rs_open.asp
http://msdn.microsoft.com/en-us/library/...s.85).aspx
http://support.microsoft.com/kb/190988

[hamed_Arfaee]

فیلد های محاسباتی
در جدول فرضی سفارشات دو فیلد تعداد سفارش و قیمت واحد رو ثبت کردیم حالا برای اینکه مجموع هر سفارش رو داشته باشیم میتونیم یک دیدگاه (ویو) بنویسم و اون فیلد مجموع رو که حاصل ضرب قیمت در تعداد هست بدست بیاریم ولی میشه یه کار دیگه ای هم کرد اون هم این که یک فیلد با نام مجموع در داخل جدول سفارشات در نظر بگیریم و مقدار اونو محاسباتی ثبت کنیم که با هر بار نیاز (باز خوانی) محاسبه بشه و چون این داده ثبت نمیشه فضایی هم اشغال نمی کنه .
   
با این کار میتونییم از تولید دیدگاه های کوچیک و ازدیادشون جلوگیری کرد .
نکته : میتونییم از فیلد،تابع و مقدار ثابت در فرمول نویسی استفاده کنیم ولی از نتیجه کوئری نمیشه استفاده کرد .
تو این حالت چون مقدار این فیلد محاسباتی هست و بصورت فیزیکی ثبت نمیشه پس نمیتونه بعنوان کلید و یا ایندکس استفاده بشه ولی اگر بخواهیم بعد از محاسبه بصورت فیزی هم ثبت بشه (در این صورت باتغییر هر جز تشکیل دهنده ،این فیلد نیز بروز رسانی خواهد شد) گزینه is persisted رو به YES تغییر میدیم .

[hamed_Arfaee]

انتقال دیتا تیبل به دیتابیس
فرض کنیم می خواهییم نتیجه یک سلکت که در یک عنصر دیتا تیبل قرار گرفته و تغییراتی روی اون اعمال شده (رکورد اضافه و یا کم شده و یا مقادیرش بروز شدن) رو به یک جدول دومی Insert کنیم دو تا راه داریم :
یک) یک حلقه ردیف به ردیف را خوانده و در جدول مقصد ثبت کند .
دو) تمام دیتا تیبل را بصورت پارامتری به دیتابیس بفرستیم و عملیات ثبت اونجا اتفاق بیافته .

روش عول که مشخصه و چون از حلقه استفاده شده پس عملیات سنگینی رو خواهد داشت .
ولی روش دوم :
اول یک "نوع" داخل سرور تعریف میکنیم از نوع ساختار جدول ورودی، یعنی اون جدولی که ما داریم بصورت پارامتر میفرستیم دارای چه ساختاری هست
مثال :

کد:

CREATE TYPE [dbo].[MyTableType] AS TABLE(
    [Id] int NOT NULL,
    [Name] [nvarchar](128) NULL
)

بعد یک استورد پروسیجر می سازیم تا دیتاتیبل ما رو بگیره و مقادیر اونو به جدول مقصد وارد کنه :

کد:

CREATE PROCEDURE [dbo].[InsertTable]
    @myTableType MyTableType readonly
AS
BEGIN
    insert into [dbo].Records select * from @myTableType
END

نکته : دیتاتیبل ما حتما با زیرمجموعه جدول مقصد باشه یعنی همه فیلد های دیتاتیبل با همون نوع باید در جدول مقصد موجود باشند حالا اگر هم بیشتر بود میتونییم در هنگام سلکت اون مقادیر رو هم اضافه کنیم
مثال :

کد:

insert into [dbo].Records select *,'Ekhtelaf1','Ekhtelaf2' from @myTableType

و یک مثال هم برای نحوه استفاده :

کد:

Dim cn As New SqlConnection()
        cn.ConnectionString = "Server=.;Database=test;Trusted_Connection=True;"
        cn.Open()

        Dim dt As New DataTable()

        Dim cm As New SqlCommand("InsertTable", cn)
        cm.CommandType = CommandType.StoredProcedure
        cm.Parameters.AddWithValue("@myTableType", dt)
        cm.ExecuteNonQuery()

نکته : dt همان دیتاتیبلی که تغییرات روی ان اعمال شده و میخواهییم آنرا در دیتابیس ثبت کنیم .
------
دوستان تجربیاتتون میتونه برای ما هم مفید باشه ، دریغ نکنین

[hamed_Arfaee]

محاسبه اختلاف دو تاریخ شمسی به روز
فرقی هم نداره کدوم تاریخ بزرگتر باشه

کد:

CREATE FUNCTION [dbo].[SKabis]
(
    @Y INT
)
RETURNS INT
AS
BEGIN
    DECLARE @kab   INT
    DECLARE @kab5  INT
    DECLARE @a     INT
    SET @kab = 0
    SET @a = @Y - 22;
    IF @a -(@a / 33 * 33) = 0
        SET @kab = 1
    ELSE
    BEGIN
        SET @kab5 = (@a + 1) / 33
        SET @a = @a - @kab5 * 5
        IF @a -(@a / 4 * 4) = 0
            SET @kab = 1
    END
    RETURN @kab
END

GO

CREATE FUNCTION [dbo].[DiffDate]
(
    @date1  CHAR(10),
    @date2  CHAR(10)
)
RETURNS INT
AS
BEGIN
    DECLARE @dat    CHAR(10)
    DECLARE @y      CHAR(4)
    DECLARE @m      CHAR(2)
    DECLARE @d      CHAR(2)
    DECLARE @x      INT
    DECLARE @Days1  INT
    DECLARE @Days2  INT
    DECLARE @K      INT
    DECLARE @i      INT
    
    SET @dat = @date1                 
    
    SET @y = SUBSTRING(@dat, 1, 4)
    SET @m = SUBSTRING(@dat, 6, 2)
    SET @d = SUBSTRING(@dat, 9, 2)
    IF CAST(@m AS INT) <= 7
        SET @x = (CAST(@m AS INT) -1) * 31 + CAST(@d AS INT)
    ELSE
        SET @x = (CAST(@m AS INT) -1) * 30 + CAST(@d AS INT) + 6
    
    SET @x = @x + CAST(@y AS INT) * 365
    
    SET @I = 1
    WHILE @I <= CAST(@y AS INT) -1
    BEGIN
        IF dbo.SKabis(@I) = 1
            SET @x = @x + 1
        
        SET @I = @I + 1
    END
    SET @Days1 = @x
    
    SET @dat = @date2                 
    
    SET @y = SUBSTRING(@dat, 1, 4)
    SET @m = SUBSTRING(@dat, 6, 2)
    SET @d = SUBSTRING(@dat, 9, 2)
    IF CAST(@m AS INT) <= 7
        SET @x = (CAST(@m AS INT) -1) * 31 + CAST(@d AS INT)
    ELSE
        SET @x = (CAST(@m AS INT) -1) * 30 + CAST(@d AS INT) + 6
    
    SET @x = @x + CAST(@y AS INT) * 365
    
    SET @I = 1
    WHILE @I <= CAST(@y AS INT) -1
    BEGIN
        IF dbo.SKabis(@I) = 1
            SET @x = @x + 1
        
        SET @I = @I + 1
    END
    SET @Days2 = @x
    RETURN ABS(@days1 -@days2)
END

[hamed_Arfaee]

بدست آوردن نام و شماره روز هفته

يعني تاريخ 1391/12/06 چند شنبس و يا کدوم روز از هفتس

کد:

-- =============================================
-- Author:        Arfaei.h
-- Create date: 1391.12.06
-- Description:    محاسبه روز هفته به عدد
-- =============================================
CREATE FUNCTION [dbo].[ShamsiWeekDayNom]
(
    @myDate CHAR(10)
)
RETURNS INT
AS
BEGIN
DECLARE @year INT  = SUBSTRING(@myDate, 1, 4)
DECLARE @month INT = SUBSTRING(@myDate, 6, 2)-1
DECLARE @day INT = SUBSTRING(@myDate, 9, 2)
--------------------------------
DECLARE @tmpYear INT = 1000
DECLARE @LeapCount INT = 0
DECLARE @monthSum int
  
IF @month < 7
    SET @monthSum = @month * 31
IF @month >= 7 AND @month < 12
    SET @monthSum = ((@month - 6) * 30) + 186
IF @month = 12
    SET @monthSum = 336
IF @day > 1
    SET @monthSum = (@monthSum + @day) - 1
-------------First Day of Year ----------------
WHILE (@year > @tmpYear)
    BEGIN
        IF ((@tmpYear + 1 ) % 4 ) = 0
            SET @LeapCount = @LeapCount + 1
        SET @tmpYear = @tmpYear + 1
    END
    SET @tmpYear = (@year -1000)+@LeapCount
    SET @tmpYear = (@tmpYear % 7) + 6
IF @tmpYear > 7
    SET @tmpYear = @tmpYear % 7
-------------------------------------------
    SET @monthSum =( @monthSum + @tmpYear)%7
IF @monthSum = 0
    SET @monthSum = 7

RETURN (@monthSum)
END
-- =============================================
-- Author:        Arfaei.h
-- Create date: 1391.12.06
-- Description: نام روز هفته
-- =============================================
CREATE FUNCTION [dbo].[ShamsiWeekDayName]
(
@WeekDayNom INT
)
RETURNS NVARCHAR (10)
AS
BEGIN
    DECLARE @Result NVARCHAR(10)
    SET @Result= CASE
    WHEN  @WeekDayNom=1 THEN N'شنبه'
    WHEN  @WeekDayNom=2 THEN N'يک شنبه'
    WHEN  @WeekDayNom=3 THEN N'دو شنبه'
    WHEN  @WeekDayNom=4 THEN N'سه شنبه'
    WHEN  @WeekDayNom=5 THEN N'چهار شنبه'
    WHEN  @WeekDayNom=6 THEN N'پنج شنبه'
    WHEN  @WeekDayNom=7 THEN N'جمعه'
     ELSE  N'ارفعي'
    END
    RETURN @Result
END

یک ایراد کوچیک وجود داشت که رفع شد .

[hamed_Arfaee]

کار با اولین ها

از عبارتهای کلیدی در دستور سلکت ، کلمه TOP هست که میشه با استفاده از اون از نتیجه انتخاب همون تعدادی رو برگردونیم که میتونن کافی باشن.
مثال
10تای اول

کد:

Select TOP 10 * from Table1

یا
ده صدم اول (اگه 450 تا رکورد بود 45 تای اول رو میاره)

کد:

Select TOP 10% from Table1

از همین کلمه میتونییم برای حذف کردن هم استفاده کنیم

کد:

Delete top(1) from Table1 where (id =10)

کاربرد این دستور وقتی معلوم میشه که بخواهیم از دو رکورد عین هم فقط یکیشو حذف کنیم

[hamed_Arfaee]

ایجاد وقفه (زمان انتظار)
میخواهییم قبل و یا بعد از اجرای دستوری یه مدت زمانی انتظار به اس کیو ال سرور (نخ عمل در خواستی ما) تحمیل کنیم .
انتظار به مدت :

کد:

WAITFOR DELAY '00:02:00'  -- 2 minutes   hh:mm:ss

انتظار تا ساعت :

کد:

WAITFOR TIME  '13:15:20'

[hamed_Arfaee]

تقسیم کردن جمله به کلمات

تقریبا شبیه تابع SPLIT عمل میکنه

کد:

PARSENAME ( 'object_name' , object_piece )

تابع 2 تا پارامتر داره : اولی جمله ورودی و دومی شماره ردیف کلمه از راست

نکته : این تابع جمله رو بر اساس . تقسیم میکنه .

مثال :

کد:

SELECT PARSENAME('AdventureWorks2012..Person', 1)

نتیجه : Person

کد:

SELECT PARSENAME('AdventureWorks2012..Person', 2)

نتیجه : (null)

کد:

SELECT PARSENAME('AdventureWorks2012..Person', 3)

نتیجه : AdventureWorks2012

کد:

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)

نتیجه : John

اگر میخواهییم از چپ به راست بگرده :

کد:

SELECT REVERSE(PARSENAME(REPLACE(REVERSE('Hello John Smith'), ' ', '.'), 1))

نتیجه : Hello