Timothy Smith

CTEs in SQL Server; Querying Common Table Expressions

January 25, 2019 by

Common table expressions (CTEs) in SQL Server provide us with a tool that allows us to design and organize queries in ways that may allow faster development, troubleshooting, and improve performance. In the first part of this series, we’ll look at querying against these with a practice data set. From examples of wrapped query checks to organization of data to multiple structured queries, we’ll see how many options we have with this tool and where it may be useful when we query data.

Read more »
Bojan Petrovic

SQL index maintenance

January 23, 2019 by

One of the main DBA responsibilities is to ensure databases to perform optimally. The most efficient way to do this is through indexes. SQL indexes are one of the greatest resources when it comes to performance gain. However, the thing about indexes is that they degrade over time.

Read more »
Rajendra Gupta

Working with SQL Server FILESTREAM – Adding columns and moving databases

January 23, 2019 by

SQL Server FILESTREAM is a great feature to store unstructured data into the file system with the metadata into SQL Server database. In the article, FILESTREAM in SQL Server, we wrote to enable the FILESTREAM feature at the instance level. We created the new database for the FILESTREAM and insert sample data into it in the Managing data with SQL Server FILESTREAM tables.

Read more »
Timothy Smith

SQL Bulk Insert Concurrency and Performance Considerations

January 18, 2019 by

One of the challenges we face when using SQL bulk insert from files flat can be concurrency and performance challenges, especially if the load involves a multi-step data flow, where we can’t execute a latter step until we finish with an early step. We also see these optimization challenges with constraints as well, as fewer steps to complete a data flow results in saved time, but possibly less accurate data.

Read more »
Rajendra Gupta

Restoring a SQL Server FILESTREAM enabled database

January 17, 2019 by

In the series on the SQL Server FILESTREAM feature, we have explored the various aspects of FILESTREAM including its overview, internal architecture, database creation etc. In my last article [put the name of the article and a link], we explored the backup for the FILESTREAM enabled database. We can take Full backup as well as filegroup level backup in the FILESTREAM database.

Rajendra Gupta

SQL Server FILESTREAM Database backup overview

January 16, 2019 by

The SQL Server FILESTREAM feature in SQL Server allows storing the large documents files directly into the file systems. In my previous articles, we explored the concepts of FILESTREAM feature in SQL Server. We also learned the folder structures, metadata information and the process of garbage collection. In this article, we are going to learn about the backup and restores in SQL Server.

Read more »
Ed Pollack

Generating Schedules with SQL Server Agent

January 15, 2019 by

Summary

SQL Server Agent allows us to create jobs and assign any number of schedules to them. This allows for great flexibility when deciding when jobs should run. Over time, as the number of jobs and schedules increase on a given SQL Server, it can become challenging to fully understand when and how often jobs run during a given span of time.

Read more »
Ranga Babu

SQL Server Replication with a table with more than 246 columns

January 14, 2019 by

Problem

In our environment we use SQL Server merge replication to replicate data to multiple subscribers. We had a situation where a table needs to be part of merge replication and the table has more than 246 columns. Merge replication supports tables with maximum of 246 columns. In this article let us see how to replicate a table with more than 246 columns.

Timothy Smith

Working With Line Numbers and Errors Using Bulk Insert

January 10, 2019 by

In the first part of reviewing the basics of bulk insert, we looked at importing entire files, specifying delimiters for rows and columns, and bypassing error messages. Sometimes we’ll want to skip first and ending lines, log errors and bad records for review after inserting data, and work with data types directly without first importing using a varchar and converting to the data type later. In this part, we look at these techniques using T-SQL’s native bulk insert.

Ahmad Yaseen

SQL Server Audit Overview

January 9, 2019 by

To fill an optimal role in all business’s computerized operations, information technology systems and infrastructure should be guaranteed, well controlled, aligned with the business goals and secured. Technology auditing ensures the evaluation and examination of these business requirements on an organization.

Read more »
Timothy Smith

Tracking Times In Data Flows for Finding Performance Issues

January 9, 2019 by

We’re facing a challenge with several of our data flows that use more time than they have in the past and we’re not sure when this trend started. We know in the past month, our reports have been delayed by over a day from the start to the finish. For some of our data flows we use SQL Server Agent that calls SSIS packages or procedures, while some of them use a custom data import and reporting application we’ve created. How can we track the length of time for these data flows, since we’re using a combination of tools for importing data?