ايران ويج

صفحه‌ها: 1 2 3

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

کد: 
NEWID ( )

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

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

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

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

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

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

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

کد: 
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 براي يافتن ديدها،‌تريگرها ،كليدهاي خارجي و بسياري كاربردهاي ديگر قابل استفاده است كه مي تونيد

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

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

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

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

کد: 
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

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

چنانچه اجراي كوئري بيشتر از چند ثانيه طول بكشه برنامه 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

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

انتقال دیتا تیبل به دیتابیس
فرض کنیم می خواهییم نتیجه یک سلکت که در یک عنصر دیتا تیبل قرار گرفته و تغییراتی روی اون اعمال شده (رکورد اضافه و یا کم شده و یا مقادیرش بروز شدن) رو به یک جدول دومی 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 همان دیتاتیبلی که تغییرات روی ان اعمال شده و میخواهییم آنرا در دیتابیس ثبت کنیم .
------
دوستان تجربیاتتون میتونه برای ما هم مفید باشه ، دریغ نکنین

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

کد: 
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

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

يعني تاريخ 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

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

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

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

کد: 
Select TOP 10 * from Table1

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

کد: 
Select TOP 10% from Table1

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

کد: 
Delete top(1) from Table1 where (id =10)

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

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

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

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

کد: 
WAITFOR TIME  '13:15:20'

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

تقریبا شبیه تابع 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

صفحه‌ها: 1 2 3

hamed_Arfaee

Di Di

hamed_Arfaee

Di Di

hamed_Arfaee

hamed_Arfaee

hamed_Arfaee

hamed_Arfaee

hamed_Arfaee

hamed_Arfaee

hamed_Arfaee