background preloader

SQL

Facebook Twitter

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?

Bitwise Operations in T-SQL

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.

Creating a SQL Server DBA's Library

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 Scripts.

Partition

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.

HowTo: Verkleinern einer Microsoft SQL Server Datenbank

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. Configuring Database Files for Optimal Perfomance - SQLServerPedia. 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.

Maintaining a Log of Database Changes - Part 1

With SCC, you can see how the code for a particular file has changed over time, and when and who made the changes. 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" ...

Audit table data changes in SQL Server 2005

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: CREATE TABLE [dbo]. 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.

Database Design: A Point in Time Architecture

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. 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.

Introduction

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. 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.

Backup SQL Server 2005 and 2008 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 ''? '''