This is the third post in the series about database development and testing using SQL Server Data Tools and Team Foundation Server. Post 1: Continuous Integration with SSDT and TFS Post 2: Unit testing with SQL Server Data Tools You can run test in Visual Studio to test the quality of your build. In VS […]
Introduction MS SQL Server 2005 and later versions include the Database Snapshot feature to have snapshot of the database for reports, as a copy in different periods. The Database Snapshot can be created multiple times and it can only be created using the T-SQL. In this article, we will show how to create a Database […]
In the first part we started discussion about choosing the right table structure and described the heaps — in my opinion a mostly overused option. Now let’s see better alternatives.
Now that Karla’s restore process is mature, it is time to pre-stage the means of reporting on these events. Two major actions have been taken for each database tested. First a restore operation occurred. The restore operation validates that the backup file is well formed and that there is nothing wrong with the data which […]
Introduction Among many different things that can affect SQL Server performance, some are more significant than others. In addition, some changes can be relatively easy to implement, but others are quite painfully:
Intro We use cryptography every day: on the internet, mobile devices, ATM machines, and in almost every aspects of our digital life. In a nutshell, cryptography is about data scrambling and hiding, depending on the implementation and user-specific needs.
Warning: This article contains examples of SQL injection. The example queries can be harmful to data and are run on the AdventureWorks2012 database DO NOT run equivalents on your production database. Backup your AdventureWorks2012 database if you don’t want to lose data. Please note that the techniques are purely for education purposes and we do […]
Introduction Most people that work with Microsoft SQL Server will have at least heard talk of cursors and often, even if people know on a basic level what SQL Server cursors do, they are not always certain when to use them and how to write the code behind them. So this article takes a step […]
Prerequisite The discussion pertaining to SQL Server 2012 columnstore indexing is better explained through theoretical and practical measures. Thus, for the practical measure part – I will be using the AdventureWorksDW2012 sample database. The rest of the prerequisites for a comprehensive understanding of this topic are as follows:
A SQL Server backup is one of the most important features DBAs should fully understand and use. It’s not uncommon that the process of backing up SQL Server data is considered as simple and therefore easily overlooked as essential when it comes to disaster or other recovery scenarios. Realizing that a backup is not valid […]
Introduction In order to build a SQL Server business intelligence solution one needs to: Design a de-normalized data warehouse Build and schedule an Extract, Transform and Load (ETL) package that will feed the data warehouse at regular intervals with new data from the OLTP database. Setup, personalize and process a cube based on the data […]
What is a SQL Server transaction log file? A SQL Server transaction log file is an integral part of every SQL Server database. Along with the data file, which has an MDF extension, the transaction log file with a LDF extension, makes a SQL Server database.
Microsoft SQL Server distinguishes between paging and pagination. Paging refers to handling of memory bottlenecks whereas pagination, the focus of this article, refers to dividing T-SQL query result set into discrete parts. The general concept of Pagination is prevalent in various professional disciplines (i.e. journalism, publishing, web development etc.). According to Wikipedia Pagination is the […]
SQL Server Policy Based Management provides means to declare certain states and properties for SQL Server instances and their objects in a form of policies. These policies can be evaluated against a set of SQL Server instances, or against a specific SQL Server instance and a set of its objects that can be narrowed even […]
In SQL Server Policy Based Management, best practices represent guidelines in a form of Policy Based Management policies that are in common SQL Server use scenarios considered as the best way to configure SQL Server instances and their objects. For example, it is considered a best practice for most SQL Server environments to use and […]
The Policy Based Management feature we described in the series of articles (see below) provides an efficient method to declare and enforce policies related to SQL Server instances and their objects. The main purpose of the feature is to facilitate maintenance of a single and, what’s more important, multiple SQL Server instances across the enterprise. […]
Introduction to Sequences Sequence is a new object that was introduced in SQL Server 2012 for a key-generating mechanism. It has been made available across all editions of SQL Server 2012. It was added as an alternative to IDENTITY columns feature that has been prevalent in the previous versions of SQL Server. Despite being newly […]
What a load of Bl… You may be wondering what Business Intelligence is. If you’re reading this article you probably have prior experience with Microsoft SQL Server or at least one other relational database management system (RDBMS). If this is the case you’re probably used to managing a lot of valuable data. If you’re a […]
A typical N-tier application design contains of the several layers from the client (Web or Windows), to the Business Logic Layer, to the Data Access Layer, and finally to the Data Storage Layer.
What is a database table partitioning? Partitioning is the database process where very large tables are divided into multiple smaller parts. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan. The main of goal of […]
In the previous part of this article, we explained how indexes affect SQL Server query execution performance and plans, and provided some basic examples. In this part, we will continue with examples that show how indexes affect query execution plans and costs.
SQL Server query execution plans are very useful in query performance analysis and troubleshooting. There are two types of query execution plans in SQL Server: actual and estimated. They show how a query was executed and how it will be executed
In the previous parts of this series, we explained what SQL Server query execution plans were, why they should be used, and presented various methods to show them. In this part, we will explain the symbols used, how to read the plans, and how to use these plans in performance analysis and troubleshooting
A primary key is a column or a combination of columns that uniquely defines each row in a table. When designing a database choosing a primary key is one of the most important steps. A table can contain one or more columns suitable to be a primary key and that column or columns are called […]
The Policy Based Management feature was introduced in SQL Server 2008. The purpose of the feature is to assists SQL Server administrators in creating and enforcing policies tied to SQL Server instances and their objects. The policies can be configured on one SQL Server and re-used on other SQL Server instances to provide a SQL […]
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy