How to create a table partition in SQL Server? Let’s consider a large table with Sales data. So, this table have a column that stores the year of the sale and the table stores millions of lines. Let’s consider also that some reports reads this data to display yearly totals …
A SQL Server table partition allows to physically split the data of one table on the disk. This data division allows to access directly one unique partition instead of all the data from the table. First, why all large tables should have partitions? When accessing hundreds of million lines, it …
A SQL Server clustered index definition? A SQL Server index is a structure, i.e. a file, stored physically in a disk. Unlike the master data and the log data files that are the main files composing a database. One or more key columns are stored in the index, basically to …
How to disable a SQL Server index? To disable an index for performance reasons, use this code and adjust it to your index and table names. For instance, let’s consider a large sales table, with million lines. Let’s consider that the table has a non-unique and non-clustered index on the …
Maintain the indexes is vital to keep a database up to speed with optimal performance. To rebuild an index use the command to alter and rebuild. Technically rebuilding the index delete it and then create it again. After that, the index as no more fragmentation and the index rows are …
Create a SQL Server index to improve the performance of queries on tables and clustered views. To create an index, use this example and adapt it to the project requirements. Indexes are the first recommended step to performance optimization in SQL Server. How to create a simple SQL Server index …
How to alter an existing stored procedure in a SQL Server database? Simply use the alter command instead of the create command one to modify an existing SQL procedure. In this example let’s add the grouping on months and employees to sum up and group data. Let’s add also a …
How to create a simple SQL Server stored procedure? This example in transact SQL code list all the Customers from the Customers table by using the selection function. This example do not use any where clause to filter the results. Its a simple example to remind the syntax creation for …
How to modify an existing T-SQL view ? To develop a SQL Server database or more generally an application, create a database and tables is mandatory. The views are also very useful because they allow you to adjust a specific number of rows and columns without changing the table structure. …
How to calculate a year-to-date value of the sales in DAX for Power BI? Also called YTD, it represents the total to date of a given measure. For example, to calculate the year-to-date sales total with a DAX formula, use the built-in function called TOTALYTD. In this example, simply copy …
2017 - 2020 - Expert-Only.net - SQL Server queries © All rights reserved.