In most cases, splitting a table into two tables containing different columns is required in order to address database design changes, business requirements or even adding domain restrictions retroactively (for example, isolating currencies in a separate table and enforcing referential integrity via a foreign key to ensure that only valid currencies can be stored in the database). However, splitting tables may actually offer additional performance benefits to the database.April 4, 2013
Database performance is a challenge for every developer and DBA. Even when some improvements are made, there is always a question – is there anything else that can be done.
There might be. Some of the performance improvement techniques are not code related – a proper indexing strategy, adding memory, using different disks for data files, log files, and database backups, using faster disks, optimizing tempdb performance.April 4, 2013
Changing the parameters of a SQL function or a stored procedure is easy – simply ALTER the function or the procedure and add or remove parameters, change the names or data types of the existing parameters and even set default parameter values. However, changing a parameter safely, in most cases, is anything but easyApril 4, 2013
Although SQL Server’s stored procedures help with code security by hiding the implementation of the business logic and even protecting against some kinds of SQL injection attacks — primarily those that use an operator such as AND or OR to append commands onto a valid input parameter value, simply wrapping the code into a stored procedure doesn’t mean that applications, database and SQL Server are safe from all types of SQL injection attacks. So, how to make stored procedures bulletproofed against SQL injections?April 4, 2013
One of the caveats of having your SQL database under a source control system, is the overhead when the time comes to deploy a new database build. Even if a single copy of the database isn’t shared among the developers, but rather each developer has its own local copy of the database objects’ scripts which are synchronized with source control on a regular basis, building a deployment SQL script may prove to be a rather challenging task.April 4, 2013
In addition to replacing the * wildcard in SELECT statements with an explicit list of column names, fully qualifying all SQL object’s names in your SQL queries will boost their performance.April 4, 2013
Having a database scripted to a script folder or to a source control, it enables to use these DDL scripts to recreate it. However, it might not go smoothly due to some missing dependencies.
When creating a database object from scratch, the needed objects are created along the way. The objects can be tied in relationships and constraints to make them dependent on each other.April 4, 2013
Writing and maintaining simple code for stored procedures and functions has never been a problem. However, maintaining complex code, with dozens of parameters and variables, and thousands of rows of SQL statements is. One of the things you can do to make your life easier is to have clean code – without unnecessary and unused parameters and values.
Removing a parameter or variable from code in a production database can cause some problems. They can be easily solved by applying the original object code from a database backup. That’s assuming you can afford the luxury of downtime.April 4, 2013
Very often, initial database design does not fit the current criteria and requirements anymore. It is therefore necessary to periodically align to what our current needs and standards are. When working with a large database, any idea of changing the name of one or more SQL objects can be a real challenge.
Changing an object name is a simple operation and it could be done easily using the SP_RENAME system stored procedure or by utilizing the RENAME and ALTER… RENAME statements for indexes and triggers.April 4, 2013
Renaming a table or view column is not something a SQL developer needs to do every day. However, when the time comes, how can it be done easily and effortlessly?
Just renaming a column is not difficult. To rename the column TerritoryID in the Sales.SalesTerritory table into TerID, execute the following statement:April 4, 2013