Những câu lệnh T-SQL Index cần thiết, lệnh sql căn bản, cơ bản

Thảo luận trong 'Kiến thức lập trình SQL & MySQL' bắt đầu bởi huongkiss, 20/2/14.

  1. huongkiss

    huongkiss

    Vip Member

    Tham gia ngày:
    12/2/14
    Bài viết:
    91
    Đã được thích:
    0
    Điểm thành tích:
    6
    1. Clustered Index:

    Có nhiệm vụ chính là lưu trữ dữ liệu của các dòng được sắp xếp theo thứ tự trong bảng dựa trên giá trị của khóa key. Chỉ 1 clustered index có thể được tạo trên mỗi bảng, bởi vì dữ liệu của các dòng chỉ có thể sắp xếp theo 1 thứ tự nhất định. Thêm 1 điểm nữa là clustered index có thể được “sản sinh” trong khi tạo những ràng buộc giống như Primary key trên bảng dữ liệu có sẵn.

    Ví dụ như:

    ALTER TABLE [MyAddress]
    ADD CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED
    (
    [AddressID] ASC​
    ) ON [PRIMARY]
    GO

    Bên cạnh đó, clustered index cũng có thể được tạo trên mỗi cột mà không có liên kết đi kèm. Ví dụ:

    CREATE CLUSTERED INDEX [MyAddress_id_CIX] ON [MyAddress1]
    (
    [ID] ASC​
    )ON [PRIMARY]
    GO

    2. Non Clustered Index:

    Được tạo ra để cải thiện hoạt động, hiệu suất của những chuỗi câu lệnh truy vấn thường xuyên được sử dụng, nhưng lại không bao gồm với clustered index. Bên trong khối nonclustered index, việc sắp xếp thông tin theo thứ tự của chỉ số index không khớp với thứ tự lưu trữ xét theo khía cạnh vật lý của các dòng dữ liệu trên ổ đĩa.

    Nonclustered Index có thể được tạo trên bảng có sẵn, bao gồm các cột không có trong clustered index. Ví dụ:

    CREATE UNIQUE NONCLUSTERED INDEX
    [NIX_col5_col2_col3_col4_col6]
    ON [MyAddress]
    (
    [AddressLine1] ASC,
    [AddressLine2] ASC,
    [City] ASC,
    [StateProvinceID] ASC,
    [PostalCode] ASC​
    )ON [PRIMARY]
    GO

    Hoặc, nonclustered index cũng có thể làm được trong khi tạo liên kết trong bảng có sẵn, ví dụ:

    ALTER TABLE [MyAddressType]
    ADD CONSTRAINT [DEFF_MyAddressType_ModifiedDate]
    DEFAULT (getdate()) FOR [ModifiedDate]​
    GO

    3. XML Index:

    Là 1 khái niệm khác, được sinh ra trên cột dữ liệu XML và các bảng có clustered index trên khóa Primary. 1 XML index thộc dạng Frimary:

    CREATE PRIMARY XML INDEX idx_xCol_MyTable on MyTable (xCol)

    Và với XML index secondary thì như sau:

    CREATE TABLE MyTable (Col1 INT PRIMARY KEY, XmlCol XML)
    GO
    -- Create primary index.
    CREATE PRIMARY XML INDEX PIdx_MyTable_XmlCol
    ON T(XmlCol)​
    GO
    -- Create secondary indexes (PATH, VALUE, PROPERTY).
    CREATE XML INDEX PIdx_MyTable_XmlCol_PATH ON MyTable(XmlCol)
    USING XML INDEX PIdx_MyTable_XmlCol
    FOR PATH​
    GO
    CREATE XML INDEX PIdx_MyTable_XmlCol_VALUE ON T(XmlCol)
    USING XML INDEX PIdx_MyTable_XmlCol
    FOR VALUE​
    GO

    4. Spatial Index:

    1 thành phần trong SQL Server 2008 cung cấp cho người dùng những cột dữ liệu đặc biệt, có liên quan đến dữ liệu biểu trưng có liên quan đến lĩnh vực không gian, ví dụ như địa lý và hình học.
    1 cấu trúc spatial index có thể được tạo bằng cú pháp sau:

    CREATE TABLE MySpatialTable(id int primary key, geometry_col geometry);
    CREATE SPATIAL INDEX SIndx_MySpatialTable_geometry_col1
    ON MySpatialTable(geometry_col)
    WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );

    Query Index có liên quan đến dữ liệu metadata:

    5. Tìm kiếm tất cả index:
    Trước tiên, nếu muốn tìm kiếm tất cả index thì hãy sử dụng câu lệnh truy vấn – query theo bảng, cột và khóa index của cơ sở dữ liệu sẵn có.

    SELECT OBJECT_SCHEMA_NAME(BaseT.[object_id],DB_ID()) AS [Schema],
    BaseT.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],I.[type_desc]
    FROM sys.[tables] AS BaseT
    INNER JOIN sys.[indexes] I ON BaseT.[object_id] = I.[object_id]
    INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
    INNER JOIN sys.[all_columns] AC ON BaseT.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
    WHERE BaseT.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'
    ORDER BY BaseT.[name], I.[index_id], IC.[key_ordinal]

    6. Fragmentation:
    Còn với quá trình Fragmentation – tìm kiếm những thành phần index trong tình trạng “Fragmentation” của tất cả các bảng dữ liệu trong database hiện tại. Ví dụ như sau:
    SELECT object_name(IPS.object_id) AS [TableName],
    SI.name AS [IndexName],
    IPS.Index_type_desc,
    IPS.avg_fragmentation_in_percent,
    IPS.avg_fragment_size_in_pages,
    IPS.avg_page_space_used_in_percent,
    IPS.record_count,
    IPS.ghost_record_count,
    IPS.fragment_count,
    IPS.avg_fragment_size_in_pages
    FROM sys.dm_db_index_physical_stats(db_id(DB_NAME()), NULL, NULL, NULL , 'DETAILED') IPS
    JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
    JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
    WHERE ST.is_ms_shipped = 0
    order by IPS.avg_fragment_size_in_pages desc

    7. Missing Index:

    Với các thành phần index bị mất, SQL Server vẫn có khả năng giám sát, theo dõi tình hình của index được tạo ra nhằm mục đích cải thiện hiệu suất hoạt động của chuỗi câu lệnh truy vấn. Phần mã dưới đây có chức năng liệt kê tất cả các mục index bị mất:

    SELECT sys.objects.name
    , (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
    , 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ('+IsNull(mid.equality_columns, '')
    + CASE WHEN mid.inequality_columns IS NULL THEN ''
    ELSE CASE WHEN mid.equality_columns IS NULL THEN ''
    ELSE ',' END + mid.inequality_columns END + ' ) ' ​
    + CASE WHEN mid.included_columns IS NULL THEN ''
    ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement​
    , mid.equality_columns
    , mid.inequality_columns
    , mid.included_columns
    FROM sys.dm_db_missing_index_group_stats AS migs
    INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
    INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
    WHERE (migs.group_handle IN
    (SELECT TOP (500) group_handle
    FROM sys.dm_db_missing_index_group_stats WITH (nolock)
    ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
    AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
    ORDER BY 2 DESC , 3 DESC

    8. Index không còn sử dụng:
    Thành phần cuối cùng chúng ta đề cập đến trong mục này là index không còn sử dụng, các bạn hãy áp dụng chuỗi câu lệnh dưới đây để liệt kê tất cả các phần index chưa từng được sử dụng, bên cạnh đó còn tạo ra lệnh DROP:
    SELECT o.name, indexname=i.name, i.index_id
    , reads=user_seeks + user_scans + user_lookups
    , writes = user_updates
    , rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
    , CASE WHEN s.user_updates < 1 THEN 100
    ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates​
    END AS reads_per_write​
    , 'DROP INDEX ' + QUOTENAME(i.name)
    + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
    FROM sys.dm_db_index_usage_stats s
    INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
    INNER JOIN sys.objects o on s.object_id = o.object_id
    INNER JOIN sys.schemas c on o.schema_id = c.schema_id
    WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND i.type_desc = 'nonclustered'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
    ORDER BY reads

    Index Maintainenance:

    9. Tái cấu trúc Index:

    Để xây dựng, tái tạo lại các mục index sau khi thực hiện quá trình defragmentation, hoặc khi muốn tạo cấu trúc bảng dữ liệu. Câu lệnh sau tương tự như DBCC DBREINDEX trong các phiên bản của SQL Server từ 2005:
    USE AdventureWorks2008R2;
    GO
    ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee​
    REBUILD;​
    GO
    10. REORGANIZE:

    Cú pháp REORGANIZE được áp dụng đối với các index ở các mức leaf khác nhau, cụ thể các câu lệnh REORGANIZE này luôn được thực hiện online, còn về mặt kỹ thuật, cú pháp này tương tự với DBCC INDEXDEFRAG trong các phiên bản SQL Server sau 2005.
    USE AdventureWorks2008R2;
    GO
    ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto​
    REORGANIZE ;​
    GO
    Chúc các bạn thành công có thêm kiến thức tốt.
     
    Cảm ơn đã xem bài:

    Những câu lệnh T-SQL Index cần thiết, lệnh sql căn bản, cơ bản

    Đang tải...
  2. admin

    admin Phạm Công Sơn Thành viên BQT

    Tham gia ngày:
    22/5/13
    Bài viết:
    3,746
    Đã được thích:
    716
    Điểm thành tích:
    113
    Giới tính:
    Nam
    Bài viết hay. Có mấy câu lệnh đọc xong bài này mới biết, để test thử xem sao
     
  3. xuandan22

    xuandan22 Thành Viên Mới

    Tham gia ngày:
    22/2/14
    Bài viết:
    49
    Đã được thích:
    0
    Điểm thành tích:
    0
    Bài viết hay. Có mấy câu lệnh đọc xong bài này mới biết, để test thử xem sao
     
  4. huongkiss

    huongkiss

    Vip Member

    Tham gia ngày:
    12/2/14
    Bài viết:
    91
    Đã được thích:
    0
    Điểm thành tích:
    6
    gì mà 2 người comment giống nhau vậy ta
     
  5. admin

    admin Phạm Công Sơn Thành viên BQT

    Tham gia ngày:
    22/5/13
    Bài viết:
    3,746
    Đã được thích:
    716
    Điểm thành tích:
    113
    Giới tính:
    Nam
    Cái này gọi là ăn cắp nội dung trắng trợn đó :\
     
  6. huongkiss

    huongkiss

    Vip Member

    Tham gia ngày:
    12/2/14
    Bài viết:
    91
    Đã được thích:
    0
    Điểm thành tích:
    6
    =)) add đăng ký bản quyền đi :)
     
  7. admin

    admin Phạm Công Sơn Thành viên BQT

    Tham gia ngày:
    22/5/13
    Bài viết:
    3,746
    Đã được thích:
    716
    Điểm thành tích:
    113
    Giới tính:
    Nam
    hihi lần sau cm đóng thêm dấu mộc nữa mới dc heheh
     
  8. dangtinorg

    dangtinorg Thành Viên Mới

    Tham gia ngày:
    30/9/14
    Bài viết:
    7
    Đã được thích:
    0
    Điểm thành tích:
    0
    Có nhiều câu cao siêu quá, dùng SQL càng đơn giản thì càng tốt cho database đó bạn.
     
  9. khamlosq1x9

    khamlosq1x9 Thành Viên Mới

    Tham gia ngày:
    19/9/15
    Bài viết:
    42
    Đã được thích:
    2
    Điểm thành tích:
    8
    Giới tính:
    Nữ
    mình mới làm seo nhìn mấy cái này chẳng hiểu gì cả mong các bạn chỉ mình nhé
     
  10. admin

    admin Phạm Công Sơn Thành viên BQT

    Tham gia ngày:
    22/5/13
    Bài viết:
    3,746
    Đã được thích:
    716
    Điểm thành tích:
    113
    Giới tính:
    Nam
    :) bạn không hiểu chỗ nào còn biết chỉ, không hiểu hết thì sao mà chỉ nôi !!!
     
  11. Hoango95

    Hoango95 Thành Viên Mới

    Tham gia ngày:
    7/12/15
    Bài viết:
    7
    Đã được thích:
    0
    Điểm thành tích:
    1
    Giới tính:
    Nữ
    có nhiều câu lệnh mới, mình đang học môn này may quá có thể áp dụng vào bài tập
     
  12. pingmaxno1

    pingmaxno1

    Vip Member

    Tham gia ngày:
    17/9/15
    Bài viết:
    165
    Đã được thích:
    5
    Điểm thành tích:
    18
    Giới tính:
    Nam
    admin có tài liệu học sql căn bản nào hay hay share cho mình vs, lên mạng nhiều cái tạp nham nên chẳng bit đằng nào mà xem
     
  13. n_erudite

    n_erudite Thành Viên Mới

    Tham gia ngày:
    4/1/16
    Bài viết:
    9
    Đã được thích:
    0
    Điểm thành tích:
    1
    Giới tính:
    Nữ
    thấy mõi mục 1 và 2 là biết, mấy cái sau mới ghê,
     


Like và Share ủng hộ ITSEOVN