‘SQL’ olarak etiketlenen yazılar

23 Nov 2009

SQL DELAY QUERY – SQL de askıya alma

WAITFOR DELAY ‘00:00:30′

BEGIN

END

——–

WAITFOR TIME ‘23:50:00′

BEGIN

END

07 Aug 2009

MS SQL Encryption Compare Decryption Methods – Şifreleme karşılaştırma Çözümleme yöntemleri

DECLARE @EncryptedPass VARBINARY(MAX), @Password NVARCHAR(MAX)

SET @Password =
‘023′

SET @EncryptedPass =
pwdencrypt(‘123′)

SELECT EncryptedPassword = @EncryptedPass

 
EncryptedPassword

————————-

0×010096A522DB61583146F78E7511080B0EBF93BD2E4515CFA824

 
–Wrong password – Yanlýþ þifre

SELECT Result =
pwdcompare(@Password,@encryptedPass)

 
Result

———–

0

 
 
SET @Password =
‘123′

–Right password – Doðru þifre

SELECT Result =
pwdcompare(@Password,@encryptedPass)

 
Result

———–

1

 
—————————————–

—Second Method – ikinci yöntem

—————————————–

DECLARE @EncryptedText VARBINARY(MAX)

SET @EncryptedText =
EncryptByPassphrase(’some handy passphrase’,
‘encrypt me!’)

SELECT EncryptedText = @EncryptedText

 
EncryptedText

————–

0×010000003785B6621388CEFAFF17085237ABBB2F48EEC2E1D08DBE75CE2C7BF644E46616

 
SELECT
CAST(DecryptByPassphrase(’some handy passphrase’, @EncryptedText)
AS
VARCHAR(MAX))
AS OriginalText

OriginalText

————–

encrypt me!

09 Jul 2009

SQL cursor alternatif ( alternative )

–bi tabloda farkli kayitlar arasindaki saat farklarina gore kayitlari isaretlemem gerekti.
–borsaci oldugumuzdan birbirine cok yakin tradeleri isaretlemekti

–CustomerID    ParityID    ProcessDate    BuySell    OrderID    isSuspicious

–30    22    2009-06-19 01:18:32.420    Buy     10124566    0

–31    22    2009-06-19 01:18:32.420    Buy     10124565    0

–70    22    2009-06-19 01:18:32.420    Buy     10122029    0

–192    22    2009-06-19 01:18:32.420    Buy     10122028    0

–737    5    2009-06-19 11:56:55.310    Buy     10125624    0

–737    5    2009-06-19 11:58:18.760    Sell    10125635    1

–737    5    2009-06-19 11:58:54.950    Sell    10125640    1

–737    5    2009-06-19 11:59:16.870    Sell    10125641    1

–737    5    2009-06-19 12:00:30.550    Sell    10125654    1

–737    5    2009-06-19 12:00:51.800    Sell    10125659    1

–737    5    2009-06-19 12:02:29.800    Sell    10125666    0

–737    5    2009-06-19 12:10:09.850    Buy     10125690    2

–737    5    2009-06-19 12:11:04.400    Sell    10125694    2

–737    5    2009-06-19 12:12:09.450    Sell    10125697    0

–737    5    2009-06-19 12:21:46.620    Sell    10125715    0
–cozumum asagidaki gibi oldu
============================
–temp tablo
============================
CREATE
TABLE #Scalpers(
CustomerID INT,
ParityID INT,
ProcessDate DATETIME,
BuySell [...]

09 Feb 2009

MS SQL iki tablo nun kolonlarını karşılaştırma – Compare Table columns

SELECT col1.NAME, temp.name

FROM DBName.sys.COLUMNS col1

JOIN DBName.sys.sysobjects ob ON ob.ID = col1.OBJECT_ID
AND ob.name =
‘Customer’

LEFT
JOIN(
SELECT col2.NAME

            FROM DBName.sys.COLUMNS col2

            JOIN DBName.sys.sysobjects ob2 ON ob2.ID = col2.OBJECT_ID
AND ob2.NAME =
‘Customer_Log’

)
AS temp ON temp.NAME = col1.name