April 11, 2021

SQL Server Query and Example


DAX functions

Calculate last month value in DAX with Power BI

How to calculate the last month value in DAX for Power BI? Managers, Financial advisors and Business consultants use month comparisons extensively. To create these comparison measures, use the DAX time intelligence functions available in the DAX editor. Let’s use the PREVIOUS MONTH DAX function to address this. How to

Time economy is key when you create a SQL Server partition.
SQL Server Partition

Create a SQL Server table partition

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

blank
SQL Server Partition

What is a SQL Server table partition?

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

blank
SQL Server Index

SQL Server clustered index definition

What is a SQL Server clustered index ? The definition of a SQL Server index is a structure, i.e. a file, stored physically in a disk. It’s similar to the master data file and the log data file that composes a database. Basically, to find the associated lines or rows

blank
SQL Server Downloads

Download SQL Server Management Studio 18 (SSMS)

How to Download SQL Server Management Studio 18 (SSMS), the latest version? To connect to a SQL Server database in order to query tables or views. Or to develop a full database project, SSMS is the most used software, and it’s a free tool! How to get and install SQL

blank
SQL Server Downloads

Download SQL Server 2019 developer edition for free

Where to download the SQL Server 2019 developer edition? It’s the free edition and contains the full set of available features. It also provides a real option for evaluation for developments, tests and proof of concepts. Nevertheless, it is not for use in a production environment. How to download the

blank
SQL Server Index

How to create a SQL Server index?

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

blank
SQL Server Index

How to list all SQL Server indexes?

How to list all SQL Server indexes? It can be very useful to display all database indexes. Indeed, to have an idea at a glance at how this SQL Server database is optimized with indexes, whether an index is A clustered or a non-clustered one. This query offers you a

blank
SQL Server Index

How to rebuild a SQL Server index?

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 speaking, the query rebuilding the index delete it and then creates it again. This operation reorders the index rows in adjacent pages and

blank
SQL Server Index

How to reorganize a SQL Server index?

How to reorganize a SQL Server index? This operation defragments the lower level of the indexes by reordering the pages physically, from left to right. The reorganize operation also compacts the pages of the index. To better understand this, imagine a book of recipes with the pages not ordered anymore.

blank
SQL Server Index

How to disable a SQL Server index?

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

blank
SQL Server Stored Procedures

How to create a SQL Server stored procedure ?

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

blank
SQL Server Stored Procedures

How to alter a SQL Server stored procedure?

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

blank
SQL Server Index

How to drop a SQL Server index?

This article is on how to drop a SQL Server index from an existing table on a given database. It can be a relational or a transactional database. For instance, learn here why you need an index optimization plan on databasejournal. How to drop a SQL Server index? To drop

blank
SQL Server Abbreviations

XMLA stands for XML for Analysis

XMLA stands for XML for Analysis stands for Extensible Mark-up Language for Analysis. It’s a language built on top of XML language. It allows data access from standard multidimensional data sources. Client applications accesses Microsoft cubes and tabular models using the XMLA language and the HTTP protocol. XMLA stands for

blank
DAX functions

Calculate year-to-date value in DAX with Power BI

How to calculate a year-to-date value of the sales in DAX with 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. From this example, easily copy

blank
SQL Server Views

How to create a SQL Server view?

How to create a SQL Server view to display specific columns from an existing table? Consider a view as a virtual table defined by a SQL query. It’s a set of columns and rows from a table. A user defined view is a object managed by a SQL Server developer.

blank
SQL Server Views

How to delete a SQL Server view?

How to delete a SQL Server view from the database? How to drop a view? For a SQL developer it’s a good practice to have a clean database and remove the unused or useless code. Unlike the tables, the views can be deleted from the database using a simple T-SQL

blank
SQL Server Views

How to modify a SQL Server view?

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.

blank
SQL Server Errors

Truncate Table Error Cannot find the object

How to avoid the SQL Server Truncate Table Error Cannot find the object? In SQL Server, to clear the full content of a table, the fastest way is to run a truncate Table query. The Truncate is different from the Delete From query because it empties all the lines in

blank
SSAS Tabular Errors

Error occurred while opening tabular model

This visual studio error occurs when opening the model on the workspace. Then Visual Studio crashes because the network connection was lost. The exact error message is something like : An error occurred while opening the model on the workspace database. For example, the local tabular model development environment lose

blank
DAX functions

Calculate last year value in DAX for Power BI

With Power BI, how to calculate the last year value in DAX? Simply use the two available DAX CALCULATE function combined with the PREVIOUS YEAR function. In financial dashboard and reports, use the previous year value for comparison and variance calculation with the current year.  Indeed analyzing financial figures is often about time comparison and rolling forecasts. Financial forecast uses rolling months, quarters or years. How to calculate last year