Welcome to my blog!

Log4net + Dapper + Stored Procedures + Views + Functions + Triggers

June 4, 2019 00:09

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


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

3/ App.config


<?xml version="1.0" encoding="utf-8"?>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
    <add name="MyConnectionString" connectionString="Data Source=MANHNV;Initial Catalog=Northwind;Uid=sa;Pwd=123456;" providerName="System.Data.SqlClient" />
    <add key="webpages:Version" value="" />
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    <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" />
    <appender name="TraceAppender" type="log4net.Appender.TraceAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date{dd-MM-yy HH:mm:ss} %-5level %message%newline" />
    <appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%-5level %message%newline" />
      <level value="All" />
      <appender-ref ref="RollingFileAppender" />
      <appender-ref ref="TraceAppender" />
      <appender-ref ref="ConsoleAppender" />

4/ Program.cs


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;
        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 = @"
        const string sqlView = @"
SELECT SCHEMA_NAME(schema_id) AS schema_name
, name AS view_name
FROM sys.views;
        static void Main(string[] args)
            //6 ways to get the current directory in C#
            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)
            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("Work with the Stored Procedures");
                Logger.Log.Info("Work with the Stored Procedures");
                foreach (var procName in listUniqueProcName)
                        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)
                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("Work with the Functions");
                Logger.Log.Info("Work with the Functions");
                foreach (var functionName in listUniqueFunctionName)
                        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)
                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("Work with the Views");
                Logger.Log.Info("Work with the Views");
                foreach (var viewName in listUniqueViewName)
                        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)
                Console.WriteLine(string.Format("Backup total {0} views successfully!", totalCount));
    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 { getset; }
        public string the__schema { getset; }
        public string procedure__name { getset; }
        public string procedure__text { getset; }
        public int colid { getset; }
    public class FunctionDto
        public string ROUTINE_NAME { getset; }
        public string ROUTINE_TYPE { getset; }
        public string ROUTINE_DEFINITION { getset; }
        public string ROUTINE_SCHEMA { getset; }
        public string DATA_TYPE { getset; }
        public DateTime CREATED { getset; }
    public class ViewDto
        public string schema_name { getset; }
        public string view_name { getset; }
        public bool IsIndexed { getset; }
        public bool IsIndexable { getset; }
    public class Logger
        private static readonly ILog log = LogManager.GetLogger(typeof(Logger));

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

Parallel Programming SQL Server

January 16, 2019 14:27

Parallel Programming SQL Server (edit)

Parallel processing is typically used for

  • Long-running reports
  • Bulk updates of large tables
  • Building or rebuilding indexes on large tables
  • Creating temporary tables for analytical processing
  • Rebuilding a table to improve performance or to purge unwanted rows

The Database Engine can execute different database tasks in parallel. The following tasks can be parallelized:

  •     Bulk load
  •     Backup
  •     Query execution
  •     Indices

The Database Engine allows data to be loaded in parallel using the bcp utility. 




Craig Freedman's SQL Server Blog


Data Warehouse Query Performance in SQL Server 2008


Tối ưu truy vấn SQL Server + Phím tắt trong SQL Server

December 22, 2018 00:21

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) 

Chỉ mục (Index) thật sự là một cách tối ưu hóa CSDL hiệu quả nhưng lại thường bị developer bỏ qua trong quá trình xây dựng ứng dụng.
Index là bảng tra cứu đặc biệt chứa các con trỏ đến dữ liệu thật sự của bảng dữ liệu. Thông qua Index, hệ thống CSDL có thể tăng thời gian truy cập dữ liệu, tương tự như cách các bạn dùng mục lục trong một cuốn sách để có thể tra và tìm được nội dung cần nhanh hơn.
Trong một lần triển khai dự án xây dựng phần mềm quản lý đào tạo của một trường Đại học, chúng tôi đã cải tiến, tăng thời gian thực hiện in phiếu điểm của sinh viên từ 1 phút lên khoảng 2 giây chỉ bằng việc tạo thêm các chỉ mục cần thiết, ngoài chỉ mục mặc định mà hệ thống tạo ra dựa trên khai báo khóa chính của bảng dữ liệu. Bạn sẽ thấy trong các bài tập truy vấn CSDL, chỉ trong tích tắc bạn sẽ có kết quả ngay nên không quan tâm lắm đến điều này. Nhưng đó là trên dữ liệu chỉ chưa đến 100 dòng, còn trong thực tế, bảng dữ liệu với cả triệu mẫu tin thì tốc độ là vấn đề mà người dùng rất quan tâm.
Một số gợi ý sau giúp bạn cân nhắc khi quyết định tạo Index:
- Nếu các truy vấn của bạn có sử dụng mệnh đề JOIN dùng để kết khóa chính và khóa ngoại của 2 bảng. Mặc định hệ thống sẽ tạo chỉ mục trên khóa chính nhưng nếu chưa có, bạn nên tạo index trên cột khóa chính và khóa ngoại có tham gia vào mệnh đề JOIN để tăng tốc độ thực hiện các truy vấn kết JOIN này.
- Nếu các truy vấn WHERE thường xuyên xác định điều kiện lọc dữ liệu trên các cột nào, bạn nên tạo thêm index trên các cột đó. Ví dụ như với bài toán Quản lý sinh viên, bạn có thể tạo index trên cột Mã số sinh viên (09155666, 01657994) vì nhu cầu tra cứu điểm theo mã số SV là rất thường xuyên. Hay như bài toán quản lý đơn hàng thì bạn có thể tạo chỉ mục theo mã khách hàng vì khách hàng thường xuyên có nhu cầu tra cứu, kiểm tra đơn hàng theo mã khách hàng. 
- Bạn cũng có thể cân nhắc tạo thêm index trên những cột hay được gom nhóm, sắp xếp trong các báo cáo, thống kê có tần suất sử dụng cao để tăng tốc độ thực hiện báo cáo.
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  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.
Ngoài ra, để biết được cách SQL Server thực thi câu lệnh SQL như thế nào, chi tiết từng bước một, bạn có thể sử dụng công cụ Execution Plan. Bạn kích hoạt Excution Plan bằng cách nhấn Include Actual Execution Plan (CTRL + M) trong màn hình SQL Server Management Studio trước khi bạn chạy câu lệnh.
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)
SQL_Server_Make selected text as Uppercase_lowercase
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


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

EXEC SP_HELP 'test_procedure'

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:
SET @SQL = 'SELECT TOP 1 * FROM sys.tables'
EXECUTE sp_executesql @SQL
Câu lệnh đúng:
SET @SQL = 'SELECT TOP 1 * FROM sys.tables'
EXECUTE sp_executesql @SQL

Default SA password of SQL Server

December 8, 2018 09:47

Default SA password of SQL Server (edit)

Here are the steps I did to fix this:

  • Login to the SQL Server using a local account which has administrative privileges account (eg: .\Administrator)
  • Once you have logged into Windows, open SQL Management Studio
  • Connect to the SharePoint SQL instance using Windows Authentication
  • Enable Mixed Mode Authentication (this is not enabled by default for the SharePoint SQL instance)
  • Set a password for the sa account
  • Enable the sa account
  • Restart the SQL instance (required due to change in authentication mode)

Enabling Mixed Mode Authentication:

  1. Right click on the SQL server instance
  2. Click on Properties
  3. Click on Security on the left pane
  4. Click on SQL Server and Windows Authentication Mode under the Server authentication section

You can also use the following SQL query to do the same:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

[Note: 2 indicates mixed mode authentication. 1 is for windows only authentication]

Setting a password on the sa account:

  1. Under the SQL instance, expand Security and Logins
  2. Right click on sa and click on Properties
  3. Type the new password in the Password and Confirm Password boxes

You can also use the following SQL query to do the same:


[Note: CHECK_POLICY=OFF ensures that Windows password policies of the computer on which SQL Server is running will NOT be enforced on this login]

Enabling the sa account:

  1. Under the SQL instance, expand Security and Logins
  2. Right click on sa and click on Properties
  3. Click on Status on the left pane
  4. Click on Enabled under the Login section

You can also use the following SQL query to do the same:



How to change a password for the `sa` user in MS SQL?


This can be done using the osql utility in a command prompt or using Microsoft SQL Management Studio.

1. Using the command prompt:

  • For a local MS SQL server use the following command: 

    osql -E -Q "exec sp_password NULL,'new_password','sa'"

  • For a remote MS SQL server use the following command: 

    osql -S servername_or_address -E -Q "exec sp_password NULL,'new_password','sa'"

2. Using Microsoft SQL Management Studio:

  • Open Microsoft SQL Management Studio. It can be done from the command line by executing ssms.exe .
  • Login into Microsoft SQL Management Studio with Administrator user credentials.
  • Go to Security > Logins > sa > properties .
  • Change the password.

Note: These actions are possible for Administrator account only.

If Windows administrator does not have permissions to reset 'sa' user password, follow instructions from the article: Connect to SQL Server When System Administrators Are Locked Out

Sql View Info

July 1, 2018 10:13

Sql View Info (edit)

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

    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

MySql + Sql Server

January 30, 2018 23:06

SQL Hints: http://sqlhints.com

Red-Gate: https://www.red-gate.com/simple-talk/sql/

SQL Blog: http://sqlblog.com/

SQL Server Central: http://www.sqlservercentral.com/

MySQL Tutorial: http://www.mysqltutorial.org/

Recursive sum in tree structure

Finding a string value in a SQL Server table

Concatenating Row Values in Transact-SQL

Exploring Your SQL Server Databases with T-SQL

Questions About CUBE, ROLLUP and GROUPING SETs That You Were Too Shy to Ask

Hierarchy Data SQL Server

Hierarchy SQL Server

What are the options for storing hierarchical data in a relational database?

Focusing on the SQL-standard, hierarchical and temporal data, and open source software


Modeling an Adjacency List Hierarchy with SQL Server 2017 Graph Database

The Performance of Traversing a SQL Hierarchy

Hierarchies on Steroids

Managing Hierarchical Data in MySQL

  • Joe Celko's Trees and Hierarchies in SQL for Smarties, Second Edition (The Morgan Kaufmann Series in Data Management Systems) 2nd Edition
  • Murach's MySQL, 2nd Edition 2nd Edition

Managing Hierarchical Data in MySQL Using the Adjacency List Model

How to create a MySQL hierarchical recursive query

How to do the Recursive SELECT query in MySQL?

Recursive self joins

Hierarchical queries in MySQL


HierarchyID Aggregate Functions in T-SQL

SQL Server

August 2, 2017 13:15

Microsoft SQL Server (edit)

Data Type


Execution Plan


SQL Profiler Duration unit


enter image description here

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/


July 25, 2017 15:11

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


SQL Cheat Sheet: Retrieving Column Description in 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 Numbers(Number) AS (
  SELECT Number + 1
  FROM Numbers
  WHERE Number <= 999999

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


), 1, 1, '') ConcatColumnNames


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

PRINT @str;


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 Customers

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

Bảng heights

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

Bảng staff

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 customers staff:

Bảng people


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  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;

Câu lệnh SELECT

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.


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;

Kết quả trả về của câu lệnh WHERE

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';

WHERE kết hợp với OR

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.


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;

Kết quả của lệnh ORDER

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 đó.


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);

Kết quả trả về sau lệnh JOIN

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.


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;

Kết quả trả về khi thực hiện lệnh ALIAS

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


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
SELECT age, name FROM staff;

Kết quả trả về khi thực hiện lệnh UNION

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
SELECT age, name FROM staff;

Kết quả trả về khi thực hiện lệnh UNION ALL

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ỉ!


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.


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.


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)

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".


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:


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à.


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ó:

  name TEXT,
  age, INTEGER,

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.


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.


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

'TimeZoneKeyName',@TimeZone OUT

SELECT @TimeZone

--SE Asia Standard Time

Handle conversion between time zones in SQL Server





Recent posts