สร้าง Trigger

การเพิ่มความเร็วของการทำ Query (SQL Server Query Performance)

เราสามารถเพิ่มความเร็วของการ Query ข้อมูลได้โดยทำให้ Column ที่เป็นข้อมูลเป็น Index

ให้เลือก Column ที่มีการใช้งานที่เป็นเงื่อนไขของโปรแกรม

เช่น Order by , Where หรือ Column ที่ใช้ในการ Join กับตารางอื่น เป็นต้น

1. เลือก Table และคลิกไปที่ Design

การ Export Table to Excel (SQL Script)

EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO
 
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
 
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

---- Export To Excel File
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=E:\_Backup\testing.xls;', 
    'SELECT * FROM [Sheet1$]') select Sku_Id,Str1 from ms_SKU

---- Insert Form Excel File
SELECT * INTO dbo.ImportedEmployeeData
FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0 Xml;HDR=YES;Database=E:\_Backup\testing.xls;',
       'SELECT * FROM [Sheet1$]')
GO

อ้างอิง :: http://sqlandme.com/2013/04/15/sql-server-import-data-from-excel-using-t-sql/

Script Import and Export Table Sql Server (Sqlserver Excel)

1. สร้างเอกสาร Exel C:\Book1.xls 2. ทดสอบตามด้านล่าง

--Query Excel 
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\Book1.xls;HDR=YES', 
'SELECT * FROM [Sheet_Name$]')


 
--Export SQL table to Excel
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\Book1.xls;', 
'SELECT * FROM [Sheet_Name$]') select * from Table_Name
 

--Import Excel to SQL table 
Insert into Table_Name 
Select * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\Book1.xls;HDR=YES', 
'SELECT * FROM [Sheet_Name$]')

การเขียน Connection String แบบต่างๆ เพื่อเชื่อมต่อ SQLConnection (.Net)

ในการทำให้โปรแกรมของเรา สามารถติดต่อฐานข้อมูล 
อันนึงที่มักเป็นปัญหา บ่อยๆ คือ การเขียน Connection String ผิด

ใช้คำสั่ง Query ด้วย TEXT หรือ NVARCHAR มากกว่า 4000 (Execute Query With NVARCHAR More Than 4000 )

ใช้คำสั่ง Query ด้วย TEXT หรือ NVARCHAR ข้อดีคือสามารถรับได้ถึง 8000 ตัวอักษร

declare @sql nvarchar(4000)
declare @sql2 nvarchar(4000)
SET  @sql = 'SELECT * FROM TABLE'
SET  @sql2 = ' WHERE 1=1'
EXEC(@sql + @sql2)

สั่ง Query ด้วย NVARCHAR ( Execute Query With NVARCHAR)

ใช้คำสั่ง Query ด้วย TEXT หรือ NVARCHAR ข้อเสียคือสามารถรับได้แค่ 4000 ตัว

declare @sql nvarchar(4000)
SET  @sql = 'SELECT * FROM TABLE'
execute sp_executesql @sql

RESTORE DATABASE WITH SCRIPT

การ Restore Database ด้วย Query Script

ALTER DATABASE dbName SET SINGLE_USER

RESTORE DATABASE dbName 
FROM DISK = 'D:\dbName.bak'
WITH Replace

ALTER DATABASE dbName SET MULTI_USER

"NoSQL" Database Management System

"NoSQL" Database Management System

 Not Only SQL (NoSQL)

พอดีตอนนี้ผมต้องเข้าไปพัวพันกับเรื่อง ฐานข้อมูล หรือ database ขนาดใหญ่ (สำหรับผมนะครับ แต่คงไม่ใหญ่สำหรับคนอื่น) และเคยหลวมตัวเข้าไปนั่งฟัง whitepaper ของพวกพี่ ป.โท เกี่ยวกับ database ที่มีการ insert วันละเป็นล้าน record มาด้วย
เลยสงสัยว่า ยักษ์ใหญ่อย่างพวก Google,Facebook,twitter และรายอื่นๆ มีวิธีเก็บฐานข้อมูลกันอย่างไร หาไปหามา เจอเรื่องตกใจ ,,, เค้าใช้ "NoSQL",,, อ้าวยังไงล่ะทีนี้ สงสัยไปหมด แล้ว Relational Database ล่ะ
ไหนจะ SELECT * FROM ... ไหนจะเรื่อง table และ field อ่าว แล้วเรื่อง SQL Injection ล่ะ ??? เอ้ะ ยังไงล่ะทีนี้ ,,,

หลังจากหาข้อมูลได้พอสมควร เท่าที่ดูแล้วมันไม่ใช่เทคโนโลยีใหม่เลยครับ มีมานานแล้ว คงแค่ว่า(ผม)ไม่ค่อยได้เข้าไปพัวพันกับมัน เลยไม่ใช่สิ่งที่จะได้เจอ หรือจะได้ใช้ในชีวิตจริงกันมากเท่าไหร่นัก

จะเข้าใจได้ มันก็คงต้องเริ่มจากเรื่อง basic concept ของมันกันก่อน ว่าแล้ว ก็หาดูกันเลย
อะไรคือ NoSQL

NoSQL ถ้าหาข้อมูลแล้วเจอชื่อเต็มๆ หลายๆ แบบก็ไม่ต้องตกใจนะครับ ก็คงแล้วแต่จะเรียกกันไป เช่น Not Only SQL บ้าง , Non Relational Database บ้าง หรืออะไรอื่นๆ อีก ก็แล้วแต่ครับ
ปัจจุบันเรื่องฐานข้อมูลต่างๆ ไม่ว่าจะเป็นตามเว็บไซต์ ก็คงจะมีข้อมูลที่ต้องเก็บต่อวันมากขึ้น ตามสังคมแบบ Social Network ทำให้ไม่ต้องสงสัยเลยว่า ในอนาคต trend การเก็บข้อมูลแบบ NoSQL นี้ จะต้องใช้กันอย่างแพร่หลายแน่ๆ
จริงๆ แล้ว เจ้า NoSQL นี้ไม่มีอะไรมากเท่าไหร่ แค่เป็นการวิวัฒนาการที่มาจาก Relational Database จำพวก MySQL, Microsoft SQL นั่นแหละครับ แต่สิ่งที่มันทำให้แตกต่างจากพวก SQL ก็คือ

1.Non Relational
2.Distributed, Large Scale Databases
3.Horizontally Scalable (More nodes/servers)
4.Open Source
5.Schema Free
6.Eventually Consistent (BASE - Basically Available, Soft state, Eventual consistency)
7.Easy Replication Support
8.Simple API support

REPLACE Enter Key and Space (แทนค่า Enter)

SELECT REPLACE(REPLACE(REPLACE(Field_Name, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')
FROM Table_Nane

การใช้งาน SQL Function

1. การสร้าง Function

(Scalar-valued and)

CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0) 
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND 
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END
GO

(table-valued )

CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
      FROM sales s, titles t
      WHERE s.stor_id = @storeid and
      t.title_id = s.title_id)
GO

2. การเรียกใช้งาน Function (Scalar-valued )

SET DATEFIRST 1
SELECT dbo.ISOweek('12/26/1999') AS 'ISO Week'

ประกาสตัวแปล Table (DECLARE TABLE VARIABLE)

DECLARE @tempLocationBalance_Index TABLE (
LocationBalance_Index varchar(13)
,PickTotal_Qty float
,FractionPickTotal_Qty float
,chkFraction bit DEFAULT (0))
 

แก้ไข Designe Database ไม่ได้ (SQL Server Table Design: Saving changes is not permitted)

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

การใช้งานฐานข้อมูล MS SQL 2005/2008 (NewDatabase,Backup,Restore)

1. การสร้างฐานข้อมูล

Kill All Process SQL Server 2005/2008

ขั้นตอน

1. รัน Query ข้างล่างนี้

DECLARE @DatabaseName nvarchar(50)
DECLARE @SPId int
DECLARE @SQL nvarchar(100)
 
--SET @DatabaseName = N'DatabaseName'
 SET @DatabaseName = DB_NAME()
 DECLARE my_cursor CURSOR FAST_FORWARD FOR
 SELECT SPId FROM MASTER..SysProcesses
 WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
 
OPEN my_cursor
 
FETCH NEXT FROM my_cursor INTO @SPId
 
WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @SQL = 'KILL ' + CAST(@SPId as nvarchar(10))
  print @SQL
  EXEC sp_executeSQL @SQL
  --KILL @SPId -- Causing Incorrect syntax near '@spid'.
 
 FETCH NEXT FROM my_cursor INTO @SPId
 END
 
CLOSE my_cursor
DEALLOCATE my_cursor 

2. ปิดหน้าต่างของ Query ด้านบนทิ้ง แล้วสามารถใช้งานเช่น Restore database ได้เลย

การใช้งานตัวแปล TABLE (DECLARE TABLE )

เราสามารถประกาศตัวแปล Table เก็บข้อมูลและใช้งานข้อมูลที่เก็บเข้าได้ และเมื่อใช้งานเสร็จตัวแปลก็จะหายไป
ไม่เปลืองฐานข้อมูลของเรา

// sql code

DECLARE @tempTable TABLE (
Indextest varchar(13)
,Qty float
,Name Varchar(50)
,chkFraction bit DEFAULT (0))

INSERT INTO @tempTable 
values('121212',10,'name1',0)


SELECT * FROM @tempTable 

การใช้งาน CURSOR (เพื่อ Loop ข้อมูลที่ Select มาได้)

// sql code
---- ประกาสตัวแปลเพื่อเก็บค่าแต่ละรอบ
DECLARE @Tag_No varchar(50)

DECLARE SharpStockOver_cursor CURSOR FOR
select Tag_No
from tb_LocationBalance
group by Tag_No
having count(Tag_No) > 1

---- เริ่มต้นเป็นค่าว่าง
set @Tag_No = ''  

----เปิดให้ Corsur ทำงาน
OPEN SharpStockOver_cursor
---- เริ่มต้นทำงาน Row แรก และเก็บค่าในตัวแปล
FETCH NEXT FROM SharpStockOver_cursor
INTO @Tag_No
---- วนลูปจนหมด Row
WHILE (@@FETCH_STATUS = 0)
Begin
 ---- Begin Action ----
 DELETE tb_LocationBalance where Tag_No = @Tag_No
 ---- End Action ----
        ---- ทำงาน Row ต่อไป และเก็บค่าในตัวแปล
 FETCH NEXT FROM SharpStockOver_cursor
 INTO @Tag_No
END
CLOSE SharpStockOver_cursor
DEALLOCATE SharpStockOver_cursor 

แปลงทศนิยม SQL SERVER

การแปลงทศนิยมเราสามารถใช้แค่ ROUND ได้ แต่เข้าถึงระดับ Column ในดาต้าเบสเราต้องมีการ CAST เข้ามาช่วยส่วนเรื่องของ
ความสามารถของทั้งสองฟังก์ชั่นรบกวนไปศึกษาเอาเองนะครับ

// sql code
SELECT CAST(ROUND((Filed_Name),2) AS Numeric(18,2))
FROM  Table_Name

การเคลียร์ log ในดาต้าเบส (Clear log)

          ผมเคยทำการ Backup ฐานข้อมูลจากลูกค้าท่านหนึ่ง ปรากฏว่าได้ขนาด 8 GB ครับ
งงมาก เลยทำการเครียร์ log ในฐานข้อมูลแล้วทำการ Backup ใหม่ ปรากฏว่าได้ขนาดเหลือ 100 MB
หวังว่า Query นี้น่าจะเกิดประโยชน์ต่อผู้ใช้งาน

// sql code
backup log Database_Name with no_log
backup log Database_Name with truncate_only
dbcc shrinkdatabase(Database_Name ,1)

Row_Number (หมายเลขบรรทัดของการ query)

MS SQL Server 2000

// sql code
SELECT top 10  (SELECT COUNT(*) FROM ms_Customer as c2
          WHERE c2.Customer_Index <= c1.Customer_Index) as myNo, c1.*
FROM ms_Customer as c1

MS SQL Server 2005

// sql code
SELECT top 10 Row_Number() OVER(ORDER BY Customer_Index) AS RowNo,*
FROM ms_Customer