Guide to DateTime Manipulation (edit)

• Greenwich Mean Time (GMT)
• Coordinated Universal Time (UTC)
• International Organization for Standardization (ISO)

https://www.toptal.com/software/definitive-guide-to-datetime-manipulation

https://www.geeksforgeeks.org/javascript-date-utc-method/

https://css-tricks.com/everything-you-need-to-know-about-date-in-javascript/

How to write the current date time to logging file with the same format?

UTC & ISO in C#

var d1 = DateTime.Now;
var s1 = d1.ToUniversalTime().ToString("yyyy-MM-dd\\THH:mm:ss.fffK"); // Return => 2020-09-01T04:52:11.511Z
Console.WriteLine(s1);

var d2 = DateTime.Now;
var s2 = d2.ToUniversalTime().ToString("ddd, dd MMM yyyy HH:mm:ss 'GMT'"); // Return => Tue, 01 Sep 2020 04:46:11 GMT
Console.WriteLine(s2);

DateTime in C#

using System;
using System.Globalization;

namespace ConsoleApp1
{
 internal class Program
 {
 private const string DateFormatInJavaScript = "yyyy-MM-dd"; //For example: 2019-07-31

 private const string DateTimeString = "yyyy-MM-dd HH:mm:ss"; //For example: 2019-07-31 15:00:23

 private const string DateTimeUTCString = "ddd, dd MMM yyyy HH:mm:ss 'GMT'"; //For example: Tue, 01 Sep 2020 04:46:11 GMT

 private const string DateTimeGMTString = "ddd, dd MMM yyyy HH:mm:ss 'GMT'"; //For example: Tue, 01 Sep 2020 04:46:11 GMT

 private const string DateTimeISOString = "yyyy-MM-dd\\THH:mm:ss.fffK"; //For example: 2020-09-01T04:52:11.511Z

 private const string TimeFormatInJavaScript = "HH:mm"; //For example: 14:31

 private static void Main(string[] args)
 {
 string dateString = "2020-09-01T04:52:11.511Z";

 DateTime dt = DateTime.ParseExact(dateString.Substring(0, 24), DateTimeISOString, CultureInfo.InvariantCulture);

 Console.WriteLine("Before converting:");
 Console.WriteLine(dateString);

 Console.WriteLine("After converting:");
 Console.WriteLine(dt.ToUniversalTime().ToString(DateTimeISOString));
 Console.WriteLine(dt.ToUniversalTime().ToString(DateTimeUTCString));
 Console.WriteLine(dt.ToUniversalTime().ToString(DateTimeGMTString));
 }
 }
}

UTC & ISO in SQL + C#

https://stackoverflow.com/questions/1820915/how-can-i-format-datetime-to-web-utc-format/

https://stackoverflow.com/questions/44788305/c-sharp-convert-datetime-object-to-iso-8601-string

string foo = yourDateTime.ToUniversalTime() .ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fff'Z'");

UTC & ISO in JavaScript

https://www.w3schools.com/jsref/jsref_obj_date.asp

var d1 = new Date();
var s1 = d1.toISOString();
console.log(s1);
// Return => 2020-09-01T04:34:35.194Z

var d2 = new Date();
var s2 = d2.toUTCString(); // Note: .toUTCString ~ .toGMTString()
console.log(s2);
Tue, 01 Sep 2020 04:34:58 GMT

Web API (C#)

https://stackoverflow.com/questions/31987050/how-to-force-iso-format-yyyy-mm-ddthhmmss-sss-on-the-json-output

public static class WebApiConfig
{
 public static void Register(HttpConfiguration config)
 {
   ...
   config.Formatters
     .JsonFormatter
     .SerializerSettings
     .DateFormatString = "yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffK";
 }
}

Utils (C#)

https://gist.github.com/dmorosinotto/81e8d809a3833b7caf7b3c813703a8e6

Relationships of a given table (edit)

Tìm tên các bảng trong Stored Procedure:  https://manhng.com/blog/tables-in-stored-procedure/

Tìm quan hệ của một bảng với các bảng khác: https://manhng.com/blog/relationship/

https://stackoverflow.com/questions/2732356/list-of-all-tables-with-a-relationship-to-a-given-table-or-view

T-SQL

SELECT
fk.name 'FK Name',
tp.name 'Parent table',
cp.name, cp.column_id,
tr.name 'Refrenced table',
cr.name, cr.column_id
FROM 
sys.foreign_keys fk
INNER JOIN 
sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
WHERE tp.name = 'Your_Table_Name'

You want to know all foreign key relationships

SELECT name 'ForeignKeyName', 
       OBJECT_NAME(referenced_object_id) 'RefrencedTable', 
       OBJECT_NAME(parent_object_id) 'ParentTable'
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('Your_Table_Name')
      OR parent_object_id = OBJECT_ID('Your_Table_Name');

COLUMNS

select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%TrangThai%'

For SQL Server 2005

SELECT
    name, OBJECT_NAME(parent_object_id) 'Table'
FROM 
    sys.foreign_keys
WHERE 
    referenced_object_id = OBJECT_ID('Your_Table_Name')

Tìm kiếm tên các bảng được sử dụng trong stored procedure (edit)

Tìm tên các bảng trong Stored Procedure:  https://manhng.com/blog/tables-in-stored-procedure/

Tìm quan hệ của một bảng với các bảng khác: https://manhng.com/blog/relationship/

Cách thực hiện

https://www.devtrends.co.uk/blog/handling-errors-in-asp.net-core-web-api

;WITH stored_procedures AS (
SELECT 
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d 
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

Cách khác

https://dba.stackexchange.com/questions/194878/get-the-list-of-tables-used-in-a-stored-procedure-for-a-database

;WITH src AS 
(
 SELECT [procedure] = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name),
 ref = 
 COALESCE(QUOTENAME(d.referenced_server_name) + N'.', N'')
 + COALESCE(QUOTENAME(d.referenced_database_name) + N'.', N'')
 + QUOTENAME(d.referenced_schema_name) + N'.'
 + QUOTENAME(d.referenced_entity_name)
 FROM sys.sql_expression_dependencies AS d
 INNER JOIN sys.objects AS o
 ON d.referencing_id = o.[object_id]
 INNER JOIN sys.schemas AS s
 ON o.[schema_id] = s.[schema_id]
 WHERE o.[type] = N'P'
)
SELECT [procedure],ref 
 FROM src
 GROUP BY [procedure],ref;

Hiển thị quan hệ của một bảng với các bảng khác

https://docs.microsoft.com/vi-vn/sql/relational-databases/tables/view-foreign-key-properties?view=sql-server-2017

SELECT f.name AS foreign_key_name, 
OBJECT_NAME(f.parent_object_id) AS table_name,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name,
OBJECT_NAME (f.referenced_object_id) AS referenced_object,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name,
is_disabled,
delete_referential_action_desc,
update_referential_action_desc 
FROM sys.foreign_keys AS f 
INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id 
WHERE f.parent_object_id = OBJECT_ID('HumanResources.Employee'); 

Cách khác xem các khóa ngoại và khóa chính của một bảng

https://stackoverflow.com/questions/7335141/sql-server-table-relationships

DECLARE @TableName varchar(250)
SET @TableName='YourTableName'
;WITH AllInfo AS
(
SELECT
 tc.TABLE_NAME,tc.CONSTRAINT_NAME, ccu.COLUMN_NAME
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
 INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
 WHERE tc.TABLE_NAME =@TableName
UNION
SELECT
 FK.TABLE_NAME,C.CONSTRAINT_NAME,CU.COLUMN_NAME
 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
 INNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAME
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
 WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
 ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
 WHERE PK.TABLE_NAME=@TableName
)
SELECT DISTINCT
 t1.TABLE_NAME,t1.CONSTRAINT_NAME
 ,STUFF(
 (SELECT
 ', ' + t2.COLUMN_NAME
 FROM AllInfo t2
 WHERE t1.TABLE_NAME=t2.TABLE_NAME AND t1.CONSTRAINT_NAME=t2.CONSTRAINT_NAME
 ORDER BY t2.COLUMN_NAME
 FOR XML PATH(''), TYPE
 ).value('.','varchar(max)')
 ,1,2, ''
 ) AS ColumnNames
 FROM AllInfo t1
 ORDER BY 1,2,3
 

Cách khác xem quan hệ của một bảng (SIMPLE)

https://stackoverflow.com/questions/8094156/know-relationships-between-all-the-tables-of-database-in-sql-server

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
    tp.name, cp.column_id

SQL Index (edit)

Clustered Index: Sắp xếp dữ liệu vật lý vẫn ở trong bảng hiện tại

Non-Clustered Index: Sắp sếp được chứa ở ngoài bảng hiện tại => tốn bộ nhớ

Composite index
Là kiểu Index có nhiều hơn 1 cột. Cả hai kiểu index cơ sở là Clustered Index và Non Clustered Index cũng có thể đồng thời là là kiểu Composite index.

Unique Index
Là kiểu Index dùng để đảm bảo tính duy nhất trong các cột được tạo Index. Nếu Index loại này được tạo dựa trên nhiều cột, thì tính duy nhất của giá trị được tính trên tất cả các cột đó, không chỉ riêng rẽ từng cột. Ví dụ, nếu bạn đã tạo ra một Index trên các cột FirstName và LastName trong một bảng, thì giá trị của 2 cột này kết hợp với nhau phải là duy nhất, nhưng riêng rẽ từng cột thì giá trị vẫn có thể trùng nhau.
Một Unique Index được tự động tạo ra khi bạn định nghĩa một khóa chính (Primary Key) hoặc một ràng buộc duy nhất (Unique Constraint):

Primary Key
Khi bạn định nghĩa một ràng buộc khoá chính trên một hoặc nhiều cột của bảng, SQL Server tự động tạo ra một Unique - Clustered Index nếu chưa có một Clustered Index nào tồn tại trên bảng hoặc view.

Unique
Khi bạn định nghĩa một ràng buộc duy nhất, SQL Server tự động tạo ra một index có các đặc tính là Unique và là Non Clustered Index. Bạn cũng hoàn toàn có thể tạo ra một Unique và là Clustered Index nếu như chưa có một Clustered Index nào được tạo ra trước đó trên bảng.

Covering index: sử dụng tất cả các cột trong câu truy vấn để tạo ra index

Các chỉ mục hay index trong sql là các bảng tra cứu đặc biệt mà công cụ tìm kiếm cơ sở dữ liệu có thể sử dụng để tăng tốc độ truy xuất dữ liệu. Đơn giản chỉ cần thiết lập một chỉ số là một con trỏ đến dữ liệu trong một bảng. Một chỉ mục trong một cơ sở dữ liệu rất giống với một chỉ mục ở mặt sau của một cuốn sách.

Một chỉ mục giúp tăng tốc các truy vấn SELECT và các mệnh đề WHERE , nhưng nó làm chậm dữ liệu nhập vào, với các câu lệnh UPDATE và INSERT . Các chỉ mục có thể được tạo ra hoặc bỏ đi mà không ảnh hưởng đến dữ liệu.

https://sites.google.com/site/mymssqldb/sql/indexes/01-cacloaiindextrongmssql

• Index giống như chỉ mục của cuốn sách
• Index có thể thực hiện trên Table hoặc View
• Index có thể thực hiện trên nhiều cột
• Index nên được tạo trên các cột mà có giá trị là Interger hơn là giá trị Chuỗi

CREATE INDEX index_name ON table_name;

CREATE INDEX index_name
ON table_name (column_name);

CREATE UNIQUE INDEX index_name
on table_name (column_name);

CREATE INDEX index_name
on table_name (column1, column2);

DROP INDEX index_name;

Chú ý: Mặc dù các chỉ mục nhằm mục đích nâng cao hiệu suất của một cơ sở dữ liệu, nhưng đôi khi cần tránh.

• Các chỉ mục không nên được sử dụng trong các bảng nhỏ.
• Bảng mà thường xuyên có các hoạt động update, insert.
• Các chỉ mục không nên được sử dụng trên các cột mà chứa một số lượng lớn giá trị NULL.
• Không nên dùng chỉ mục trên các cột mà thường xuyên bị sửa đổi.

Các hướng dẫn sau đây chỉ ra khi sử dụng một chỉ mục nên được xem xét lại.

• Các chỉ mục không được sử dụng trên các bảng nhỏ.
• Các bảng có cập nhật hàng loạt thường xuyên, hoặc các hoạt động insert.
• Các chỉ mục không nên được sử dụng trên các cột có chứa một số lượng lớn các giá trị NULL.
• Các cột thường được thao tác không nên được lập chỉ mục.

https://vietjack.com/sql/chi_muc_index_trong_sql.jsp

https://viettuts.vn/sql/index-trong-sql

http://www.sqlviet.com/blog/de-dung-duoc-index-trong-dieu-kien-tim-kiem-cua-cau-lenh

https://viblo.asia/p/su-dung-index-trong-sql-query-1ZnbRlPQR2Xo

https://viblo.asia/p/chi-mucindex-trong-co-so-du-lieu-phan-1-bJzKmMJBK9N

https://viblo.asia/p/chi-mucindex-trong-co-so-du-lieu-phan-2-924lJYN0ZPM

https://viblo.asia/p/chi-mucindex-trong-co-so-du-lieu-phan-3-OeVKB4prlkW

Tối ưu truy vấn

https://viblo.asia/p/5-meo-de-toi-uu-cau-truy-van-sql-cua-ban-RnB5pX4Y5PG

http://www.vertabelo.com/blog/technical-articles/5-tips-to-optimize-your-sql-queries

http://www.nanosoft.vn/home/news/97/detail/185/nguyen-nhan-va-cach-toi-uu-cau-truy-van-sql-trong-lap-trinh.aspx

1) Đánh Index hợp lý

2) Chỉ lấy những cột cần thiết

3) Tránh sử dụng hàm đối với cột của bảng: SELECT * FROM Users WHERE CreatedOn > '2015-04-30';

4) Loại bỏ câu truy vấn con (sub query)

5) Sử dụng '%' OR '_' ở đầu giá trị tìm kiếm LIKE sẽ ngăn việc DB sử dụng index (nếu có) của cột

6) Sử dụng EXISTS thay cho OUTER JOIN (Consider replacing the OUTER JOIN with EXISTS)

7) Để xác định những câu truy vấn chậm bạn có thể sử dụng vết "TSQL by Duration" trong SQL Server Profiler để theo dõi thời gian xử lý của các câu truy vấn

Có thể tìm ra bảng cần đánh index bằng cách sử dụng SQL Server Profiler để tạo Trace Wizard với vết là "Identify Scans of Large Tables"

8) Hạn chế sử dụng function trên column

SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,DOB,GETDATE()) > 21

SELECT member_number, first_name, last_name FROM members WHERE DOB < DATEADD(yy,-21,GETDATE())

9) Sử dụng SQL Stored Procedure vì nó được biên dịch ngay ở lần đầu chạy, những lần sau nó sẽ sử dụng lại cái đã biên dịch trước đó nên tốc độ sẽ nhanh hơn

10) Hạn chế sử dụng DISTINCT và ORDER BY (chú ý rằng UNION tương đương SELECT DISTINCT còn UNION ALL sẽ tương đương SELECT)

11) Hạn chế sử dụng các hàm chuyển đổi kiểu dữ liệu trong mệnh đề WHERE.

12) Ép sử dụng Index để truy vấn nhanh hơn

SELECT lname, fname, address FROM Customers WHERE CusID in (1, 5, 10)

SELECT lname, fname, address FROM Customers (INDEX = IX_CusID) WHERE CusID in (1, 5, 10)

13) Trong các câu truy vấn có 1/nhiều OR bạn có thể viết lại bằng cách kết hợp UNION ALL để tăng tốc độ truy vấn

SET STATISTICS IO ON
SET STATISTICS TIME ON

select * from Orders where UserID<10
UNION ALL
select * from Orders where UserID<20

select * from Orders where UserID < 10 and UserID < 20

14) View không được khuyến khích dùng và nên tránh sử dụng các Views lồng nhau

15) Khi nào dùng IN, EXISTS?

Dùng IN khi: Dữ liệu bảng T1 lớn hơn dữ liệu ở bảng T2
Dùng EXISTS khi: Dữ liệu bảng T2 lớn hơn dữ liệu bảng T1, trường điều kiện được đánh index
Nếu dữ liệu cả 2 bảng đều lớn, hoặc tương đương nhau thì 2 cách dùng có thời gian thực hiện tương đương nhau

16) Sử dụng câu lệnh JOIN thay vì sử dụng SUB-QUERY

17) Sử dụng UPSERT

https://stackoverflow.com/questions/11010511/how-to-upsert-update-or-insert-in-sql-server-2005

18) Nên dùng Constraints thay cho Triggers

19) Thêm câu lệnh SET NOCOUNT ON vào Stored Procedures để dừng thông báo về số dòng được thực thi bởi câu lệnh T-SQL

Database Guide

https://database.guide/how-to-change-the-current-date-format-in-sql-server-t-sql/

https://database.guide/how-to-set-the-current-language-in-sql-server-tsql/

Ví dụ tối ưu

https://dba.stackexchange.com/questions/1215/how-do-i-replace-this-where-clause-with-a-join

http://www.gregreda.com/2013/06/03/join-vs-exists-vs-in/

SET DATEFORMAT ymd;

SELECT * FROM Orders WHERE StartTime > '2013-01-23' AND EndTime < '2013-01-24'

Thiết kế CSDL

https://viblo.asia/p/nhung-buoc-co-ban-de-xay-dung-len-mot-co-so-du-lieu-maGK7jXb5j2

https://viblo.asia/p/nhung-sai-lam-trong-thiet-ke-co-so-du-lieu-1Je5E83wlnL

Có thể xóa các Execution Plan được lưu trong bộ nhớ hay không?

Câu trả lời là có, dùng câu lệnh DBCC FREEPROCCACHE

Để hiển thị chi tiết các thông tin vào ra và thời gian CPU:

SET STATISTICS IO ON
SET STATISTICS TIME ON

Các Stored Procedure hệ thống nên biết

sp_help

sp_helptext

sp_MSforeachtable (link)

Đây là store procedure hệ thống giúp bạn duyệt qua tất cả các table trong Database. Khi đó bạn dùng kí tự ? để làm kí tự đại diện cho table. Ví dụ cơ bản nhất khi dùng sp_MSforeachtable là đếm số dòng của tất cả các bảng trong DB

EXEC sp_MSforeachtable 'SELECT ''?'' TableName, Count(1) NumRecords FROM ?'

https://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database

https://www.sqlmatters.com/Articles/Listing%20all%20tables%20in%20a%20database%20and%20their%20row%20counts%20and%20sizes.aspx (Count * Size of all tables)

https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/ (HAY HAY HAY)

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

sp_depends

Đây là stored procedure hệ thống giúp bạn liệt kê các Views, store procedure do người dùng định nghĩa, hàm do người dùng định nghĩa, trigger, ... có phụ thuộc vào tên đối tượng truyền vào

sp_spaceused

Đây là stored procedure hệ thống giúp bạn lấy kích thước của Database hoặc kích thước của một đối tượng trong Database.

Full Text Index và Full Text Search

https://techblog.vn/fulltext-search-don-gian-ma-huu-ich

https://www.red-gate.com/simple-talk/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

https://www.databasejournal.com/features/mssql/article.php/1438211/Setting-Up-Full-Text-Search-A-Step-by-step-Guide.htm

Full Text Search ~ Inverted Index ~ Tokenize

Full-text search là một cơ chế mà sql server cho phép bạn tìm kiếm một từ hay một cụm từ một cách hiệu quả và nhanh chóng.

Để tạo Full Text Search ta thực hiện qua các bước sau:

1. Tạo Full-Text Catalog 
2. Tạo Full-Text Index 
3. Tạo Index

• SELECT SERVERPROPERTY('IsFullTextInstalled')
  
  • LIKE
  • FREETEXT
  • CONTAINS
• Ý nghĩa
  • Tìm từ đồng nghĩa
  • Tìm từ gần đúng, so từng từ trong cụm từ, đảo vị trí
  • Hỗ trợ tìm kiếm có đấu
  • Loại bỏ các từ khóa noise (nhiễu)

Việc tách string hay còn gọi là tokenize là kỹ thuật quan trọng trong Full Text Search

• char
• nchar
• varchar
• nvarchar
• text
• ntext

CREATE TABLE Products
(
    [ProductID] VARCHAR(10) CONSTRAINT PK_Products PRIMARY KEY,
    [Description] NVARCHAR(MAX) NOT NULL
)

CREATE FULLTEXT CATALOG MyCatalogName
GO

CREATE FULLTEXT INDEX ON Products(ProductID)
 KEY INDEX PK_Products
 ON MyCatalogName
GO

Log4net + Dapper + Stored Procedures + Views + Functions + Triggers (edit)

Source Code

1/ NuGet packages

• Microsoft .NET Framework 4.5
• Log4net version 2.0.8
• Dapper version 1.50.2

2/ Properties\AssemblyInfo.cs

Snippet

[assembly: log4net.Config.XmlConfigurator(Watch = true)]

3/ App.config

Snippet

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
  </configSections>
  <connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=MANHNV;Initial Catalog=Northwind;Uid=sa;Pwd=123456;" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
  </appSettings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
  <log4net>
    <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="ConsoleApp1.log" />
      <appendToFile value="true" />
      <encoding value="UTF-8" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="5" />
      <maximumFileSize value="5MB" />
      <staticLogFileName value="true" />
      <lockingModel type="log4net.Appender.FileAppender+MinimalLock" />
      <layout type="log4net.Layout.PatternLayout">
        <param name="ConversionPattern" value="%date{dd-MM-yy HH:mm:ss} %-5level %message%newline" />
      </layout>
    </appender>
    <appender name="TraceAppender" type="log4net.Appender.TraceAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date{dd-MM-yy HH:mm:ss} %-5level %message%newline" />
      </layout>
    </appender>
    <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%-5level %message%newline" />
      </layout>
    </appender>
    <root>
      <level value="All" />
      <appender-ref ref="RollingFileAppender" />
      <appender-ref ref="TraceAppender" />
      <appender-ref ref="ConsoleAppender" />
    </root>
  </log4net>
</configuration>

4/ Program.cs

Snippet

using Dapper;
using log4net;
using log4net.Config;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace ConsoleApp1
{
    class Program
    {
        static string m_ConnectionString = @"
Data Source=MANHNV;
Initial Catalog=Northwind;
Uid=sa;
Pwd=123456;
";
 
        const string sqlStoredProcedure = @"
 SELECT db_name() AS the__database
, OBJECT_SCHEMA_NAME(P.object_id) AS the__schema
, P.name AS procedure__name 
, C.text AS procedure__text
, C.colid
 FROM sys.procedures P WITH(NOLOCK)
 LEFT JOIN sys.syscomments C ON P.object_id = C.id;";
 
        const string sqlFunction = @"
SELECT
    ROUTINE_NAME, 
	ROUTINE_TYPE, 
    ROUTINE_DEFINITION , 
    ROUTINE_SCHEMA, 
    DATA_TYPE,
    CREATED
FROM
    INFORMATION_SCHEMA.ROUTINES 
WHERE
    ROUTINE_TYPE = 'FUNCTION'
	AND DATA_TYPE = 'TABLE'
";
 
        const string sqlView = @"
SELECT SCHEMA_NAME(schema_id) AS schema_name
, name AS view_name
, OBJECTPROPERTYEX(OBJECT_ID, 'IsIndexed') AS IsIndexed
, OBJECTPROPERTYEX(OBJECT_ID, 'IsIndexable') AS IsIndexable
FROM sys.views;
";
 
        static void Main(string[] args)
        {
            //6 ways to get the current directory in C#
            //https://yetanotherchris.dev/csharp/6-ways-to-get-the-current-directory-in-csharp/
            string basePathStoredProcedures = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Constants.StoredProcedures);
 
            string basePathFunction = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Constants.Functions);
 
            string basePathView = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Constants.Views);
 
            //How to: Create a File or Folder (C# Programming Guide)
            //https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/file-system/how-to-create-a-file-or-folder
            System.IO.Directory.CreateDirectory(basePathStoredProcedures);
            System.IO.Directory.CreateDirectory(basePathFunction);
            System.IO.Directory.CreateDirectory(basePathView);
 
            //Console.WriteLine(basePath);
 
            int totalCount = 0;
            using (IDbConnection db = new SqlConnection(m_ConnectionString))
            {
                var listProc = db.Query<StoredProcDto>(sqlStoredProcedure, CommandType.StoredProcedure).ToList();
                var listUniqueProcName = (from x in listProc select x.procedure__name).ToList();
 
                totalCount = 0;
 
                Console.WriteLine();
                Console.WriteLine("Work with the Stored Procedures");
                Logger.Log.Info("Work with the Stored Procedures");
 
                foreach (var procName in listUniqueProcName)
                {
                    try
                    {
                        string fileName = procName.Trim().Replace("_", "") + Constants.SqlExtension;
                        string filePath = System.IO.Path.Combine(basePathStoredProcedures, fileName);
                        string storedProcedureText = string.Join("", listProc.Where(m => m.procedure__name == procName).Select(m => m.procedure__text).ToList());
                        System.IO.File.WriteAllText(filePath, storedProcedureText);
 
                        totalCount += 1;
                    }
                    catch (Exception ex)
                    {
                        Logger.Log.Error(ex.ToString());
                    }
                }
 
                Console.WriteLine(string.Format("Backup total {0} stored procedures successfully!", totalCount));
 
                /*
                 * Process for Functions
                 */
                var listFunction = db.Query<FunctionDto>(sqlFunction, CommandType.StoredProcedure).ToList();
                var listUniqueFunctionName = (from x in listFunction select x.ROUTINE_NAME).ToList();
 
                totalCount = 0;
 
                Console.WriteLine();
                Console.WriteLine("Work with the Functions");
                Logger.Log.Info("Work with the Functions");
 
                foreach (var functionName in listUniqueFunctionName)
                {
                    try
                    {
                        string fileName = functionName.Trim().Replace("_", "") + Constants.SqlExtension;
                        string filePath = System.IO.Path.Combine(basePathFunction, fileName);
                        string functionText = string.Join("", listFunction.Where(m => m.ROUTINE_NAME == functionName).Select(m => m.ROUTINE_DEFINITION).ToList());
                        System.IO.File.WriteAllText(filePath, functionText);
 
                        totalCount += 1;
                    }
                    catch (Exception ex)
                    {
                        Logger.Log.Error(ex.ToString());
                    }
                }
 
                Console.WriteLine(string.Format("Backup total {0} functions successfully!", totalCount));
 
                /*
                 * Process for View
                 */
                var listView = db.Query<ViewDto>(sqlView, CommandType.StoredProcedure).ToList();
                var listUniqueViewName = (from x in listView select string.Concat(x.schema_name, Constants.Dot, x.view_name)).ToList();
 
                totalCount = 0;
 
                Console.WriteLine();
                Console.WriteLine("Work with the Views");
                Logger.Log.Info("Work with the Views");
 
                foreach (var viewName in listUniqueViewName)
                {
                    try
                    {
 
 
                        string sqlTextView = @"
select definition
from sys.objects o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id('" + viewName + @"') and o.type = 'V'
";
                        string fileName = viewName.Trim().Replace("_", "") + Constants.SqlExtension;
                        string filePath = System.IO.Path.Combine(basePathView, fileName);
                        string viewText = db.Query<string>(sqlTextView).FirstOrDefault();
                        System.IO.File.WriteAllText(filePath, viewText);
 
                        totalCount += 1;
                    }
                    catch (Exception ex)
                    {
                        Logger.Log.Error(ex.ToString());
                    }
                }
 
                Console.WriteLine(string.Format("Backup total {0} views successfully!", totalCount));
            }
 
            Console.ReadKey();
        }
    }
 
    public static class Constants
    {
        public const string SqlExtension = ".sql";
 
        public const string StoredProcedures = "StoredProcedures";
        public const string Functions = "Functions";
        public const string Views = "Views";
 
        public const string Dot = ".";
    }
 
    public class StoredProcDto
    {
        public string the__database { get; set; }
        public string the__schema { get; set; }
        public string procedure__name { get; set; }
        public string procedure__text { get; set; }
        public int colid { get; set; }
 
    }
 
    public class FunctionDto
    {
        public string ROUTINE_NAME { get; set; }
        public string ROUTINE_TYPE { get; set; }
        public string ROUTINE_DEFINITION { get; set; }
        public string ROUTINE_SCHEMA { get; set; }
        public string DATA_TYPE { get; set; }
        public DateTime CREATED { get; set; }
    }
 
    public class ViewDto
    {
        public string schema_name { get; set; }
        public string view_name { get; set; }
        public bool IsIndexed { get; set; }
        public bool IsIndexable { get; set; }
    }
 
    public class Logger
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(Logger));

        public static ILog Log
        {
            get { return Logger.log; }
        }
    }
}

Phần 1: Tối ưu hóa cơ sở dữ liệu (edit)

Tối ưu hóa Cơ sở dữ liệu là một công việc thật sự thử thách, nhất là khi bạn làm việc trên các CSDL có quy mô đủ lớn. Lúc này ngoài yêu cầu về tính đúng đắn thì bài toán hiệu suất, thời gian thực thi của ứng dụng là rất quan trọng. Bạn không thể nói người dùng là do dữ liệu lớn nên họ buộc phải chờ, bạn phải có phương án xử lý. Bạn sẽ thấy mình không thể cứ nâng cấp CPU, RAM, đĩa cứng,… là giải quyết được tất cả mà đôi lúc chỉ cần thực hiện một số thay đổi nhỏ trong thuật giải, cách xử lý đọc-ghi-tính toán nhưng lại có tác động đáng kể đến hiệu suất, thời gian thực thi của hệ thống. Thường thì công việc này sẽ do các chuyên gia quản trị CSDL (DBA) thực hiện dựa trên quá trình kiểm soát vận hành của hệ thống CSDL. Tuy nhiên, bạn - developer cũng nên biết qua về những kiến thức này để có thể áp dụng một số kỹ thuật tối ưu hiệu suất CSDL ngay từ lúc phát triển ứng dụng, giúp ứng dụng xây dựng được hiệu quả hơn.

1) Chọn lựa và tối ưu hóa các chỉ mục (Index) 

Tuy nhiên, bạn cũng nên lưu ý rằng nếu bảng của bạn thường xuyên có các thao tác cập nhật dữ liệu với cách lệnh INSERT, UPDATE và DELETE thì Index sẽ làm tăng thời gian thực hiện các lệnh cập nhật dữ liệu. Lúc này, bạn sẽ phải cân nhắc sao cho tối ưu, không nên có quá nhiều Index trên bảng, còn nếu dữ liệu của bạn có tính ổn định cao, ít có thao tác cập nhật và thao tác đọc là chính thì bạn có thể dễ dàng tăng số lượng index.

Quan sát kết quả thực hiện câu lệnh SQL với Excution Plan, bạn sẽ biết được ở mỗi bước hệ thống thực hiện lấy dữ liệu bằng cách sử dụng hay không sử dụng Index, thời gian thực hiện truy vấn nhiều nhất là ở bước nào để từ đó có cải tiến Index cho phù hợp.

2) Tránh các truy vấn tương quan (correlated sub query)

Truy vấn tương quan là dạng truy vấn con trong đó có sử dụng các giá trị từ các truy vấn cha. Ví dụ như: 

Đây là loại truy vấn có xu hướng chạy từng dòng một, tức là cứ mỗi hàng được trả về bởi các truy vấn bên ngoài và truy vấn con sẽ thực hiện, và do đó làm giảm hiệu suất truy vấn SQL. Có nhiều developer lựa chọn cách viết này vì dễ viết.

Bạn sẽ thấy truy vấn con được gọi nhiều lần, tương ứng với số dòng kết quả của truy vấn cha và đó là lý do mà tốc độ thực hiện truy vấn khá chậm. Bạn có thể hoàn toàn có cách viết khác thông qua  JOIN cho kết quả đúng như vậy nhưng hiệu suất được cải tiến lên rất nhiều.

Lúc này, bảng Cong_ty chỉ phải duyệt một lần, hiệu quả hơn rất nhiều, đặc biệt khi bạn thực hiện truy vấn trên bảng có dữ liệu lớn. 

3) Chỉ chọn các cột dữ liệu cần thiết

Một lời khuyên trong tối ưu hóa SQL đáng lưu ý nữa là bạn nên tránh lấy hết các cột của bảng với cú pháp SELECT *. Bạn nên xác định rõ các cột nào bạn cần, chỉ lấy những dữ liệu nào bạn cần vì dữ liệu chính là tài nguyên, là bộ nhớ, là dung lượng trên đường truyền,… Bạn sẽ mất thời gian nếu lấy quá nhiều dữ liệu mà thật sự không cần thiết. Bạn sẽ thấy chẳng có vấn đề gì nếu là dữ liệu ít, nhưng hãy xem xét một bảng với hàng trăm cột và hàng triệu dòng dữ liệu. Bạn có thật sự cần hết tất cả với câu lệnh SELECT *. Đó thật sự là một sự lãng phí lớn!

4) Nên sử dụng EXISTS() thay vì COUNT(*) để kiểm tra truy vấn có trả về dữ liệu không?

Kỹ thuật tối ưu hóa SQL này liên quan đến việc sử dụng EXISTS(). Nếu bạn muốn kiểm tra xem kết quả truy vấn có dữ liệu theo điều kiện nào đó không, bạn nên sử dụng EXISTS() thay cho COUNT(*). Lý do là vì khi bạn dùng COUNT(*), toàn bộ bảng dữ liệu sẽ được quét và đếm tất cả các dòng thỏa điều kiện bạn đưa vào. Trong khi đó, lệnh EXISTS() sẽ thoát ngay khi nó thấy kết quả thỏa điều kiện. Như vậy, rõ ràng sử dụng lệnh EXISTS() sẽ hiệu quả hơn nhiều so với COUNT(*) mà nó còn giúp cho mã lệnh của bạn cũng rõ ràng, dễ hiểu hơn.

5) Tránh dùng cursor khi cần xử lý từng dòng dữ liệu 

Dữ liệu SQL trả về thường là tập hợp dữ liệu, gồm dòng và cột. Tuy nhiên, trong một số tình huống chúng ta cần xử lý dữ liệu từng dòng như trong bài toán xử lý số liệu nhập-xuất-tồn kho của hàng hóa (hàng có nhập – không xuất, có xuất – không nhập, hàng có tồn đầu kỳ nhưng không có nhập xuất trong kỳ) , bài toán tính đạt/không đạt trên phiếu điểm của sinh viên (tương ứng với mỗi môn học là quy tắc khác nhau),… Trong trường hợp đó, thông thường chúng ta có thể lựa chọn kiểu cursor để duyệt và xử lý từng dòng dữ liệu. Bạn sẽ thấy trên CSDL dữ liệu nhỏ, cùng một thời điểm không có nhiều người dùng cùng cập nhật trên bảng thì cursor làm rất tốt các xử lý này. Nhưng mọi chuyện sẽ khác khi dữ liệu đủ lớn và cần xử lý khóa tranh chấp vì lúc này cursor sẽ khóa dòng dữ liệu cho đến khi dòng đó được duyệt và xử lý xong. Ngoài ra, trong quá trình xử lý của cursor nếu có một xử lý khác muốn cập nhật bảng dữ liệu nguồn của cursor thì hệ thống sẽ báo lỗi.

Có nhiều cách để bạn có thể dùng thay thế cho cursor như dùng bảng tạm, kết hợp truy vấn UNION và truy vấn con, sử dụng truy vấn CASE,… Nhưng thực tế qua nhiều dự án cho thấy, chúng tôi nhận thấy bạn hoàn toàn có thể sử dụng bảng tạm thay thế cho cursor và đặc biệt bảng tạm giúp cho tốc độ xử lý được cải tiến đáng kể, đặc biệt khi làm việc trên CSDL đủ lớn.

Gửi bạn…

Chắc hẳn bạn đã quen với các bài tập CSDL ở trường, cũng đã từng có cảm giác vui vui sau một hồi loay hoay với các bài tập truy vấn SQL đã  thấy kết quả thực hiện đúng. Tuy nhiên, trong thực tế, giải quyết được vấn đề đúng vẫn chưa đủ mà còn cần phải tối ưu hơn về mặt tài nguyên, đặc biệt là tối ưu về tốc độ xử lý. Càng làm nhiều bạn sẽ càng thấy cùng một truy vấn SQL nhưng bạn sẽ có nhiều cách giải quyết khác nhau và nhiệm vụ của bạn là làm sao cho tối ưu nhất. Bạn sẽ hiểu rõ hơn những nguyên tắc nền tảng của SQL qua mỗi lần trải nghiệm và rồi bạn sẽ phát hiện ra thêm được nhiều điều thú vị về SQL, SQL Server để làm tốt hơn công việc của mình.

Phần 2: Phím tắt trong SQL Server

SQL server Management Studio hỗ trợ rất nhiều phím tắt, sau đây là một số phím tắt đặc biệt.

1. Mở cửa sổ query mới (Ctrl + N)

Tổ hợp phím này sẽ giúp bạn mở cửa sổ mới một cách nhanh chóng

2. Hiển thị các cửa sổ SQL đang làm việc (Ctrl + Tab)

Tổ hợp phím này sẽ hỗ trợ cho việc hiển thị tất cả các cửa sổ đang làm việc.

3. Ẩn hiện kết quả câu lệnh (Ctrl + R)

Khi muốn ẩn kết quả câu query thì bạn chỉ cần nhần tổ hợp phím Ctrl + R

4. Chạy câu lệnh SQL đang được chọn (Ctrl + E)

Tổ hợp phím Ctrl + E sẽ giúp bạn chạy câu query đang được chọn

5. Hủy bỏ câu query  đang chạy (Alt + Break or Alt + Scroll Lock)

Trường hợp câu query của bạn đang chạy mất quá nhiều thời gian, bạn có thể sử dụng tổ hợp phím này để hủy bỏ nhanh chóng.

6. Chuyển câu lệnh đang chọn thành chữ hoa, chữ thường (Ctrl + Shift + U, Ctrl + Shift + L)

7. Hiển thị quá trình chạy câu query (Ctrl + L)

Hiển thị quá trình chạy câu query  với tổ hợp Ctrl + L

8. Hiển thị kết quả cùng quá trình chạy câu query (Ctrl + M)

9. Gợi ý những câu lệnh, bảng ... (Ctrl + Space, Tab)

10. Chuyển nhanh đến dòng (Ctrl + G)

Bạn có thể chuyển nhanh đến dòng code bao nhiêu băng cách dùng tổ hợp Ctrl + G

11. Comment và bỏ comment dòng lệnh ( Ctrl + K & Ctrl + C; Ctrl + K & Ctrl + U)

Phần 3: Cách sử dụng SQL Server Agent

http://phuongnguyentrung.blogspot.com/2015/06/sql-tips-create-sql-job-to-backup.html

Phần 4: Tìm thông tin về parameters trong Stored Procedure

EXEC SP_HELP 'test_procedure'

SELECT parameter_name, data_type, ordinal_position FROM information_schema.parameters WHERE specific_name='test_procedure'

Phần 5: Lưu ý đối với các câu lệnh sử dụng thủ tục sp_executesql:

Phải khai báo biến kiểu NVarchar để tránh lỗi

Câu lệnh sai:

DECLARE @SQL VARCHAR(100)
SET @SQL = 'SELECT TOP 1 * FROM sys.tables'
EXECUTE sp_executesql @SQL

Câu lệnh đúng:

DECLARE @SQL NVARCHAR(100)
SET @SQL = 'SELECT TOP 1 * FROM sys.tables'
EXECUTE sp_executesql @SQL

Tips & Tricks (edit)

inurl: tips-tricks

https://gokulraja.wordpress.com/category/tips-tricks/

T3 Templates

https://www.codeproject.com/Articles/21162/Template-Based-Code-Generator

T4 Templates

https://www.tritac.com/blog/code-generationscaffolding-with-visual-studio-t4-templates/

http://paginaswebpublicidad.com/questions/40657/tuy-chon-gian-giao-tuy-chinh-asp-net-mvc-5-t4-templates

Dapper By Eample

https://www.tritac.com/blog/dappernet-by-example/

Generate SQL script (.SQL) from XML Schema (.XSD)

http://mssql.tools/Download.aspx?Id=118

JavaScript

http://crockford.com/javascript/

https://johnresig.com/blog/simple-javascript-inheritance/

OOP in JavaScript

• Work effectively with JavaScript
• JavaScript Built-in Functions
• Prototype-based OOP
• Class-based OOP
• Pseudo-classes
• Pseudo-elements

https://alistapart.com/article/prototypal-object-oriented-programming-using-javascript

In JavaScript, almost "everything" is an object.

• Booleans can be objects (if defined with the new keyword)
• Numbers can be objects (if defined with the new keyword)
• Strings can be objects (if defined with the new keyword)
• Dates are always objects
• Maths are always objects
• Regular expressions are always objects
• Arrays are always objects
• Functions are always objects
• Objects are always objects

All JavaScript values, except primitives, are objects.

JavaScript defines 5 types of primitive data types:

• string
• number
• boolean
• null
• undefined

Built-in methods

var message = "Hello world!";
var x = message.toUpperCase();

Adding a Method to an Object

person.name = function () {
return this.firstName + " " + this.lastName;
};

Prototype-based OOP

var genericAnimal = Object.create(null);

genericAnimal.name = 'Animal';

Class-based OOP

function Person(name) { ... }

var adam = new Person('Adam');

Một số phân tích về deadlock trong cơ sở dữ liệu (edit)

1. Nguyên lý consistency của relational database – SQL Server.

SQL Server nói riêng và các database quan hệ nói chung là dạng database có độ nhất quán dữ liệu cao nhất. Dữ liệu trong database luôn ở dạng consistency tại mọi thời điểm. Nghĩa là với cùng một dữ liệu thì không thể có việc đọc ghi tại cùng một thời điểm. Việc đọc ghi cùng một dữ liệu sẽ dẫn tới hiện tượng lock lẫn nhau để đảm bảo dữ liệu được consistency. Điều đó dẫn tới một số tình huống sau:

• Lệnh Select sẽ xung đột với các lệnh Update, Insert, Delete. Vì các lệnh Update, Insert, Delete làm thay đổi dữ liệu của một table do đó tại một thời điểm nếu thực hiện lệnh select thì sẽ không thể insert, update, delete trên cùng một table hoặc ngược lại. Điều này rất quan trọng, vì nếu không lock dữ liệu thì sẽ dẫn tới việc đọc dữ liệu ra sai so với thực tế. Ví dụ: kiểm tra đơn hàng xem đã được xác nhận hay chưa, nhưng ngay tại thời điểm đó đơn hàng được update thành trạng thái xác nhận, như vậy nếu không lock dữ liệu đọc ra sẽ khác với thực tế và có thể dẫn tới các logic bị sai. Tương tự, nếu khi insert hoặc delete dữ liệu xảy ra cùng thời điểm với lệnh tìm kiếm nếu không lock sẽ dẫn tới kết quả sai lệch.
• Các lệnh Select không lock lẫn nhau. Các lệnh select thự hiện chế độ share lock, nghĩa là việc thực hiện lệnh đọc đồng thời cùng một dữ liệu thì không bị ảnh hưởng lẫn nhau.
• Các lệnh Insert, Update, Delete lock theo row không lock theo table. Nghĩa là có thể thực hiện đồng thời các lệnh đó trên cùng một table miễn là chúng không tranh chấp nhau cùng một row dữ liệu. Lock để đảm bảo tính consistency data là một trong các ưu điểm nổi bật của cơ sở dữ liệu quan hệ. Vì vậy nếu đã dùng cơ sở dữ liệu quan hệ thì phải biết tận dụng nó. Tránh tối đa việc sử dụng các chế độ bỏ lock như mode nolock vì có thể dẫn tới hiện tượng đọc sai lệch dữ liệu (lệch giá cả, số lượng, trạng thái …).

2. Lock và deadlock

Lock thực ra không đáng sợ, nó chỉ ảnh hưởng một phần tới performance khi các lệnh thực hiện đồng thời có thể sẽ phải đợi nhau một cách tuần tự. Nhưng deadlock thì khác, nó dẫn tới không lệnh nào được thực hiện. Vậy deadlock là gì?

Deadlock là hiện tượng tranh chấp tài nguyên giữa hai hay nhiều lệnh trong đó lệnh này giữ tài nguyên mà lệnh kia cần dẫn tới việc không lệnh nào có thể kết thúc để giải phóng tài nguyên. Điều đó thể hiện qua hình minh họa:

Trong trường hợp này: Giao dịch T1 giữ được tài nguyên R1, nhưng cần tài nguyên R2 để kết thúc và giải phóng R1, nhưng R2 lại đang bị T2 giữ và T2 cần R1 để kết thúc và sau đó giải phóng R2. Trong trường hợp này cả T1 và T2 không thể kết thúc. Trường hợp này trong SQL Server sẽ phải chọn kill một trong hai giao dịch.

3. Demo tái tạo hiện tượng deadlock giữa các sql transaction.

Một transaction được thực thi và hoàn tất khi và chỉ khi tất cả các lệnh trong một transaction được hoàn tất.

Deadlock trong database sẽ chỉ xảy ra khi có nhiều hơn một tài nguyên cần phải bị chiếm giữa hoàn tất giao dịch. Xét một câu trúc DB đơn giản như sau:

Thực hiện hai giao dịch đồng thời:

• Giao dịch 1:
• Insert bảng A
• Insert bảng B
• Giao dịch 2:
• Select bảng B
• Select bảng A Code demo: Transaction 1:

static void Main( string[] args )
       {
           Console.ReadKey();
           Console.WriteLine( "Start" );
           using ( TransactionScope transaction = new TransactionScope() )
           {
               using ( SqlConnection con = new SqlConnection( connectionString2 ) )
               {
                   con.Open();
                   string cmd = "Insert Into A(Name) Values('Z')";
                   SqlCommand command = new SqlCommand( cmd, con );
                   command.ExecuteNonQuery();

                   Thread.Sleep( 5000 );

                   cmd = "Insert Into B(Name) Values('X')";
                   command = new SqlCommand( cmd, con );
                   command.ExecuteNonQuery();
               }
               transaction.Complete();
           }

           Console.WriteLine( "Finish!" );
           Console.ReadKey();
       }

Transaction 2:

static void Main( string[] args )
       {
           Console.ReadKey();
           Console.WriteLine( "Start" );
           using ( TransactionScope transaction = new TransactionScope() )
           {
               using ( SqlConnection con = new SqlConnection( connectionString2 ) )
               {
                   con.Open();
                   string cmd = "Select * From B";
                   SqlCommand command = new SqlCommand( cmd, con );
                   SqlDataReader reader = command.ExecuteReader();
                   while(reader.Read())
                   {
                       Console.WriteLine( (string)reader[ "Name" ] );
                   }
                   reader.Close();
                   Console.WriteLine( "-------" );
                   cmd = "Select * From A";
                   command = new SqlCommand( cmd, con );
                   reader = command.ExecuteReader();
                   while ( reader.Read() )
                   {
                       Console.WriteLine( (string)reader[ "Name" ] );
                   }
               }
               transaction.Complete();
           }

           Console.WriteLine( "Finish!" );
           Console.ReadKey();
       }

Ở đây, trong giao dịch 1 ta để thêm lệnh Thread.Sleep(5000) để quan sát hiện tượng deadlock được rõ hơn. Thực hiện hai chương trình gần như đồng thời, chương trình của giao dịch 1 chạy trước, ngay sau là chương trình giao dịch 2 thì sẽ ghi nhận deadlock exception xảy ra:

Nguyên nhân xảy ra:

• Giao dịch 1 vào trước chiếm giữ bảng A để insert, sau đó dừng 5s để tiếp tục chiếm giữa bảng B insert. Trong thời gian chờ bảng B hoàn toàn không bị lock. Vì trong một giao dịch nên lệnh Insert vào bảng A chưa kết thúc chừng nào lệnh insert bảng B kết thúc, bảng A bị giữ bởi T1.
• Giao dịch 2 vào sau nhưng chiếm được bảng B để select trước, sau đó nó cần chiếm bảng A để tiếp tục select. Do trong cùng một giao dịch nên bảng B bị giữ chừng nào T2 chiếm được bảng A để thực hiện hoàn tất một giao dịch.

Như vậy cả T1 và T2 đều lock lẫn nhau và không thằng nào thoát ra được. Dẫn tới SQL Server buộc phải kill một trong hai giao dịch, trong trường hợp này là giao dịch T2 bị kill, T1 thực hiện bình thường.

Một điều cần lưu ý là nếu hai giao dịch trên chỉ có các lệnh insert và update, delete mà không có các lệnh select thì khả năng deadlock thấp hơn rất nhiều. Vì các lệnh insert và update chỉ lock row, do đó việc tranh chấp dẫn tới deadlock khó xảy ra.

4. Chiến lược giảm thiếu nguy cơ deadlock.

Không đảm bảo có thể loại bỏ hoàn toàn deadlock, nhưng áp dụng các chiến lược sau sẽ giúp việc xảy ra deadlock ít xảy ra hơn:

• Giảm thiểu số lượng bảng cần tác động trong một giao dịch, giao dịch có số bảng tham gia càng ít thì khả năng xảy ra deadlock càng thấp.
• Kiểm soát việc thưc thi các giao dịch sau cho thứ tự tác động các bảng là giống nhau như vậy sẽ chỉ xảy ra hiện tượng lock chứ không xảy ra hiện tượng deadlock.
• Thực hiện giao dịch dạng batch thay vì gọi nhiều lần từ trong ứng dụng tới database. Nghĩa là thay vì gọi rời rạc thì generate ra một script duy nhất rồi gửi vào database hoặc gọi store procedure để thực thi giao dịch. Việc này giúp giảm thiếu deadlock vì hai lý do sau:
• Giảm thời gian round trip qua đường mạng do việc gọi đi gọi lại do đó giao dịch kết thúc nhanh hơn.
• Việc gửi script dạng batch, hoặc thực thi store procedure giúp sql server phân tích được toàn bộ mã script từ đó tạo ra một execution plan hiệu quả, khả năng tránh deadlock cao hơn. Không như việc gọi thực thi nhiều lần từ trong ứng dụng, SQL Server không thể biết sau lệnh thứ nhất thì lệnh tiếp theo là gì do đó không thể tạo ra chiến lược giải quyết deadlock. Cùng là ví dụ trên nhưng nếu để thành store procedure và thực thi đồng thời thì hiện tượng deadlock không xảy ra:

Transaction 1:

CREATE PROCEDURE [dbo].[Test1]
AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;
     BEGIN TRANSACTION TEST1
   -- Insert statements for procedure here
     Insert Into A(Name) Values('Zz')
     WAITFOR DELAY '00:00:10';
     Insert Into B(Name) Values('Xz')
     COMMIT TRANSACTION TEST1
END

Transaction 2:

CREATE PROCEDURE [dbo].[Test2]

AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;
     BEGIN TRANSACTION TEST2
     select *
     from B

     select *
     from A
     COMMIT TRANSACTION TEST2
END

Mặc dù store procedure Test1 có thời gian delay lâu hơn 10 giây, nhưng hiện tượng deadlock vẫn không xảy ra.

• Tránh tương tác khi thực thi giao dịch để đảm bảo giao dịch thực thi nhanh nhất có thể. Ví dụ: insert vào bảng A, chờ xác nhận người dùng, insert vào bảng B rồi hoàn tất.
• Sử dụng các mức độ lock phù hợp (Isolation Level).

5. Lưu ý về việc sử dụng SQL Transaction.

Việc sử dụng transaction cần phải hiểu ý nghĩa và không nên lạm dụng. Giả sử để thực hiện được một tính năng cần tác động tới hai hoặc nhiều bảng, vậy có nhất thiết phải gói tất cả các lệnh vào một giao dịch? Để trả lời chính xác thì phải hiểu thế nào là một giao dịch.

Một giao dịch là chuỗi các hành động mà nếu một trong các hành động không hoàn thành thì toàn bộ các hành động khác cũng phải không được hoàn thành. Xét ví dụ kinh điển về giao dịch rút tiền:

Lệnh 1: Thực hiện lệnh update trừ tiền

Lệnh 2: Xác nhận người dùng nhận được tiền

Như vậy nếu lệnh 1 trừ tiền xong, nhưng lệnh xác nhận người dùng nhận được tiền không thực thi được thì lệnh 1 cũng phải không được thực hiện mà phải rollback về trạng thái đầu.

Nhưng nếu có một nghiệp vụ cập nhật thông tin hồ sơ cho học sinh khác như sau:

Lệnh 1: cập nhật tuổi bảng profile

Lệnh 2: cập nhật điểm bảng lớp học

Rõ ràng để thực hiện nghiệp vụ trên cần phải thực hiện cả hai hành động trên, nhưng không có nghĩa là nếu lệnh 2 không thành công thì lệnh 1 không được thực hiện, vì sau đó ta hoàn toàn có thể kiểm tra là lệnh 1 mà thực thi rồi thì chỉ thực thi lệnh 2, việc không thực thi đồng thời hai lệnh trên không làm sai nghiệp vụ của hệ thống.

Do đó khi thực hiện giao dịch cần phải rất thận trọng và phải có đánh giá tổng thể xem việc thực thi đó có bị xung đột nhau hay không? Việc sử dụng tràn lan transation, thậm chí cả với các lệnh select đọc dữ liệu là vô cùng tai hại và đó là thể hiện sự máy móc, thiếu hiểu biết trong lập trình.

• Tránh sử dụng transaction tối đa có thể
• Transaction càng ngắn càng tốt

6. Lưu ý về việc sử dụng các mode nolock, readpast.

Lock trong CSDL là vô cùng quan trọng, nó đảm bảo tính consistency database. Nhưng trong một số trường hợp cần hiệu năng cao thì có thể sử dụng một số mode đặc biệt để đọc, ghi mà không cần lock dữ liệu:

• Nolock: là bỏ hoàn toàn chế đố lock, cho phép đọc dữ liệu bẩn (dirty read).
• Readpast: đọc các dữ liệu không bị lock, bỏ qua các dữ liệu đang bị lock (bởi lệnh insert, update…). Cả hai lệnh trên đều ảnh hưởng tới consistency data. Do đó phải đặc biệt thận trọng khi sử dụng và chỉ khi sử dụng vào những trường hợp vô cùng đặc biệt. Vì nếu không sau này có thể xảy ra các sai lệch dẫn tới các nghiệp vụ bị sai. Mà việc trace lại là vô cùng khó khăn, do việc sai lệch chỉ diễn ra trong tích tắc. Ví dụ như sai lệnh số lượng hàng hóa trong kho, sai lệch về giá…

Sql View Info (edit)

Lấy thông tin Description của một cột trong SQL Server

select 
    st.name [Table],
    sc.name [Column],
    sep.value [Description]
from sys.tables st
inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id
                                        and sc.column_id = sep.minor_id
                                        and sep.name = 'MS_Description'
where st.name = 'T_Fighting'
and sc.name = 'Status'

Lấy thông tin về Table, Column, DataType trong SQL Server

SELECT TAB.NAME AS TableName, TAB.object_id AS ObjectID, COL.NAME AS ColumnName, TYP.NAME AS DataTypeName, TYP.max_length AS MaxLength
FROM   sys.columns COL
       INNER JOIN sys.tables TAB ON COL.object_id = TAB.object_id
       INNER JOIN sys.types TYP ON TYP.user_type_id = COL.user_type_id
where TAB.name = 'T_Fighting'

--where TYP.name = 'varchar'
--where COL.name = 'Status'
--where TAB.name = 'T_Fighting'

-- where TAB.name = ''
-- Uncomment above line and add to fetch details for particular table 

-- where COL.name = '' 
-- Uncomment above line and add to fetch details for particular column names 

-- where TYP.name = '' 
-- Uncomment above line and add to fetch details for particular Data Type

Share links edit

75 Important queries in SQL Server every developer should know

http://www.codingfusion.com/post/75-Important-queries-in-SQL-Server-every-developer-should-know

Everywhere JSON so why not in SQL SERVER–New feature in SQL SERVER 2016

https://www.codeproject.com/Articles/1240829/Everywhere-JSON-so-why-not-in-SQL-SERVER-New-featu

Từ khóa

• Vanilla JavaScript
• Pure JavaScript

Bài viết

JavaScript Design Patterns: IIFE pattern

https://www.codebyamir.com/blog/javascript-designpattern-iife

Tạo Dropdown list từ JSON và sử dụng AJAX (XMLHttpRequest) + JavaScript hoặc sử dụng jQuery

https://www.codebyamir.com/blog/populate-a-select-dropdown-list-with-json

Ví dụ mẫu về Bootstrap

https://www.codebyamir.com/blog/bootstrap-fundamentals

https://www.codebyamir.com/blog/creating-a-fixed-header-on-a-scroll-event

Hiểu về Web Storage API - Local Storage & Session Storage

https://www.codebyamir.com/blog/web-storage-api-localstorage-sessionstorage

Hiểu về tấn công CSRF

https://www.codebyamir.com/blog/understanding-preventing-csrf

Làm việc với Excel và thư viện EPPlus

https://www.codebyamir.com/blog/create-excel-files-in-c-sharp

1. TRUNCATE và DELETE các cậu có phải anh em?

• Hồi mới đi phỏng vấn mình có gặp 1 câu hỏi rất chí đó là "Nếu người dùng xóa lịch sử giao dịch của họ em sẽ dùng TRUNCATE hay DELETE ?" Vậy ta dùng gì đây nhỉ?
• Trước tiên ta có cú pháp của TRUNCATE như sau:

TRUNCATE TABLE table_name

• còn DELETE là như này:

DELETE FROM table_name WHERE ...

• Nếu chỉ như này thì ta có thể thấy TRUNCATE đơn thuần chỉ là xóa dữ liệu trong bảng mà không làm thay đổi cấu trúc của bảng. Nếu như vậy tại sao chúng ta lại cần phải tạo ra nó trong khi ta cũng có thể làm nhiệm vụ đấy với DELETE theo cú pháp sau?

DELETE FROM table_name

• Trong khi đó DELETE của chúng ta còn có thể where để xóa chính xác từng bản ghi còn TRUNCATE mong mỏi mãi cũng chả có where. Tuy vậy theo Document của SQL khi bạn dùng TRUNCATE trong Transaction log không có ghi lại những dòng xóa lịch sử mờ ám, còn đối với DELETE nhất cử nhất động của bạn đều được gi lại.
• Từ đây ta có thể suy được những sự giống và khác nhau của 2 câu lệnh này

• Tuy nhiên mọi người thường bảo dùng TRUNCATE xóa nhanh hơn DELETE là vì nó không có gì lại lịch sử action trong tran log. Trên thực tế điều này không chính xác, bản chất TRUNCATE làm việc nhanh hơn DELETE là vì cơ chết ghi Log của chúng khác nhau Thằng DELETE nó ghi lại hầu như những gì nó xóa vì thế nếu 1 2 rows thì Transaction log dễ dàng chứa được nhưng nếu dữ liệu lên đến mức rất lớn thì dễ sảy ra việc bị tràn log. Trong khi đò TRUNCATE cũng là xóa nhưng thay vì xóa hẳn dữ liệu chúng chỉ hủy cấp phát các trang dữ liệu được dùng lưu trữ dữ liệu trong bảng và ghi lại chỉ các trang được hủy cấp phát trong transaction log. Dữ liệu vẫn tồn tại cho đến khi nó bị ghi đè hoặc shrunk
• Vậy đáp án của câu hỏi kia là dùng DELETE vì TRUNCATE đâu có where, thật bất ngờ phải không.

2. HAVING với WHERE vấn đề muôn thủa

• Nếu từng học môn SQL hay Hệ quản trị CSDL,.. hoặc bất kì một môn nào đó có liên quan đến SQL, thì bạn đã từng được nếm trải qua câu hỏi sự khác nhau giữa HAVING và WHERE rồi nhỉ. Đây là một câu hỏi Key mà làm nao lòng của chúng ta bất kể bạn có nghĩ mình nắm chắc như nào nhưng khi test thì kết quả vẫn sai
• Ai cũng biết WHERE dùng để filter theo row còn HAVING dùng để filter theo GROUP nhớ là Filter nhé chứ không phải chọn đâu. Nhưng tại sao Having có thể thay thế được cho Where như ví dụ sau đây.

// lấy tất cả
(1) select * from tbl_user

• còn đây là 2 câu lệnh lọc sử và có cùng kết quả.

(2) select * from tbl_user where age > 22
(3) select * from tbl_user having age > 22

• Thực chất nếu bạn đẻ ý kỹ trong mysql và sqlserver việc bạn viết khôi lệnh (1) cũng đồng nghĩa bạn thực hiện khối lệnh sau.

(4) SELECT * FROM tbl_user GROUP BY `primary key` ORDER BY `primary key` ASC

• Như vậy đồng nghĩa với việc những câu Select querry của bạn luôn luôn có group by vì thế nó luôn thực hiện được mệnh đề Having.
• Tương tự Having là câu lệnh điều kiện để dùng trong Group by bởi khi muốn dùng các Hàm tổng hợp (AGGREGATE function) được định nghĩa trên 1 tập hợp thì ta phải dùng đến Group by.
• Từ đây ta có thể thấy Where và Having đều làm được những thứ giống nhau. Nhưng Having thì có thể thay thế cho Where, còn where thì không bao giờ.

• Có một lưu ý nhỏ như thế này nếu bạn sử dụng group by khác mặc định như (4) thì cách thức hoạt động có thể hình dung như sau:

1. Group by tạo bảng ảo với điều kiện Having
2. Thực hiện các hàm tổng hợp
3. trả về bảng kết quả

3. Kết luận.

• Trong thực tế bạn không cần nắm quá rõ TRUNCATE và DELETE bởi việc dùng TRUNCATE chỉ cho các bài toán đặc trưng hoặc sếp bạn muốn bạn làm thế.
• Còn việc Having và Where thực chất bạn dùng where và sub querry cũng có thể giải quyết được vấn đề nhưng nếu gặp các bài toán về thống kê, tính toán nhóm thì việc dùng Having rất có lợi và còn làm giảm số lượng câu querry của chương trình.

SQL Server (edit)

SQL Server Reporting Services (SSRS)

SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft. It is part of suite of Microsoft SQL Server services, including SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services).

SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks.

COLLATE

CREATE DATABASE MyDB
COLLATE SQL_Latin1_General_CP1_CI_AS;
GO

ALTER DATABASE MyDB
COLLATE Vietnamese_CI_AS;
GO

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

ALTER TABLE YourTableName
  ALTER COLUMN OffendingColumn
    VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL

COLLATE: xác định hệ thống ngôn ngữ cho DB của bạn. Liên quan đến việc xắp xếp chuỗi là chủ yếu.

https://ppolyzos.com/2016/12/07/change-sql-server-database/

Để nhập dữ liệu tiếng Việt Unicode (dựng sẵn) thì chỉ cần dùng collation SQL_Latin1_General_CP1_CI_AS (mặc định) là được. Tuy nhiên kết quả sắp xếp lại không đúng theo thứ tự trong tự điển.

Windows System Locale: Vietnamese, Default SQL Collation: Vietnamese_CI_AS

Không cần phải đổi Windows System Locale để có Vietnamese_CI_AS collation. Bạn có thể override default collation, như sau:

SELECT * FROM aTable ORDER BY aColumn COLLATE Vietnamese_CI_AS

https://www.ddth.com/archive/index.php/t-125380.html

Sort data in correct Vietnamese alphabetical order

Cách sửa lỗi conflict collation trong MS SQL Server

Trong quá trình cài đặt SQL Server, do không chú chọn Collation phù hợp dẫn đến khi vận hành thường bị conflic collation của SQL Server và Database.

Có nhiều cách để sửa lỗi này. 
1. Cách tốt nhất là cài lại SQL Server và chọn Collation phù hợp. 
2. Nếu không muốn cài lại SQL Server, đây là một trong những cách mà tôi đã làm thành công.

• Tìm một instance SQL ở máy nào đó, đã cài đúng collation (chú ý là cũng phải đúng phiên bản SQL server).
• Backup database Model (đây là một database nằm trong phần Sys Databases).
• Restore database Model đúng đã backup ở bước trên sang instance SQL bị lỗi Conflic Collation.
• Restart lại service SQL Server và kiểm tra lại.

C# Sort List of Strings with Localization

https://docs.microsoft.com/en-us/dotnet/standard/collections/comparisons-and-sorts-within-collections

https://stackoverflow.com/questions/5544955/sort-list-by-localization

Unit Test

Unit Test with xUnit & Moq

Test-Driven Development (TDD)

Behavior-Driven Development (BDD)

SELECT - GROUP BY- Transact-SQL

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

• GROUP BY ROLLUP
• GROUP BY CUBE ( )
• GROUP BY GROUPING SETS ( )

GROUP BY (Transact-SQL)

https://technet.microsoft.com/en-us/library/ms177673(v=sql.110).aspx

Show all tables that have specified column name

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%AuditId%';

Microsoft SQL Server (edit)

Data Type

https://www.tutorialrepublic.com/sql-reference/sql-server-data-types.php

Execution Plan

https://www.sqlshack.com/sql-server-query-execution-plan-beginners-types-options/

SQL Profiler Duration unit

https://dba.stackexchange.com/questions/165912/duration-unit-in-sql-server-trace-file

Tìm hiểu về SCOPE_IDENTITY() trong MS SQL Server

Mấy cái này tài liệu đầy trên mạng, search google cái ra liền, hồi trước tui cũng có coi rồi mà cái tự nhiên nay xài lại quên mất tiêu nên sau khi xài xong ghi lại đây để mai mốt có quên nữa thì coi cho dễ:

@@IDENTITY: biến toàn cục, nếu trong table vừa Insert vào có trigger thì nó sẽ trả về identity của bên table mà trigger tác động chứ không phải table bạn vừa Insert nhé.

Ví dụ bạn thêm 1 bản ghi vào bảng Order, và có 1 trigger âm thầm thêm 1 bản ghi vào bảng OrderHistory, thì nếu dùng @@IDENTITY, bạn sẽ nhận được giá trị cuối cùng của cột Identity của bảng OrderHistory, nếu dùng SCOPE_IDENTITY, bạn sẽ nhận được giá trị cuối cùng của cột Identity của bảng Order.

Vụ này không để ý kỹ lộn hoài!

SCOPE_IDENTITY(): hàm có sẵn, trả về identity của table vừa Insert vào.

IDENT_CURRENT(‘table_name’): hàm có sẵn, cũng giống như SCOPE_IDENTITY() nhưng khác là không phụ thuộc vào session đang kết nối đến SQL Server.

Tối ưu hóa C# và SQL Server

1) Nạp dữ liệu – DataSet và DataReader

DataReader nhanh hơn gấp 2 lần.

2) Thuộc tính CommandType của đối tượng Command

• CommandType.Text
• CommandType.StoredProcedure
• CommandType.TableDirect

Nhanh nhất là TableDirect, nhưng nên dùng StoredProcedure trong bất kỳ hoàn cảnh nào.

3) Chỉ nạp những cột cần thiết

4) Tìm kiếm dữ liệu – DataTable và DataView

DataView nhanh hơn gấp 1.5 lần.

5) Lọc dữ liệu – DataTable và DataView

DataView nhanh hơn gấp 100 lần.

Tham khảo: https://thuonglv.wordpress.com/2014/05/18/c-toi-uu-hoa-chuong-trinh-c-co-so-du-lieu/

SQL (edit)

SQL CookBook

SQL Cookbook: query solutions and techniques for all sql users | Anthony Molinaro, Robert de Graaf | download (vn1lib.org)

SQL Cheat Sheet

https://www.sisense.com/blog/sql-symbol-cheatsheet/

SQL Cheat Sheet: Retrieving Column Description in SQL Server

https://www.sisense.com/blog/sql-cheat-sheet-retrieving-column-description-sql-server/

In SQL Server, details regarding a specific table column (e.g., column name, column id, column data type, column constraints) can be retrieved by joining system tables such as sys.tables, sys.columns, and sys.types.

PRINT 1..100

;WITH CTE AS (
  SELECT COUNT=1
  UNION ALL
  SELECT COUNT=COUNT+1
  FROM CTE WHERE COUNT<100
)
SELECT COUNT FROM CTE

;WITH Numbers(Number) AS (
  SELECT 1
  UNION ALL
  SELECT Number + 1
  FROM Numbers
  WHERE Number <= 999999
)
SELECT * FROM Numbers OPTION (MAXRECURSION 0)

select number from master..spt_values 
where type = 'p' 
and number between 1 and 100 
order by number

STUFF

SELECT TABLE_NAME ,
STUFF(( SELECT ',' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS Columns
WHERE Tables.TABLE_NAME = Columns.TABLE_NAME
ORDER BY COLUMN_NAME
FOR
XML PATH('')
), 1, 1, '') ConcatColumnNames
FROM INFORMATION_SCHEMA.COLUMNS Tables
GROUP BY TABLE_NAME;

COALESCE

SET @str=COALESCE(@str+','+Name,Name) FROM dbo.Customer

PRINT @str;

DENSE_RANK

DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary) AS drank

Tối ưu hóa câu lệnh truy vấn SQL

+ Dùng index để tìm kiếm nhanh hơn

+ Dùng join thay vì sub-query

+ Chỉ chọn những trường cần thiết

+ Dùng exists thay vì count

+ Tránh dùng CURSOR

13 câu lệnh SQL quan trọng Programmer nào cũng cần biết

Cơ sở dữ liệu là một phần không thể thiếu của những trang web hiện đại. Trang web lớn hoặc web động đều sử dụng database theo một cách nào đó và khi được kết hợp với Structured Query Language (SQL) thì khả năng thao tác dữ liệu thực sự là vô tận. Nếu đã biết SQL mà lại còn là lập trình viên thì bạn hãy chắc chắn rằng mình đã nắm chắc 13 câu lệnh SQL quan trọng mà chúng tôi đề cập đến trong bài viết này nhé!

Có rất nhiều tên dữ liệu được trả về từ bảng dữ liệu. Dữ liệu thường được gọi là Rows (hàng), Records (bản ghi) hoặc Tuples. Những thuật ngữ vừa liệt kê sẽ được sử dụng thay thế cho nhau trong suốt bài viết này.

Lời nói đầu

Tất cả các ví dụ ngày hôm nay sẽ được dựa trên bốn bảng giả định. Bảng customers có tên và tuổi của khách hàng: 

Bảng heights có chứa tên và chiều cao của bất kỳ người nào:

Bảng staff có tên và tuổi của nhân viên - chính xác như customers:

Bảng cuối cùng được gọi là people có tên và tuổi của người, giống như bảng customersvà staff:

1. SELECT

Câu lệnh SELECT là đơn giản nhất, và bạn cần phải hiểu nó vì nó làm cơ sở cho khá nhiều lệnh khác. Hãy cân nhắc việc luyện tập viết các lệnh SQL bằng chữ hoa, vì nó làm cho câu lệnh dễ đọc và dễ hiểu hơn.

Như tên của nó ngụ ý, SELECT được sử dụng để chọn dữ liệu từ cơ sở dữ liệu. Đây là cách sử dụng đơn giản nhất:

SELECT * FROM table;

Câu lệnh trên có hai phần:

• SELECT *: xác định cột bạn muốn chọn, dấu * ở đây hiểu là bạn muốn chọn tất cả các cột trong bảng.
• FROM table: phần này nói với công cụ cơ sở dữ liệu nơi bạn muốn trích xuất dữ liệu, thay thế "table" bằng tên của bảng cơ sở dữ liệu cần lấy.

Câu lệnh SELECT này được gọi là "select star", sử dụng dấu * là một phương pháp khá hay giúp tìm, tính toán dữ liệu trong bảng, nhưng không phải lúc nào cũng dùng câu lệnh này. Khi sử dụng select star, việc trình bày dữ liệu trả về như thế nào hoàn toàn phụ thuộc vào engine của database, bạn không thể kiểm soát thứ tự dữ liệu được trả về, vì vậy, nếu có ai đó thêm cột mới vào bảng, bạn thấy các biến trong ngôn ngữ lập trình của mình không hiển thị dữ liệu đúng. May mắn là có một giải pháp khác cho vấn đề này.

Bạn có thể nói rõ các cột muốn truy xuất, như sau:

SELECT age, name FROM people;

Truy vấn này sẽ trích xuất cột name và age từ bảng people. Việc này có vẻ hơi nhàm chán nếu bạn có quá nhiều dữ liệu, nhưng làm vậy sẽ giúp giảm nhiều vấn đề có thể xảy ra trong tương lai, cũng như làm cho SQL dễ hiểu hơn với các lập trình viên mới sau này.

Nếu bạn muốn chọn thêm dữ liệu bổ sung, nhưng nó không được lưu trữ trong bất kỳ bảng nào, thì có thể làm như sau: 

SELECT age, '1234' FROM people;

Bất kỳ chuỗi nào bên trong dấu nháy đơn sẽ được trả về thay vì tên cột phù hợp.

2. WHERE

Câu lệnh SELECT là lựa chọn tuyệt vời để lấy dữ liệu, nhưng nếu bạn muốn lọc kết quả kỹ hơn chút nữa, ví như, chỉ muốn trích xuất ra những người có màu mắt xanh, người sinh tháng 1 và làm thợ cơ khí thì phải làm sao? Đây chính là lúc sử dụng câu lệnh WHERE. WHERE cho phép áp dụng thêm các điều kiện vào SELECT, bạn chỉ cần nối nó vào cuối cùng của câu lệnh là được:

SELECT age, name FROM people WHERE age > 10;

Truy vấn này được giới hạn cho những người có tuổi lớn hơn 10. Bạn có thể kết hợp nhiều điều kiện bằng cách sử dụng toán tử AND:

SELECT age, name FROM people WHERE age > 10 AND age < 20;

Lệnh AND làm việc chính xác như nghĩa của nó trong tiếng Anh: Nó áp dụng những điều kiện khác nhau cho câu lệnh. Trong ví dụ trên, dữ liệu được trả về sẽ là bất kỳ bản ghi nào có tuổi nằm giữa 10 và 20. Do không có kết quả nào phù hợp nên không có dữ liệu nào được trả lại.

Một lệnh khác có thể được sử dụng để kết hợp điều kiện là OR. Đây là ví dụ:

SELECT age, name FROM people WHERE age > 10 OR name = 'Joe';

Truy vấn này yêu cầu trả về những bản ghi có tuổi lớn hơn 10 hoặc tên là Joe. Chú ý, ở đây chỉ có một dấu "=", nhưng nhiều ngôn ngữ lập trình sử dụng 2 dấu bằng (==) để kiểm tra sự tương đương, điều này không cần thiết cho phần lớn các engine của database, xong bạn vẫn nên kiểm tra kỹ trên môi trường làm việc của cơ sở dữ liệu.

3. ORDER

Lệnh ORDER được sử dụng để sắp xếp kết quả trả về, sử dụng ORDER khá đơn giản, chỉ cần thêm ORDER vào cuối câu lệnh như ví dụ dưới đây:

SELECT name, age FROM people ORDER BY age DESC;

Nếu cần chọn cột và thứ tự cụ thể, bạn có thể làm như sau (ASC là tăng dần, DESC là giảm dần): 

SELECT name, age FROM people ORDER BY name ASC, age DESC;

ORDER BY có lẽ là hữu ích nhất khi kết hợp với các lệnh khác. Không phải tất cả các truy vấn sẽ trả về dữ liệu một cách hợp lý hoặc có trật tự - lệnh này cho phép bạn thay đổi điều đó.

4. JOIN

Lệnh JOIN được sử dụng để kết hợp các dữ liệu liên quan được lưu trữ trong một hoặc nhiều bảng. Bạn có thể nối bảng thứ hai vào bảng đầu tiên, và chỉ định cách dữ liệu được kết nối. Dưới đây là ví dụ cơ bản:

SELECT age, name, height FROM people LEFT JOIN heights USING (name);

Có một vài chú ý ở đây. Bạn phải bắt đầu với cú pháp "LEFT JOIN", hiểu rằng bạn muốn nối một bảng bằng cách sử dụng một kiểu nối LEFT. Tiếp theo, xác định bảng mà bạn muốn nối (heights). Cú pháp USING (name) cho biết cột "name" có thể được tìm thấy trong cả hai bảng và cột này sẽ được sử dụng như một chìa khóa để kết hợp các bảng với nhau.

Đừng lo lắng nếu các cột của bạn có tên khác nhau trong mỗi bảng. Bạn có thể sử dụng "ON" thay vì "USING":

SELECT age, name, height FROM people LEFT JOIN heights ON (namea = nameb);

Lệnh ON sẽ xác định rõ cột nào là chìa khóa để nối. Có rất nhiều kiểu nối mà bạn sẽ cần chút thời gian để tìm hiểu chi tiết, đây là một bản tóm tắt nhanh:

• (INNER) JOIN: Trả về các hàng có trong cả hai bảng.
• LEFT (OUTTER) JOIN: Trả về tất cả các hàng từ bảng bên trái cùng với những bản ghi phù hợp ở bảng bên phải. Nếu không có bản ghi nào phù hợp thì những bản ghi ở bảng bên trái vẫn được trả về.
• RIGHT (OUTER) JOIN: Trái ngược với kiểu nối bên trên, tất cả các hàng của bảng bên phải sẽ được trả về cùng với những hàng phù hợp của bảng bên trái.
• FULL (OUTER) JOIN: Trả về tất cả những bản ghi phù hợp ở trong hai bảng.

Cú pháp INNER hay OUTER là tùy chọn, nó làm cho mọi thứ dễ hiểu hơn nhưng không nhất thiết lúc nào bạn cũng bắt buộc phải dùng đến chúng.

5. ALIAS

Bây giờ bạn đã biết những câu lệnh cơ bản rồi, thử tiếp với lệnh ALIAS xem sao nhé.

Câu lệnh này được sử dụng để tạm thời đổi tên một bảng, tên mới này chỉ tồn tại bên trong tiến trình xử lý (transaction) bạn đang chạy. Đây là cách sử dụng:

SELECT A.age FROM people A;

Có thể sử dụng bất kỳ tên phù hợp nào bạn muốn, trong ví dụ này tôi sử dụng các chữ cái trong bảng chữ cái. Trước mỗi tên cột, ALIAS sẽ được đặt trước. ALIAS này được gán cho bảng ngay sau khi khai báo. Tương tự:

SELECT people.age FROM people;

Thay vì phải nhập tên bảng dài, bạn chỉ cần nhập chữ cái đơn giản, dễ nhớ. Nhưng ở đây có một vấn đề nhỏ, nếu bạn chọn từ nhiều bảng, rất dễ bị nhầm lẫn giữa các cột trong bảng. Trong trường hợp các bảng đó có những cột giống tên nhau, truy vấn cơ sở dữ liệu có thể bị lỗi vì không tham chiếu chính xác được đến tên bảng hoặc ALIAS. Đây là ví dụ với hai bảng:

SELECT staff.age, staff.name, customers.age, customers.name FROM staff, customers;

Và đây là truy vấn tương tự với các ALIAS:

SELECT A.age, A.name, B.age, B.name FROM staff A, customers B;

Bảng staff được gán tên mới là A, bảng customers được gán tên mới là B. Các bảng này giúp code dễ hiểu hơn và giảm số lượng chữ cần phải gõ.

Nếu muốn đổi tên cột với ALIAS, bạn sử dụng lệnh AS:

SELECT age AS person_age FROM people;

Khi truy vấn này được thực hiện, cột sẽ được gọi là "person_age" thay vì "age".

6. UNION

UNION là một lệnh tuyệt vời. Nó cho phép bạn nối các hàng với nhau. Không giống như lệnh JOIN chỉ nối thêm các cột phù hợp, UNION có thể nối các hàng không liên quan với nhau nếu có cùng một số lượng cột và tên cột. Đây là cách bạn sử dụng nó:

SELECT age, name FROM customers
UNION 
SELECT age, name FROM staff;

Một câu lệnh UNION sẽ chỉ trả về những kết quả là hàng duy nhất giữa 2 truy vấn, bạn có thể sử dụng cú pháp UNION ALL để trả lại tất cả dữ liệu, kể cả những cái trùng nhau.

SELECT age, name FROM customers
UNION ALL
SELECT age, name FROM staff;

Dù kết quả trả về của 2 câu lệnh trên giống nhau, nhưng bạn nhận thấy thứ tự của các hàng có sự thay đổi, đúng không? UNION hoạt động theo cách hiệu quả nhất, vì vậy dữ liệu trả về có thể khác nhau theo thứ tự.

Một trường hợp nữa có thể sử dụng UNION là tính tổng số phụ (subtotal), bạn kết hợp một truy vấn của tổng số (sum total) vào truy vấn của các tổng số riêng lẻ (individual total) cho một tình huống cụ thể. Nghe lằng nhằng nhỉ!

7. INSERT

6 câu lệnh bên trên đều giúp bạn trích xuất dữ liệu từ database, nếu muốn chèn thêm dữ liệu vào database thì làm thế nào? Đây là lúc cho lệnh INSERT thể hiện:

INSERT INTO people(name, age) VALUES('Joe', 102);

Bạn phải chỉ định tên bảng (people) và cột bạn muốn sử dụng (name và age). Cú pháp VALUES sau đó được sử dụng để cung cấp các giá trị cần chèn. Thứ tự của giá trị cần chèn phải được đặt đúng như thứ tự của các cột đã được chỉ định trước đó.

Bạn không thể chỉ định WHERE để chèn, và cần đảm bảo rằng đã tuân thủ đúng các ràng buộc giữa các bảng.

8. UPDATE

Sau khi chèn thêm dữ liệu, bạn cần phải thay đổi các hàng cụ thể. Đây là cú pháp của lệnh UPDATE:

UPDATE people SET name = 'Joe', age = 101;

Bạn phải chỉ định bảng muốn thay đổi, sau đó sử dụng cú pháp SET để xác định các cột và các giá trị mới của chúng. Câu lệnh trong ví dụ này sẽ cập nhật tất cả bản ghi riêng lẻ.

Để cụ thể hơn, bạn có thể sử dụng WHERE giống như khi thực hiện lệnh SELECT:

UPDATE people SET name = 'Joe', age = 101 WHERE name = 'James';

Thậm chí, có thể sử dụng cả toán tử điều kiện AND, OR:

UPDATE people SET name = 'Joe', age = 101 WHERE (name = 'James' AND age = 100) OR name = 'Ryan';

Hãy chú ý cách mà dấu ngoặc đơn được sử dụng để bắt buộc tuân theo các điều kiện.

9. UPSERT

UPSERT nghe có vẻ lạ, nhưng đây lại là lệnh khá hữu ích. Giả sử có một hạn chế trên bảng dữ liệu là bạn chỉ lưu những bản ghi với tên duy nhất, bạn không muốn có hai hàng trùng tên nhau xuất hiện trong bảng. Khi đó nếu cố gắng chèn nhiều giá trị "Joe" vào thì engine của database sẽ báo lỗi và từ chối làm điều đó (gần như vậy). Lệnh UPSERT cho phép bạn cập nhật bản ghi nếu nó đã tồn tại. Nếu không có lệnh này, bạn sẽ phải viết rất nhiều logic để kiểm tra như kiểm tra xem nó đã tồn tại chưa, nếu chưa tồn tại thì chèn, nếu đã tồn tại thì trích xuất khóa chính (primary key) chính xác của nó rồi cập nhật. Thật là muốn phát điên luôn mà...

Tiếc là lệnh này được thực hiện khác nhau trên những database khác nhau. PostgreSQL gần đây đã có thêm lệnh này, trong khi MySQL đã có từ rất lâu. Đây là cú pháp lệnh UPSERT trên MySQL để bạn tham khảo:

INSERT INTO people(name, age)
VALUES('Joe', 101)
ON DUPLICATE KEY UPDATE age = 101;

Nếu tinh ý, bạn sẽ nhận thấy rằng cách này thực chất là một lệnh cập nhật kết hợp với lệnh chèn, có thể hiểu là "cập nhật nếu chèn không thành công".

10. DELETE

Lệnh DELETE được sử dụng để xóa hoàn toàn các bản ghi, nó có thể khá nguy hiểm nếu bị lạm dụng. Cú pháp của lệnh này khá đơn giản:

DELETE FROM people;

Câu lệnh trên sẽ xóa mọi thứ từ bảng people. Nếu chỉ muốn xóa những bản ghi nhất định hãy sử dụng thêm WHERE:

DELETE FROM people WHERE name = 'Joe';

Nếu bạn đang phát triển một hệ thống thì cách khôn ngoan hơn là sử dụng một lệnh "soft delete". Cụ thể, bạn không bao giờ thực sự chạy một lệnh DELETE, mà tạo một cột đã xóa (chuyển dữ liệu sang đó), kiểm tra cột một lần nữa để tránh những trường hợp xóa nhầm đáng tiếc. Cách này cũng giúp nhanh chóng lấy lại bản ghi nếu phát hiện lỗi hay vấn đề cần kiểm tra lại. Tất nhiên, đây không phải là lựa chọn sao lưu thích hợp đâu nhé. Hãy cứ thực hiện sao lưu hệ thống của bạn, bởi cẩn tắc vô áy náy mà.

11. CREATE TABLE

Vâng, đúng như tên gọi, lệnh này được sử dụng để tạo bảng, và đây là cú pháp của nó:

CREATE TABLE people (
  name TEXT,
  age, INTEGER,
  PRIMARY KEY(name)
);

Chú ý cách các tên cột, ràng buộc nằm trong ngoặc và gán kiểu dữ liệu cho cột được viết như thế nào. Khóa chính cũng cần được chỉ định, đây là yêu cầu đầu tiên của một thiết kế database chuẩn.

12. ALTER TABLE

Lệnh ALTER TABLE được sử dụng để sửa đổi cấu trúc của một bảng. Ở đây có một chút hạn chế, vì cơ sở dữ liệu của bạn sẽ không cho phép thay đổi một bảng nếu dữ liệu đang tồn tại có thể gây ra xung đột, ví dụ, thay đổi một chuỗi thành một số nguyên. Trong những trường hợp này, cần sửa dữ liệu trước, sau đó sửa đổi bảng. Đây là ví dụ:

ALTER TABLE people ADD height integer;

Ví dụ này thêm một cột được gọi là "height" với kiểu dữ liệu là số nguyên vào bảng people. Không có giới hạn về những gì bạn có thể thay đổi.

13. DROP TABLE

Lệnh cuối cùng là DROP TABLE. Lệnh này cũng gần giống với DELETE nhưng thay vì xóa một bản ghi duy nhất, nó xóa mọi bản ghi trong bảng. Đây là cách sử dụng nó:

DROP TABLE people;

Lệnh này khá nguy hiểm, vì thế nên thực hiện nó bằng tay trong phần lớn các trường hợp, đề phòng những lỗi không mong muốn có thể xảy ra.

Xong rồi, 13 lệnh tất cả, hy vọng bạn đã bỏ túi được một số thủ thuật hữu ích khi làm việc với cơ sở dữ liệu. Hãy chia sẻ với chúng tôi những câu lệnh, thủ thuật SQL khác mà bạn đã khám phá được nhé! 

SQL SERVER – Get Current TimeZone Name in SQL Server

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT

SELECT @TimeZone

--SE Asia Standard Time

Handle conversion between time zones in SQL Server

https://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/

https://www.mssqltips.com/sqlservertip/3174/handle-conversion-between-time-zones-in-sql-server--part-2/

https://www.mssqltips.com/sqlservertip/3175/handle-conversion-between-time-zones-in-sql-server--part-3/

Một số bài báo hay

http://www.sqlviet.com/blog/tim-kiem-voi-nhieu-tham-so/

http://www.sqlviet.com/blog/cau-hinh-sql-server-de-gui-mail/

http://www.sqlviet.com/blog/tao-sql-job-backup-database-hang-ngay/

SQL động

http://www.sqlviet.com/blog/sql-dong/

http://www.sqlviet.com/blog/sql-dong/2

Cách 1:

DECLARE @SqlStr NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
SET @SqlStr = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID AND OrderQty > @MinQty'
EXEC SP_EXECUTESQL @SqlStr, N'@ProductID INT, @MinQty INT', 1, 1000

Cách 2:

DECLARE @SqlStr NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
SET @SqlStr = N'SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = @ProductID AND OrderQty > @MinQty'
EXEC SP_EXECUTESQL @SqlStr, N'@ProductID INT, @MinQty INT', @ProductID = 1, @MinQty = 1000

Phân giải tên đối tượng

Đoạn code sau sẽ báo lỗi:

DECLARE @SqlStr NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
SET @SQL = N'SELECT * FROM @Tenbang2 WHERE ProductID = @ProductID2'
SET @ParamDefinition = N'@Tenbang2 NVARRCHAR(100),@ProductID2 int'
EXEC SP_EXECUTESQL
@SqlStr = @SQL,
@params = @ParamDefinition,
@Tenbang2 = @Tenbang,
@ProductID2 = @ProductID

Bạn cần viết như sau (nhớ luôn luôn dùng hàm QUOTENAME với tên đối tượng để ngăn chặn sql injection):

DECLARE @STATEMENT NVARCHAR(MAX),@ParamDefinition NVARCHAR(MAX)
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@Tenbang) + ' WHERE ProductID = @ProductID2'
SET @ParamDefinition = N'@ProductID2 int'
EXEC SP_EXECUTESQL
@STATEMENT = @SQL,
@params = @ParamDefinition,
@ProductID2 = @ProductID

Tìm kiếm với nhiều tham số

Khi ta cần viết một thủ tục để tìm kiếm dữ liệu dựa vào các tham số đầu vào, ta có thể hình dung ra logic sẽ như sau:

IF @Param1 IS NOT NULL
SELECT... FROM dbo.Tblxxx WHERE Col1= @Param1
ELSE
SELECT TOP 200 ... FROM dbo.Tblxxx -- TOP 200 để khống chế số bản ghi khi không có tham số

Tức là khi tham số vào @Param1 được truyền giá trị thì ta lọc các bản ghi dựa trên giá trị đó, còn nếu không (NULL) thì ta không lọc. Tuy nhiên cách làm trên không thể mở rộng với nhiều tham số, vì số nhánh chương trình sẽ tăng rất nhanh (2^n). Ví dụ nếu ta có hai tham số @Param1 và @Param2, đoạn code sẽ giống như thế này:

IF (@Param1 IS NOT NULL) AND (@Param2 IS NOT NULL)
...
ELSE IF (@Param1 IS NOT NULL) AND (@Param2 IS NULL)
...
ELSE IF (@Param1 IS NULL) AND (@Param2 IS NOT NULL)
...
ELSE
...

Không những code rất cồng kềnh mà nó còn rất khó bảo trì. Nếu đến một lúc ta cần thêm một tham số thứ ba @Param3, sẽ tốn rất nhiều công để sửa lại và viết thêm vào đoạn code trên. Hoặc nếu cần thêm một cột vào kết quả đầu ra, ta sẽ phải thêm vào tất cả các nhánh của chương trình. Có thể nói cách làm trên là không khả thi trong đa số trường hợp.

Bài viết này giới thiệu hai cách làm có thể áp dụng trên thực tế, nhưng trước hết tôi nói qua về ví dụ sẽ được sử dụng trong bài.Ta có một database về các bộ phim đã được sản xuất, và giả sử ta cho phép tìm kiếm theo các tiêu chí sau:

@Tenphim:Tên của bộ phim

@NamsxMin: Từ năm sản xuất

@NamsxMax: Đến năm sản xuất

@Nuocsx: Nước sản xuất

@Theloai: Thể loại phim (hành động/hài/chính kịch…)

Cách làm thứ nhất

CREATE PROCEDURE dbo.TimKiemPhim_1
    @Tenphim NVARCHAR(50),
    @NamsxMin INT,
    @NamsxMax INT,
    @Nuocsx NVARCHAR(50),
    @Theloai NVARCHAR(50)
AS
SELECT P.*
FROM dbo.Phim P
WHERE (@Tenphim IS NULL OR P.Tenphim like '%'+@Tenphim+'%')
AND (@NamsxMin IS NULL OR P.Namsx >= @NamsxMin)
AND (@NamsxMax IS NULL OR P.Namsx <= @NamsxMax)
AND (@Nuocsx IS NULL OR P.Nuocsx = @Nuocsx)
AND (@Theloai IS NULL OR P.Theloai = @Theloai)

Trong cách làm này ta khai thác trị chân lý của mệnh đề OR – khi tham số @p là NULL, tức là “@p IS NULL” đúng, thì cả mệnh đề ở mỗi dòng AND đúng. Do đó chỉ khi @p được truyền giá trị thì điều kiện tìm kiếm mới được thực hiện. Như vậy code trông đã gọn hơn, mà mở rộng cũng rất dễ dàng, khi cần bổ sung thêm một tham số thì ta chỉ cần viết thêm một dòng lệnh.

Có những trường hợp khi một tham số nào đó được cung cấp ta cần truy nhập vào bảng khác . Giả sử có thêm tham số @TenDienvien để tìm các phim có một diễn viên nào đó tham gia; và giả sử bảng dbo.Dongphim (Đóng phim) chứa tên các diễn viên tham gia đóng phim và quan hệ của bảng dbo.Phim với bảng này là 1-nhiều (mỗi phim có nhiều diễn viên tham gia). Ta có thể thêm đoạn code sau:

AND (@TenDienvien IS NULL OR
     EXISTS(SELECT 1 FROM dbo.Dongphim D
            WHERE D.PhimID = P.PhimID AND D.TenDienvien like '%'+@TenDienvien+'%')
)

Trong một số tình huống, thủ tục trên có thể chạy rất nhanh ở lần thực hiện đầu nhưng lại chậm hơn nhiều ở lần tiếp theo, khi các tham số tìm kiếm khác với lần đầu. Nguyên nhân của nó là hiện tượng “parameter sniffing” (tôi sẽ nói ở dịp khác). Một cách để khắc phục là thêm lựa chọn “WITH RECOMPILE” vào đoạn khai báo thủ tục, ngay trước từ khóa AS.

Ví dụ về WITH RECOMPILE

EXEC MyStoredProc '12/31/2004' WITH RECOMPILE

Cách làm thứ hai

Dùng sql động, xây dựng chuỗi sql động dựa trên các tham số đầu vào và thực thi chuỗi sql đó.

CREATE PROCEDURE dbo.TimKiemPhim_2
    @Tenphim NVARCHAR(50) = NULL,
    @NamsxMin INT = NULL,
    @NamsxMax INT = NULL,
    @Nuocsx NVARCHAR(50) = NULL,
    @Theloai NVARCHAR(50) = NULL
AS
DECLARE @SqlStr NVARCHAR(MAX),
        @ParamList NVARCHAR(2000)
SELECT @SqlStr = '
       SELECT P.*
       FROM dbo.Phim P
       WHERE (1=1)
       '
IF @Tenphim IS NOT NULL
       SELECT @SqlStr = @SqlStr + '
              AND (P.Tenphim like '''%'+@Tenphim2+'%''')
              '
IF @NamsxMin IS NOT NULL
       SELECT @SqlStr = @SqlStr + '
              AND (P.Namsx >= @NamsxMin2)
              '
IF @NamsxMax IS NOT NULL
       SELECT @SqlStr = @SqlStr + '
             AND (P.Namsx <= @NamsxMax2)
             '
IF @Nuocsx IS NOT NULL
       SELECT @SqlStr = @SqlStr + '
              AND (P.Nuocsx = @Nuocsx2)
              '
IF @Theloai IS NOT NULL
       SELECT @SqlStr = @SqlStr + '
              AND (P.Theloai = @Theloai2)
              '
SELECT @Paramlist = '
       @Tenphim2 NVARCHAR(50),
       @NamsxMin2 INT,
       @NamsxMax2 INT,
       @Nuocsx2 NVARCHAR(50),
       @Theloai2 NVARCHAR(50)
       '
EXEC SP_EXECUTESQL @SqlStr,
                   @Paramlist,
                   @Tenphim,
                   @NamsxMin,
                   @NamsxMax,
                   @Nuocsx,
                   @Theloai

Với cách làm này việc viết code có rườm rà và khó theo dõi hơn. Tuy nhiên trong một số trường hợp cách này lại có ưu điểm hơn cách thứ nhất:

1. Thủ tục sp_executesql sẽ lưu kế hoạch thực thi cho mỗi bộ tham số, do đó nó giải quyết vấn đề “parameter sniffing” một cách thông minh hơn so với cách thứ nhất (luôn luôn phải biên dịch lại).

2. Trong trường hợp ta cần SELECT dữ liệu từ các bảng khác nhau tùy theo tham số được truyền. Ví dụ ta có tham số @Phimkinhdien kiểu BIT, khi bằng 1 thì cần SELECT từ bảng dbo.Phimkinhdien, khi bằng 0 thì SELECT từ bảng dbo.Phim như trên. Với cách làm dùng sql động ta có thể dễ dàng làm như sau:

...
SELECT @SqlStr = '
              SELECT P.*
              FROM ' + CASE WHEN @Phimkinhdien=1 THEN 'dbo.Phimkinhdien' ELSE 'dbo.Phim' END+'
              WHERE (1=1)
              '

Với cách làm thứ nhất, ta không có cách nào khác là tạo thêm một nhánh, trong đó lặp lại câu lệnh SELECT và thay bảng dbo.Phim bằng dbo.Phimkinhdien

Bổ sung: một số bạn viết thư hỏi dùng EXEC thay cho sp_executesql có được không. Câu trả lời là bạn nên dùng sp_executesql và tránh EXEC, vì sp_executesql tăng khả năng dùng lại kế hoạch thực thi, trong khi EXEC luôn dẫn đến thủ tục phải biên dịch lại. Một lý do nữa là sp_executesql tránh được lỗi SQL injection, EXEC thì gặp lỗi này. Tôi sẽ trở lại vấn đề so sánh giữa sp_executesql và EXEC trong một dịp khác.
Một bạn nêu trường hợp các cột cần trả về thay đổi tùy theo giá trị của tham số, ví dụ nếu tham số @p=1 thì SELECT các cột col1, col2, col3, còn nếu @p=2 thì SELECT col4, col5, col6. Khi đó cách làm thứ hai ở trên có thể áp dụng dễ dàng, và đây cũng là một trường hợp nó có ưu thế hơn cách làm thứ nhất.

Một số điều thú vị trong SQL Server

-- Kiểm tra xem database của bạn có cài đặt FULLTEXT SEARCH hay không?
SELECT SERVERPROPERTY('IsFullTextInstalled')

-- Bạn chạy 2 lệnh sau rồi hãy chạy lệnh trên, nó sẽ in ra chi tiết các thông tin vào ra và thời gian xử lý của CPU:

SET STATISTICS IO ON
SET STATISTICS TIME ON

Lấy bản ghi tuần tự

SELECT *
FROM Sales.Customer;

Lấy bản ghi ngẫu nhiên

SELECT C.*
FROM Sales.Customer C
JOIN ( SELECT CustomerID ,
ROW_NUMBER() OVER ( PARTITION BY TerritoryID ORDER BY NEWID() ) AS ID
FROM Sales.Customer
) RC -- Random Customer
ON C.CustomerID = RC.CustomerID
WHERE RC.ID <= 2
ORDER BY C.TerritoryID ,
C.CustomerID;

Hiểu thêm về Index trong SQL

Xem xét ví dụ sau

USE AdventureWorks
GO
-- câu lệnh 1 (non-sargable)
SELECT * FROM Sales.Individual
WHERE CustomerID+2 = 11002
 
-- câu lệnh 2 (sargable)
SELECT * FROM Sales.Individual
WHERE CustomerID = 11000

Hai câu lệnh trên cho cùng một kết quả, nhưng ở câu lệnh 1 điều kiện tìm kiếm của nó là non-sargable và index trên cột CustomerID trở nên vô dụng. Kế hoạch thực thi của nó cho thấy điều này:

Câu lệnh 1 dẫn đến thao tác Clustered Index Scan, tức là quét cả cây clustered index, đồng nghĩa với quét bảng (vì clustered index chính là bảng). Do vậy mà chi phí của nó tăng vọt. Sở dĩ index đã không được sử dụng vì khi bạn áp dụng một phép tính toán trên cột, hệ thống phải thực hiện tính toán đó trên từng node trên cây index trước khi có thể lấy kết quả để so sánh với giá trị cần tìm. Vì thế nó phải duyệt tuần tự qua từng node thay vì tìm theo kiểu nhị phân (index seek, như với câu lệnh 2). Và đây là các con số thống kê về IO và thời gian thực hiện:
Câu lệnh 1 (non-sargable):
Table 'Individual'. Scan count 1, logical reads 3088, physical reads 35
CPU time = 0 ms, elapsed time = 259 ms.

Câu lệnh 2 (sargable):
Table 'Individual'. Scan count 0, logical reads 3, physical reads 3
CPU time = 0 ms, elapsed time = 19 ms.

Trên đây là một trường hợp đơn giản mà có lẽ không mấy ai mắc phải, tuy nhiên có những tình huống khác không hiển nhiên như thế. Ví dụ, khi cần tìm tất cả các đơn hàng được thực hiện trong ngày 21/08/2009, một cách trực giác có thể bạn nghĩ ngay đến một trong các cách làm sau:

SELECT *
FROM dbo.DonHang
WHERE CONVERT(VARCHAR,OrderDate,103) = '21/08/2009' --cắt bỏ phần thời gian, chỉ giữ lại phần ngày
 
-- hoặc
SELECT *
FROM dbo.DonHang
WHERE DATEPART(d,OrderDate) =21
AND DATEPART(m,OrderDate)=8
AND DATEPART(YEAR,OrderDate)=2009

Cả hai cách viết trên đều làm mất tác dụng index trên trường OrderDate. Cách viết đúng phải là:

SELECT *
FROM dbo.DonHang
WHERE OrderDate >= '20090821' AND OrderDate < '20090822'
-- lưu ý chuỗi ngày tháng mặc định của ANSI có dạng yyyymmdd

Một ví dụ khác, bạn cần tìm tất cả các khách hàng có tên bắt đầu bằng chữ C, như Can, Công, Cường… Các cách viết sau là không sargable:

SELECT *
FROM dbo.Customer
WHERE SUBSTRING(Ten,1,1) = 'C'
 
--hoặc
SELECT *
FROM dbo.Customer
WHERE LEFT(Ten,1) = 'C'

Cách viết để thành sargable là:

SELECT *
FROM dbo.Customer
WHERE Ten LIKE 'C%'

Ở đây có một chi tiết thú vị, khi quan sát kế hoạch thực thi của câu lệnh ở ngay trên, bạn sẽ thấy điều kiện “Ten LIKE ‘C%’” được chuyển thành “Ten >=’C’ AND Ten< ‘D’” (vâng, chuỗi cũng so sánh lớn bé được như số), và điều kiện này hoàn toàn thích hợp với việc tìm kiếm bằng index.

Tuy nhiên, nếu bạn thay đổi yêu cầu một chút, tìm các khách hàng có tên chứa chữ ‘C’ ở bất kỳ vị trí nào, không chỉ ở đầu. Câu lệnh của bạn sẽ trở thành:

SELECT *
FROM dbo.Customer
WHERE Ten LIKE '%C%'

Khi đó index trên trường Ten lại bị mất tác dụng, vì hệ thống không có cách nào khác là dừng lại trên từng node, kiểm tra xem giá trị của trường Ten có chứa ký tự ‘C’ hay không. Trên thực tế đây là một thao tác rất tốn kém về tài nguyên, với các bài toàn như thế này cách làm hiệu quả hơn là dùng fulltext index và fulltext search.

Row Count for all Tables in a Database

CREATE TABLE #counts
(
table_name VARCHAR(255) ,
row_count INT
);

EXEC sp_MSforeachtable @command1 = 'INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?';
SELECT table_name ,
row_count
FROM #counts
ORDER BY table_name ,
row_count DESC;
DROP TABLE #counts;

Create login

USE [master]

GO

CREATE LOGIN [manhnguyenv] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [manhnguyenv]

GO

USE [GRPNashTec]

GO

CREATE USER [manhnguyenv] FOR LOGIN [manhnguyenv]

GO

USE [GRPNashTec]

GO

ALTER ROLE [db_owner] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

CREATE USER [manhnguyenv] FOR LOGIN [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [db_owner] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

CREATE USER [manhnguyenv] FOR LOGIN [manhnguyenv] WITH DEFAULT_SCHEMA=[dbo]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[Bullet] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[Comms] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[SmartFX_Role] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[SQLBackOffice] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER AUTHORIZATION ON SCHEMA::[SQLFrontOffice] TO [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [Bullet] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [Comms_Role] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [db_owner] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [SmartFX_Role] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [SQLBackOffice] ADD MEMBER [manhnguyenv]

GO

USE [HnDev20170516_BAU_DEV]

GO

ALTER ROLE [SQLFrontOffice] ADD MEMBER [manhnguyenv]

GO

Restore Database

Setting a Database to Restricted User Mode

ALTER DATABASE [TestDB] SET RESTRICTED_USER

ALTER DATABASE [TestDB] SET RESTRICTED_USER WITH NO_WAIT

ALTER DATABASE [TestDB] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [TestDB] SET RESTRICTED_USER WITH ROLLBACK AFTER 300 SECONDS

Restore database

After restore database

ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [TestDB] SET MULTI_USER

Show you who is connected at the time

EXEC sp_who2

Database Engine
Ðây là một engine có khả năng chứa data ở các quy mô khác nhau dưới dạng table và support tất cả các kiểu kết nối (data connection) thông dụng của Microsoft như ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC).

Replication
Cơ chế tạo bản sao (Replica): Giả sử bạn có một database dùng để chứa dữ liệu được các ứng dụng thường xuyên cập nhật. Một ngày đẹp trời bạn muốn có một cái database giống y hệt như thế trên một server khác để chạy báo cáo (report database) (cách làm này thường dùng để tránh ảnh hưởng đến performance của server chính). Vấn đề là report server của bạn cũng cần phải được cập nhật thường xuyên để đảm bảo tính chính xác của các báo cáo. Bạn không thể dùng cơ chế back up and restore trong trường hợp này. Thế thì bạn phải làm sao? Lúc đó cơ chế replication của SQL Server sẽ được sử dụng để bảo đảm cho dữ liệu ở 2 database được đồng bộ (synchronized).

Integration Services (DTS)
Integration Services là một tập hợp các công cụ đồ họa và các đối tượng lập trình cho việc di chuyển, sao chép và chuyển đổi dữ liệu.
Nếu bạn làm việc trong một công ty lớn trong đó data được chứa trong nhiều nơi khác nhau và ở các dạng khác nhau cụ thể như chứa trong Oracle, DB2 (của IBM), SQL Server, Microsoft Access….Bạn chắc chắn sẽ có nhu cầu di chuyển data giữa các server này (migrate hay transfer) và không chỉ di chuyển bạn còn muốn định dạng (format) nó trước khi lưu vào database khác, khi đó bạn sẽ thấy DTS giúp bạn giải quyết công việc trên dễ dàng.

Analysis Services
Một dịch vụ phân tích dữ liệu rất hay của Microsoft
Dữ liệu (Data) chứa trong database sẽ chẳng có ý nghĩa gì nhiều nếu như bạn không thể lấy được những thông tin (Information) bổ ích từ đó. Do đó Microsoft cung cấp cho bạn một công cụ rất mạnh giúp cho việc phân tích dữ liệu trở nên dễ dàng và hiệu quả bằng cách dùng khái niệm hình khối nhiều chiều (multi-dimension cubes) và kỹ thuật khai phá dữ liệu (data mining).

Notification Services
Dịch vụ thông báo Notification Services là nền tảng cho sự phát triển và triển khai các ứng dụng tạo và gửi thông báo. Notification Services có thể gửi thông báo theo địch thời đến hàng ngàn người đăng ký sử dụng nhiều loại thiết bị khác nhau.

Reporting Services
Reporting Services bao gồm các thành phần server và client cho việc tạo, quản lý và triển khai các báo cáo. Reporting Services cũng là nền tảng cho việc phát triển và xây dựng các ứng dụng báo cáo.

Full Text Search Service
Dịch vụ SQL Server Full Text Search là một dịch vụ đặc biệt cho việc đánh chỉ mục và truy vấn cho dữ liệu văn bản không cấu trúc được lưu trữ trong các CSDL SQL Server. Đánh chỉ mục với Full Text Search có thể được tạo trên bất kỳ cột nào sử dụng dữ liệu văn bản. Nó sẽ rất hiệu quả cho việc tìm kiếm sử dụng toán tử LIKE trong SQL với trường hợp tìm văn bản.

Service Broker
Được sử dụng bên trong mỗi Instance, là môi trường lập trình cho việc các ứng dụng nhảy qua các Instance. Service Broker giao tiếp qua giao thức TCP/IP và cho phép các component khác nhau có thể được đồng bộ cùng nhau theo hướng trao đổi các message. Service Broker chạy như một phần của bộ máy cơ sở dữ liệu, cung cấp một nền tảng truyền message tin cậy và theo hàng đợi cho các ứng dụng SQL Server.