SQL Server 2016 Stretch Database
One of the features in SQL Server 2016 that you will want to explore is the ability to store your historical data in the Azure cloud. By leveraging the Stretch Database feature your applications can silently migrate data to the cloud without having to change your...Read More »
What is SQL Server Polybase?
Coming in SQL Server 2016 is the ability to query relational tables as well as data stored in Hadoop or Azure Blob storage. This technology will allow you to continue to leverage SQL Server for your data warehousing and access data stored in different formats and...Read More »
Leveraging SQL Server Database Schema
SQL Server 2014 Deprecated Features – Database Engine
Everyone looks at the new features when trying to determine when to upgrade from an older version of SQL Server to a newer release. There are many factors to consider and for every environment certain features have a higher priority and need for...Read More »
T-SQL EXCEPT and INTERSECT
Both T-SQL EXCEPT and INTERSECT are set based operators that combine multiple query results back in the same result set. EXCEPT returns the records from the query on the left that are not found in the right query. INTERSECT returns the distinct rows that are in both the...Read More »
SSRS Farm Overview
Building a SSRS farm will require Enterprise or Business Intelligence editions of SQL Server for versions 2012 and 2014. SQL Server allows for the deployment of 2 or more servers running SQL Server Reporting Services to increase the performance of your reporting environment.
SSRS stores its data in...Read More »
T-SQL Random Numbers using RAND()
SQL Server includes the T-SQL RAND() function to create a random value between 0 and 1 of float datatype. To create a random number execute Select Rand(), in my example it returned .0131039082850364. If I wanted to always return the same number I can include a...Read More »
Row_Number SQL Server
SQL Server includes several Ranking Functions that can be called in T-SQL. One of these is the Row_Number() function. You can use this function to return a sequential number in your result set that begins at 1.
There are 2 arguments that can be passed into the function that...Read More »
SQL Server Management Studio Reports
Whether you are an Application, SQL, BI Developer or a DBA. Knowing what is occurring within your database is critical to the performance of your applications. One tool you can leverage to see what is happening are the built in reports in SSMS. SQL Server includes...Read More »
Why Use a SQL Server Table Partition
Using TABLESAMPLE in SQL Server
SQL Server has several ways for you to limit the number of records returned from a query. For example, the AdventureWorks [sales].[salesorderdetail] table has 121,317 rows loaded in sequential order starting with SalesOrderDetailID 1. By Using a top 1000 clause I can limit the results to...Read More »
Use SQL Server Checksum in Data Warehousing
When building data warehouses you...Read More »
Common Table Expresseion (CTE)
A common table expression (CTE) is a derived table that is defined and only stored for the duration of the query. CTE’s can be used for recursive queries, creating a view on demand and referencing the same table multiple times in the same query. CTE’s make your...Read More »
SQL Server 2014 Cardinality Estimator
Included in the new...Read More »
SQL Server Data Encryption Types
Businesses today have a greater need than ever to protect their data from security breaches. Data stored in databases are a prime target for hackers since it contains data that is normalized and contained in a central location. Once a hacker accesses a database all of...Read More »
What is (SSAS) SQL Server Analysis Services Tabular Mode?
SQL Server 2012 ships Analysis Services that can be installed in the traditional OLAP engine or in the Tabular engine mode. Tabular mode is an in-memory database that compresses and queries the data with great performance. In most cases, you may find...Read More »
The Path to the PDW
For decades we had the traditional electric oven to cook with and people thought it was great. This technology allowed for consistent and dependable cooking for its users. Then something magical happened, the microwave was invented and used for food preparation. By leveraging the a different...Read More »
SQL Server Analysis Services (SSAS) Fundamentals
SSAS comes with your purchase of Microsoft’s SQL Server. It is a separate component and can be installed in two different modes, Multidimensional or Tabular. Multidimensional is the traditional method of building and OLAP cube. Tabular mode using in memory techniques to aggregate relational tables...Read More »
SQL Server Data Compression Overview
I have been reviewing the options for data compression in SQL Server and believe it can be a great value to Reporting and Business Intelligence databases. By their nature BI databases typically store more data than any other database in your environment. Even with the cost...Read More »
Filtered Index – Database Performance
SQL Server 2012 includes filtered indexes and is a great feature for Business Intelligence and Reporting databases. Filtered indexes allow you to reduce the overhead of having a index on a tables and increase the performance of the query optimizer by already reducing the number of...Read More »
SQL Server Sparse Column
Often stored in the columns of a reporting database are troublesome columns that are populated with limited amounts of data. One method to optimize these types of columns in your reporting database is to utilize the SPARSE keyword in a CREATE or ALTER table statement. For instance...Read More »
SQL Server Index Performance Tips for Business Intelligence
If you are new to Reporting Databases you need to be aware that they indexing needs are different from OLTP database operations. While you will still have clustered and nonclustered indexes how you utilize them will be different with Reporting systems.
Having a lot...Read More »
Each table can have only 1 columnstore index, data from the index is stored in memory in the xVelocity engine. Once a columnstore index has been created on a table you cannot update the table. Microsoft recommends you take the following actions to update the data in a table containing...Read More »
Data Mining SQL Server 2012
Included in your purchase of Microsoft’s SQL Server 2012 is the ability to perform data mining techniques. This feature comes as part of Analysis Services via the Data Mining model.
There are 9 total algorithms included broken into 5 types within SQL Server. The types of Data...Read More »
If you have a large reporting data and have not explored the new Columnstore Indexes. Take some time to review what you are missing. Unlike regular database indexes (rowstore format) that index tables based on the data tied to a row. Columnstore indexes compress the data within a column to...Read More »
What is PowerView and why is it important to IT and business users?
PowerView is an Excel 2013 feature that enables users to build integrated charts and graphs within Excel. Given a completed mashup of data, a user can then create a wide selection of reports in the familiar Excel interface....Read More »
SQL Server Backup to URL
A new feature to SQL Server 2012 (requires SP1 CU2) is the backup to URL functionality. This feature allows you the option to backup your SQL Server database directly to the Windows Azure service. This adds value to an infrastructure by decreasing on-site hosted storage, increasing...Read More »