How to rename database schema and object names without breaking SQL database dependencies

The main purpose of renaming an object or a column in SQL Server is to increase readability, adopt naming conventions, or to give a more meaningful name to an object. The sp_rename procedure will not rename nor update all SQL database dependencies for a renamed object or a column automatically. Therefore all object dependencies should be found by using e.g. the sys.sql_expression_dependencies function and renamed manually.

How to create Visual Studio add-ins with VSPackages

Along with macros and add-ins, VSPackages are a common tool for extending Visual Studio. The main reason why we are writing about them is that Microsoft announced that add-ins will be deprecated in future releases of Visual Studio. VSPackages are supported in all versions of VS coming from 2005. Some would say they are hard to use and understand but once you get to know them a little better they are not that difficult to implement

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) file format 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?

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.

How to refactor a SQL Server database by splitting a table

Depending on your 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.

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