Concept and basics of Temporal tables in SQL Server 2016 November 24, 2016 by Mustafa EL-Masry In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including: Read more »
Understanding the SQL Server Proportional fill algorithm November 22, 2016 by Ahmad Yaseen When creating a database, SQL Server maps this database with minimum two operating system files; the database data file MDF and the database log file LDF. Logically, the database data files are created under a collection set of files that simplifies the database administration, this logical file container is called the Filegroup. Database data files can come in two types, the Primary data files that contains metadata information for the database and pointers to the other database files in addition to the data, where each database should have only one Primary data file and the optional Secondary files to store data only. The basic storage unit in SQL Server is the Page, with each page size equal to 8KB. And each 8 pages with 64KB size called Extent. Read more »
3 ways to improve T-SQL performance November 22, 2016 by Daniel Calbimonte Introduction When customers used to ask for advice to solve some T-SQL Problem, they would show me their scripts, stored procedures and cursors. I saw horrible things doing that job, some things I do not want to remember, and even some I cannot tell you about 😉 In this article, we will talk about performance problems when using while loops in T-SQL. We will also talk about problems with UNIONsand finally the use of JOINS of two or more tables using the where clause (ANSI 89) instead of using INNER, LEFT or RIGHT JOINS using the from clause (ANSI 92). Read more »
PowerShell: Get a daily database status email November 22, 2016 by Aamir Syed The need for this script came about when I took a new job as a DBA. One of my responsibilities was to make sure all databases were available after the maintenance window which ran during the weekend. Rather than log into each database server and check that the databases were online, I had a script do the work for me and shoot me an email. Read more »
SQL Server Partitioned Views November 18, 2016 by Ahmad Yaseen SQL Server table partitioning is a great feature that can be used to split large tables into multiple smaller tables, transparently. It allows you to store your data in many filegroups and keep the database files in different disk drives, with the ability to move the data in and out the partitioned tables easily. A common example for tables partitioning is archiving old data to slow disk drives and use the fast ones to store the frequently accessed data. Table partitioning improves query performance by excluding the partitions that are not needed in the result set. But table partitioning is available only in the Enterprise SQL Server Edition, which is not easy to upgrade to for most of small and medium companies due to its expensive license cost. Read more »
How to create and configure Microsoft Azure HDInsight November 18, 2016 by Daniel Calbimonte Introduction In our chapter about PolyBase, we presented this new SQL Server 2016 feature to query CSV files stored in Azure Storage accounts. We mentioned that in PolyBase you can query data in Hadoop (HDInsight) using SQL Server. HDInsight is a very popular system in Azure that eventually you will need to interact with if you use SQL Server. That is why we will give an explanation for newbies about it. Read more »
What’s new in SQL Server 2014 Cardinality Estimator? November 14, 2016 by Ahmad Yaseen The Cardinality Estimator is a SQL Server Query Processor component that is responsible for predicting the number of rows that the query will return. This estimation of the number of rows in addition to the SQL Server statistics will be used by the SQL Server Query Optimizer to create the optimal and the most accurate execution plan for your query that has the lowest processing cost to execute. Read more »
Creating reports based on existing stored procedures with SQL Server Reporting Services November 10, 2016 by Nesha Maric Basic extraction of the SQL Server database data is usually achieved by querying the databases and creating stored procedure to automate the extraction process. Unfortunately, extracting the information in this way will not yield high-end reports, and only basic table-shaped reports are available when extracting the information via SQL Server Management Studio or similar tools. In order to create high-end reports that will include additional projections of the data, such are graphs, lists, charts… SQL Server offers powerful reporting options within the SQL Server Reporting service. Read more »
How to use SQL Server Data Quality Services to ensure the correct aggregation of data November 9, 2016 by Steve Simon Introduction An interesting opportunity arose at a client site during early October which provided a phenomenal opportunity to do a Data Quality Services implementation. My client (a grocer) had been requested to produce summary reports detailing the amount of funds spent during 2016 (YTD) with the myriad of manufacturers from whom the chain purchases their inventory. All “accounts payable” entries are done manually and as such are prone to errors. Read more »
Two methods for restoring a data warehouse/data mart environment November 8, 2016 by Sifiso W. Ndlovu Implementing best data warehouse designs and practices such as data lineage reduces the need to ever have to restore an entire relational data warehouse. However, sometimes there are instances whereby you have inherited poorly designed data warehouse environments that leaves you with no other options but to perform an entire database restore in an event of a sudden disaster. I recently found myself in a similar situation of having to recover one of my data mart following a data integrity issue wherein all data of a type 1 dimension was updated/overwritten using an incorrect source file. In this article I take a look at how different approaches can be utilised to restore the compromised SQL Server-based data mart back to its “good state”. Read more »
Have your database server talk to you: Send emails directly from SQL Agent Jobs November 7, 2016 by Aamir Syed Recently, I was asked if I can write and run a specific query and have the results, (if any) emailed to a specific group of people every day? But only send the email if the query returns results. Who wants to open and empty email after all? As with any DBA, we need a way to automate this process, as our time is valuable. Who wants to manually run a script, paste it into excel and then email it directly to users every morning? Read more »
How to create an image cataloging system using SQL Server Reporting Services November 2, 2016 by Steve Simon Introduction With Christmas just around the corner, in today’s “get together” I thought that we would have some fun by cataloging a collection of ‘your favorite items’. Whether it be a coin collection, a china plate collection or a stamp collection, the process is the same and certainly something that you will enjoy creating and maintaining. For today’s example, we are going to construct a “Postage Stamp” cataloging system. We are going to see how we are able to get from this… Read more »
How to check if a date is a holiday in SQL server November 1, 2016 by Gerald Britton Introduction There are plenty of scripts to compute the date of various holidays given the year. Let’s look at the problem in reverse: Given a date, determine if it is a given holiday or not. Read more »
How to create and manage T-SQL code snippets October 28, 2016 by Artemakis Artemiou Introduction Transact-SQL (T-SQL) snippets were first introduced in SQL Server 2012 Management Studio. T-SQL snippets are templates containing one or more T-SQL statements which you can easily use them when you develop T-SQL scripts. The main concept behind code snippets is code reuse. With code reuse you develop faster, easier and with less syntax errors. If you frequently use specific T-SQL statements, then you should consider creating snippets with these statements as they will help you a lot. Read more »
How to create and configure the Tree Map Chart in SQL Server 2016 Reporting Services October 28, 2016 by Rajendra Gupta In the previous article, in this series, How to create and configure a Sunburst chart in SQL Server 2016 Reporting Services, we have explored new chart types in SQL Server 2016; the Sunburst chart and the Tree Map chart, that are used to display hierarchical data. The Sunburst chart is a way of presenting relational datasets together in a compact form. The Tree Map Chart is also a way of showing hierarchical data. In this article, we are going to show how to create a Tree Map chart and its comparison with the Sunburst chart. Read more »
Securing SQL Server Surface Area October 26, 2016 by Artemakis Artemiou In a previous article, we have discussed about the top 10 security factors that you should take into consideration in order to secure your SQL Server instances. In this article I will try to deep dive into one of those factors, that is SQL Server Surface Area. Read more »
How to create and configure SQL Server Agent Alerts October 24, 2016 by Minette Steynberg Introduction If you have ever wanted to run a job dependent on a certain performance condition, then this article is for you. Usually SQL Server agent jobs are configured to run on a schedule. But what if instead of a schedule you want a job to be executed when a certain performance threshold is exceeded? Read more »
SQL Server 2016 Database Scoped Configuration October 21, 2016 by Ahmad Yaseen SQL Server 2016 introduces a new set of configurations that can be applied at the database level to control its performance and behavior. These configurations were previously applicable only at the SQL instance level before version SQL Server 2016, With the ability to configure settings at the database level, many databases that are hosted in the same instance can now be isolated from each other, with each database has its own customized configurations. The database level configurations allow us also to set different database configurations for the Always On Availability Groups primary and secondary databases to meet the different types of workloads. Read more »
SQL Server 2016 – PolyBase tutorial October 21, 2016 by Daniel Calbimonte Introduction This article will teach you how to install PolyBase and will show you a simple example to start. PolyBase is a new feature in SQL Server 2016. It is used to query relational and non-relational databases (NoSQL). You can use PolyBase to query tables and files in Hadoop or in Azure Blob Storage. You can also import or export data to/from Hadoop. Read more »
How to effectively extract data from an OLAP cube by relying upon T-SQL October 19, 2016 by Steve Simon Introduction Last month I ran two Business Intelligence pre-conferences in South Africa. A interesting request arose during the course of the preconference in Cape Town. The individual wanted an approach to extracting data from an OLAP cube that would avoid intensive utilization of MDX and more reliance upon T-SQL. His main concern was with filtering the data at run time, via the report front end. In this “fire side chat” we shall do just that, utilizing the cube that comes with the new Microsoft database “WideWorldImporters” and we shall learn how we can get from this Read more »
SQL Server 2016 Parameter Sniffing October 19, 2016 by Ahmad Yaseen SQL Server tries always to generate the most optimized execution plan for each stored procedure the first time that the stored procedure is executed. The SQL Server Engine looks at the stored procedure passed parameter values when compiling the stored procedure, the first execution, in order to create the optimal plan including the parameters and keep that plan for future use in the plan cache. This parameter analysis process is called the Parameter Sniffing. Read more »
Reporting in SQL Server – Manipulating the data via the grouping functions (provided by the Tablix control) October 18, 2016 by Steve Simon Introduction In many of our past “get togethers” we have discussed pivoting raw data in order to best represent corporate data within our business reporting. As we may remember, in many instances we pivoted the data within the report query. Read more »
How to import a sample bacpac file to an Azure SQL Database using sqlpackage and PowerShell October 18, 2016 by Daniel Calbimonte Introduction In our first part of this series, we showed how to import a bacpac file using the Azure Portal with Microsoft Azure Storage Explorer (MASE) and SQL Server Management Studio (SSMS) to create a Database in Azure. In the second part, we will show how to import a bacpac file to create a Database using the sqlpackage.exe tool and PowerShell. Read more »
Using Dynamic Data Masking in SQL Server 2016 to protect sensitive data October 17, 2016 by Ahmad Yaseen Dynamic Data Masking is a new security feature introduced in SQL Server 2016 that limits the access of unauthorized users to sensitive data at the database layer. As an example of the need for such a feature is allowing the applications developers to access production data for troubleshooting purposes and preventing them from accessing the sensitive data at the same time, without affecting their troubleshooting process. Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person. Read more »
How to create and configure a Sunburst chart in SQL Server 2016 Reporting Services October 12, 2016 by Rajendra Gupta Microsoft SQL Server 2016 introduces several significant new features, and enhances some existing ones in reporting services. We have summarised these new features in the previous article. We’ve also explored how to configure the SQL Server Mobile reports in my earlier article. Read more »
