EugeneChiang.com

Cross Join

The CROSS JOIN joined every row from the first table (T1) with every row from the second table (T2). In other words, the cross join returns a Cartesian product of rows from both tables. In general, if the first table has n rows and the second table has m rows, the cross join will result in n... » read more

Split String Function

Returns a table based off of a delimited string

SQL to Open XML file

SQL to retrieve content of xml file.

Gathering SQL Server Index Statistics and Usage Information

After creating the indexes, we should proactively know which indexes are badly used, or totally unused in order to perform the correct decision to maintain these indexes or replace it with more efficient ones. Recall that removing the unused indexes or badly indexes will improve the performance of the data modification queries, that needs to... » read more

Fine Tuning Queries

Database administrators have to balance between creating too many indexes and too few indexes. For example, there is no need to index every column individually or involve the column in many overlapping indexes. They should also take into consideration that, the index that will enhance the performance of SELECT queries will also slow down the... » read more

SQL Server Non-Clustered Index Design

A Non-clustered index is built using the same 8K-page B-tree structure that is used to build a Clustered index, except that the data and the Non-clustered index are stored separately. A Non-clustered index is different from a Clustered index in that, the underlying table rows will not be stored and sorted based on the Non-clustered... » read more

SQL Server Clustered Index Design

The clustered index can be beneficial for the queries that read large result sets of ordered sequential data. In this case, the SQL Server Engine will locate the row with the first requested value using the clustered index, and continue sequentially to retrieve the rest of rows that are physically adjacent within the index pages with the correct... » read more

SQL Server Index Design Basics and Guidelines

Workload type Before creating an index, you should understand the workload type of the database. On Online Transaction Processing (OLTP) database, workloads are used for transactional systems, in which most of the submitted queries are data modification queries. In contrast, Online Analytical Processing (OLAP) database workloads are used for data warehousing systems, in which most of... » read more

Adding Primary Key to Existing Table

If you want SQL Server to automatically provide values for the new column, make it an identity. Note: this will add a new column at the end of the table. You will need to recreate the table if you want the new PK column to be the first column. There is a way around this... » read more