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 that the life make 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, assuming it can afford the luxury of downtime.
April 4, 2013SQL developers are increasingly required to include tracking data changes into their custom applications. The information about what change was made – insert, update or delete – who made it, when, and also which computer and application was used, is very useful and can be essential for troubleshooting.
April 4, 2013
If there is a need to quickly revert an inadvertent change such as an Update without a Where clause, to recover missing or corrupt data, to move data across development, test, integration and production environments or even just to avoid generating a report using a busy production SQL Server database as a data source, it brings the question if there is a way just to restore specific rows instead of restoring the whole database backup.
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?
April 4, 2013When trying to delete a database object, which has an object that depends on it, no problems can be caused, since it will not allow to be deleted:
Drop failed for Table ‘HumanResources.Employee’. (Microsoft.SqlServer.Smo)
Could not drop object ‘HumanResources.Employee’ because it is referenced by a FOREIGN KEY constraint.
If SQL queries perform sluggishly and severely impact SQL Server performance, the solution can be very simple – in some cases all that needs to be done is to not use the * wildcard in the SELECT statement.
April 4, 2013
Many DBAs think of database documentation as “grunt work”. It’s one of those tasks that you never have enough time for, as you’re always busy with other higher priority tasks.
But what if it could be made automatic?
April 4, 2013Every production database requires maintenance and improvements in order to meet the ever-changing demands. The easiest way to test the new requirements, such as new functionalities and improvements, is to try them on a test database.
So, creating the test base from the backup of the production database would be the easiest way… at least that’s what it looks like. But, is this really the case?
April 4, 2013
