Add or Remove IDENTITY Property From an Existing Column Efficiently. Introduction Refactoring is often needed to improve schema design or address changes in requirements.
Occasionally, these schema modifications require removing the IDENTITY property from an existing non-empty table while retaining the existing values. Unfortunately, there is no DDL command to add or remove IDENTITY from an existing column. Traditionally, one must go through the pain of either recreating the table or jumping through hoops by adding a new column, updating the new column value with the old column value, and dropping the original column.
This is especially problematic with large tables and short maintenance windows. This article details how to use SWITCH as an alternative technique to add or remove IDENTITY from an existing column. Script SQL Server: Move Tables between Filegroups. How to transfer the tables from one file group to another file group in SQL 2008.? Bitwise Operations in T-SQL. How can you, in T-SQL, get a list of columns that have changed within a trigger?
How can you see what bits are set within a varbinary or integer? How would you pass a bitmap parameter to a system stored procedure? Phil Factor shows how, introduces the bitwise operations, illustrates ways of peeking at bit-level operations, and explains the concepts behind the coding of integer datatypes. Nowadays, you are unlikely to need any bit-level manipulation of data for general work in SQL Server, since most of the places where it was once necessary are no longer so. You can see some of the problems we used to face in deprecated parts of the system such as sys.syspermissions and some system stored procedures (e.g. sys.sp_MSdependencies) that used bitwise flags as parameters.
In any traditional storage system, there is a clear distinction between the storage form of a datatype, its representation form and its transmission form. The data in columns_Updated() is simply a bitmap of the columns. Creating a SQL Server DBA's Library. Problem In a previous MSSQLTips article I discussed the concept of a DBA Configuration Repository which can be a container for all your SQL Server related metadata.
This time, I would like to talk about another best-practice area: the DBA's Library. Solution DBAs - like any other information worker - creates, collects, shares and interacts with all different types of information to help him/her do their job. Some of these pieces of information may be needed repeatedly, some of the knowledge gathered over time may be better saved in a secure location. DBA Knowledge, Containers and Library Before we begin, here are some definitions.
Server build guidesProgram installers and third-party tools, customized DLLs and SQL ScriptsAudits and governance reportsDBA rosters, schedules and work responsibilities, contact details for after-hours supportDiagrams, flow-charts and topologies from system architecturesProcess instructions E-books, presentations and white-papersetc... Building the Library.
Compare Schema of Two SQL Server Databases. SQL in the Wild » Performance. SQL Server Tutorials and Tips. HowTo: Verkleinern einer Microsoft SQL Server Datenbank. Der normale Weg, eine Microsoft SQL Server-Datenbank zu verkleinern ist über das Microsoft SQL Server Management Studio bzw. über den Befehl DBCC SHRINKDATABASE.
Doch was ist, wenn die Datei der Datenbank nicht kleiner wird? Was ist, wenn die Datenbank anzeigt, dass nur noch ein paar Megabyte Speicherplatz frei sind, obwohl Sie jede Menge Datensätze aus der Datenbank gelöscht haben? Das ist mir mit einer Datenbank passiert. Inhalt: ca. 40 Mio. Datensätze, Größe: ca. 12 GB. Maintaining a Log of Database Changes - Part 1. By Scott Mitchell Introduction One of the benefits of using Source Code Control (SCC) software like SourceSafe, Perforce, Subversion, Vault, and others is that the software keeps a detailed history of all changes to the source code.
With SCC, you can see how the code for a particular file has changed over time, and when and who made the changes. Moreover, with such a log in place it is easy to rollback the system to a previous state. Audit table data changes in SQL Server 2005. Some time ago I happened to comment on the post, "How do I track data changes in a database" ...
My comment didn't come out as understandable, so I wrote this little piece to explain how to audit changes in a table. First I create a table to record the changed data: Database Design: A Point in Time Architecture. Point in Time Architecture (PTA) is a database design that guarantees support for two related but different concepts – History and Audit Trail.
History – all information, both current and historical, that as of this moment, we believe to be true. Audit Trail – all information believed to be true at some previous point in time. The distinction is that the Audit Trail shows the history of corrections made to the database. Support for History and Audit Trail facilities are notably absent from typical OLTP databases. By "typical", we mean databases that support the traditional Select, Insert, Delete and Update operations.
Typical OLTP databases destroy data. However, some updates are deemed important while others are not. The Insert statement can be almost as destructive. Setting up log shipping using Management Studio. Real SQL Guy. Introduction. Welcome to SQLBackupRestore.com, your online resource for SQL Server backup and recovery issues.
The articles are generally applicable to both SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL 2012. If any parts are applicable only to a specific version, they will be clearly indicated. The SQL Server Books Online documentation should still be your primary source of information on SQL Server. The information presented here is more of a troubleshooting guide, a discussion of common issues faced by users, when dealing with SQL Server backup and recovery issues. If there are any topics you would like me to add, or suggestions on improving the articles, please drop me a line at yeoh.ray.mond@yohz.com. Conventions When a reference is made to a topic in the Books Online documentation, they are highlighted in bold blue, and the version of SQL Server they apply to is also stated.
Backup SQL Server 2005 and 2008 Databases. I have modified isp_Backup, which is the stored procedure that I use to backup SQL Server databases.
This new version includes one bug fix and three new features: Bug Fix – removed ReportServerTempdb from exclusion list Bug Fix – fixed file retention code to handle database names with spaces Feature – support for SQL Server 2008 including compression Feature – archive bit option Feature – COPY_ONLY option I decided that excluding the ReportServerTempdb from the backups was unnecessary. This database, used by Reporting Services, is in FULL recovery model by default, so this exclusion could have caused you to run out of space in its transaction log. Bryan Conlon, one of my blog readers, noticed that the file retention code was not working when a database name had spaces in it. The archive bit option was added in case you don’t want to delete files that haven’t been backed up to tape. The COPY_ONLY option was added due to a feature request from one of my blog readers, “The Baking DBA”.
DB Tutorials. SQL RNNR. SQL Server Helper. How To Obtain The Size Of All Tables In A SQL Server Database. In a current project we were wondering why an application database grew quickly during the last couple of weeks of development and testing. We wanted to know the space used of each table without using Management Studio's table properties. Actually SQL Server gives you everything you need with its Stored Procedure sp_spaceused. Unfortunately this SP does not support iterating over all tables in a database, so we needed to leverage another (undocumented) Stored Procedure sp_msForEachTable. SET NOCOUNT ON DBCC UPDATEUSAGE(0) -- DB size.EXEC sp_spaceused-- Table row counts and sizes.CREATE TABLE #t ( [name] NVARCHAR(128), [rows] CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18)) INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?
''' SELECT *FROM #t-- # of rows.SELECT SUM(CAST([rows] AS int)) AS [rows]FROM #t DROP TABLE #t The screen shot below shows the results of a smaller testing database.