How to generate randomized test data from a CSV file

A new SQL database is created, everything is set, but it needs some data source so it can be tested. The question would be, what data source to use in order to populate the SQL Server database with a random test data?

A commonly available data source is the CSV (comma-separated values) formatted file that is widely supported. Among its most common uses is moving tabular data between programs that natively operate on incompatible (often proprietary and/or undocumented) formats. This works because so many programs support some variation of CSV at least as an alternative import/export format.

A CSV file containing the sample data is chosen, and now it needs to be mapped and its data inserted. What are our options?

March 20, 2015

How to visualize SQL database objects dependencies

ApexSQL Search is a free SQL Server Management Studio and Visual Studio add-in that, among other features, provides object a database dependency diagram.

The View Dependencies feature has the ability to visualize all SQL database objects’ relationships, including those between encrypted and system objects, SQL server 2012 specific objects, and objects stored in databases encrypted with Transparent Data Encryption (TDE). Let’s take a closer look at this feature.

March 18, 2015

How to refactor a SQL Server database by splitting a table

Depending on environment, splitting a SQL table may have a positive impact on the overall database performance. For instance, in scenarios where a table contains some large but rarely used columns, moving them to a separate table will increase performance as the frequently used data will be stored in a much smaller table, and the rarely used data will be only looked up when required. The impact on performance caused by the occasional joining will be compensated just by having SQL Server look up the data that’s used more often in a table which requires less disk space leading thus to decrease in I/O and potentially increase in page cache hits.

March 17, 2015

How to add a timestamp to SQL database documentation

Documenting SQL database is the best practice to ensure that the organizational schema, data objects, and other related information are captured for some future references, especially during development. In case of frequent documentation due to constant database structure changes, it is crucial to have documents organized in an accurate and chronological manner.

March 12, 2015

How to package database scripts into C# solutions and executable installers

For users that need to build deployment packages across environments from SQL scripts, either by using the C# solution or executable installer, ApexSQL Build offers an efficient way to accomplish this task in a few simple steps. This applies, for example, to situations where development changes to the structure and content of a production database need to be pushed as an upgrade.

March 12, 2015

SQL Server database trigger auditing – How to customize data tracking and reporting – Part 1

ApexSQL Trigger uses SQL Server database triggers for capturing and storing changes that occur on the audited data and schema. More specifically, the DML database triggers are used for capturing INSERT, UPDATE or DELETE statements, and DDL trigger is used for capturing CREATE, ALTER or DROP statements. Now, since there are various types of columns in each database table, reading the audit report is often not very user-friendly and this especially applies to foreign key values in different tables.

March 12, 2015

How to customize SQL database documentation

Using the default settings and options when documenting a SQL database via ApexSQL Doc does not have to be the only option, especially if there are additional options available, like Styles. You can personalize SQL databases documentation to make it stand out and differ, adding to its professional look.

March 9, 2015