How to Split a Comma Separated Value (CSV) file into SQL Server Columns December 29, 2016 by Kimberly Killian Receiving a comma delimited file is not new technology nor is it difficult to deal with in SQL Server. As a matter of fact, it is extremely easy. There are many cases as to why you would want to do this. For example, you have an external data source that needs to be imported into your database/table. There a couple ways to do this, however the quickest and easiest way is to use the native “import” feature within SQL Server Management Studio and you can even save it to an SSIS Package at the end of the process. The end result of using this method is that the external CSV file is loaded into a SQL Server table where columns are created and rows are populated. Read more »
Java HashCode in TSQL December 28, 2016 by Ed Pollack The Java HashCode method is used to determine uniqueness or similarity of strings. While implemented in Java, there can be many benefits of creating a similar or customized version of this method. Read more »
SQL Server database migration best practices for low risk and downtime December 27, 2016 by Mustafa EL-Masry Introduction The main goal of many organizations, today, is reducing costs while maintaining the highest degree of stability and efficiency. To this end, we should think out of the box about how we can help to achieve this as DBAs. The approaches include: Read more »
What is a SQL Server Data Dictionary and why would I want to create one? December 26, 2016 by Alen Gubicak A database A SQL Server database can be thought of, in and of itself, as a data dictionary. It is self-describing, in that objects can be scripted into Data Definition Language aka DDL scripts to view all attributes, quickly and easily. Read more »
Increase your Coding Speed in SQL Server Management Studio December 23, 2016 by Samir Behara Every developer wants to be productive and get more things accomplished during their day to day work. Given a choice between working harder and working smarter, you will most likely choose the latter. But the big question is – How do you boost your productivity? How do you reduce your development time? How do you increase your coding speed? Read more »
FAQ about Dates in SQL Server December 22, 2016 by Daniel Calbimonte Introduction In this article, I compiled a list of FAQs and Answers about dates. Which function should I use to get the current date in SQL Server? How can I get the current time in the format hh:mm:ss? How can I calculate my age in SQL Server with a birth date? How can I insert the current time by default in a SQL Server table? How can I check the total time that the employees of my company worked per day? How can I get the time of a specific region? How can I get the time of a specified Standard time? Read more »
Why is my CTE so slow? December 22, 2016 by Gerald Britton Introduction Have you ever written up a complex query using Common Table Expressions (CTEs) only to be disappointed by the performance? Have you been blaming the CTE? This article looks at that problem to show that it is a little deeper than a particular syntax choice and offers some tips on how to improve performance. Read more »
Using SSMA v7.1 for Oracle to migrate into SQL Server December 21, 2016 by Vitor Montalvão Introduction This is a second article that is a continuation of the Microsoft SQL Server Migration Assistant (SSMA) v7.1 for Oracle. In my first article I wrote an overview about the tool and how to install it. In this second article I will write about how to use it to migrate data and objects from an Oracle database to a SQL Server database. Read more »
Handy features of the dbatools Read-DbaBackupHeader command December 21, 2016 by Aaron Nelson If you haven’t heard of dbatools, it’s an open source project out on GitHub dedicated to making life easier for Data Professionals. If you have an idea that you don’t see a command for, you can build it and contribute it to the project. Or you can ask if someone has time to write if for you. Read more »
SQL Server performance myth busters December 21, 2016 by Daniel Calbimonte Introduction In this article, we will verify if the following statements are true or they are just myths: Truncate is faster than a delete and it consumes less space in the logs Do not use cursors to Create or drop multiple objects because it is very slow Do not use cursors to convert or process row values because it is very slow Read more »
New features and enhancements in SQL Server 2016 SP1 December 20, 2016 by Ahmad Yaseen SQL Server 2016 SP1 is released as announced by Microsoft. It comes with a bunch of new features and enhancements as a result of customer and community feedback. In this article, I will introduce you to these new features and enhancements. Read more »
Top 5 New SQL Server 2016 DMVs for DBAs December 20, 2016 by Sifiso W. Ndlovu Since its initial release that was part of SQL Server 2005, Dynamic Management Views (DMV) changed – mostly for the better – the role of database administration (DBA) within SQL Server-based environments. They improved the efficiency of DBAs regarding database server monitoring, issue diagnoses, and subsequent performance optimisation. As a result of this positive reception, it has become customary that some of the highly anticipated items in every release of SQL Server includes discovering what new DMVs will be added. In this article I take you through my favourite top 5 DMVs to come out of SQL Server 2016. Read more »
Reporting in SQL Server – Using calculated Expressions within reports December 19, 2016 by Steve Simon Introduction Late in October, I received an unusual request from the head of sales within one of my client sites. Sales sells three articles: bread, perfume, and Jaguar motor cars. Now the reader will note that one of these items is a staple and the other two are for those folks with considerable disposable income. Management within the firm had increased the salesmen’s bonuses for those folks that managed to sell perfume and/or Jaguars along with the standard loaves of bread. The summary report may be seen below showing the final bonus rate for each sales order booked during the month. Read more »
How to configure Transparent Data Encryption (TDE) in SQL Server December 19, 2016 by Aamir Syed Introduction and Overview Transparent Data Encryption was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). TDE Encrypts SQL Server, Azure SQL Databases, and Azure SQL Data Warehouse data files. Read more »
Point in Time Recovery with SQL Server December 19, 2016 by Dinesh Asanka Introduction How often are you working with multiple environments? For example, if you are a database administrator who is responsible for a production environment as well as another environment, it most likely that you will be working with both environments simultaneously. What is the probability that you will execute a script on production, which actually needs to be executed on the other environment? I would say it is high. To prove this point let me present you an example. Read more »
Introduction to SQL Server Analysis Services (SSAS) monitoring December 15, 2016 by Daniel Tikvicki When it comes to monitoring of SQL Server Analysis Services (SSAS) performance, as it relates to the database engine, there are several categories that should be mentioned and compared. Read more »
Microsoft SQL Server database deployment process December 14, 2016 by Mustafa EL-Masry Introduction Database deployments are critical tasks that can affect negative in on performance in production. In this article we’ll describe some performance related best practices for database deployments. Read more »
How to use SQLCMD commands in the SSMS query editor December 13, 2016 by Marko Zivkovic SQLCMD Mode allows creating, testing, executing SQLCMD commands or scripts in SQL Server Management Studio directly in the query editor. This option is available since SQL Server 2005. Read more »
Creating a “smart” trigger based audit trail for SQL Server December 12, 2016 by Syed Shanu Introduction Audit tables are used to track transactions for a particular table or tables. For every important transaction table, it’s important to create an audit table. Auditing can be helpful if you need to track who is Inserting/Updating and Deleting data on a sensitive table and/or view before and after data change information. Read more »
Microsoft SQL Server Migration Assistant (SSMA) v7.1 for Oracle December 12, 2016 by Vitor Montalvão Introduction SQL Server Migration Assistant (SSMA) is a free supported tool from Microsoft that simplifies and automates database migration processes (assessment analysis, necessary conversions, data migration and tests) from Microsoft Access, MySQL, Oracle, Sybase and DB2 to SQL Server, Azure SQL DB and SQL Server vNext (at the time I’m writing this article, SQL Server vNext stills in CTP1 version, so being a technical preview version it should not be used for production migrations). Read more »