sp_spaceused: Determine SQL Server table size

Have you ever wondered which tables take the most disk space in your database? Using the…

sp_configure: Limit SQL Server memory usage

Use the sp_configure system stored procedure with the max server memory option to limit the amount of memory in the buffer pool used by an instance of SQL Server or MSDE. This will prevent SQL Server from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. You cannot […]

GO (SQL Server TSQL)

Signals the end of a batch of SQL statements in Microsoft SQL Server utilities. It is not a Transact-SQL command. Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited […]

Comments (SQL Server)

Comments indicate user-provided text. There are two different types of comments: the — comment and the /*…*/ comment. Comments can be inserted on a separate line, nested (– only) at the end of a SQL command line, or within a SQL statement. The comment is not evaluated by the server. Two hyphens (–) is the SQL-92 […]

DBCC CHECKCONSTRAINS: Verify foreign key integrity

There are two scenarios in SQL Server that can leave tables with rows that are not bound by an existing FOREIGN KEY constraint or CHECK constraint: Newly added constraints ignore existing table data if created using “WITH NOCHECK”. Re-enabled constraints ignore all table updates and inserts that took place since the constraint was disabled. Use […]

UPDATE (SQL Statement)

Description Changes existing data in a table. Syntax UPDATE  { table_name  | view_name } SET { column_name = expression } [ ,…n ] [ FROM { table_name  | view_name } [ ,…n ] ] [ WHERE < search_condition > ] Example UPDATE table1 SET c1 = 9 Detail UPDATE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and db_securityadmin roles, and the table owner […]

DELETE (SQL Statement)

Description Removes rows from a table.   Syntax DELETE { table_name  | view_name } [ FROM { table_name  | view_name } [ ,…n ] ] [ WHERE < search_condition > ]   Example DELETE table1 WHERE table1.c1 > 10 Detail DELETE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner. Members of the sysadmin, db_owner, and the db_securityadmin roles, and the table owner […]

SELECT (SQL Statement)

Description Retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables.   Syntax SELECT [ DISTINCT ] [ TOP n [ PERCENT ]] select_list FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]   Example SELECT * FROM table1 INNER […]

DBCC INDEXDEFRAG Improves SQL Server Index Scanning

Fragmented SQL Server indexes can result in performance degradation and longer index scanning time. DBCC INDEXDEFRAG helps improve index scanning performance by defragmenting clustered and nonclustered indexes.   Syntax DBCC INDEXDEFRAG (database1, table1, index1) Results DBCC INDEXDEFRAG returns a result set similar to this: Pages Scanned Pages Moved Pages Removed ————- ———– ————- 359           346         […]

SQL Server Data Manipulation Language (DML)

Data Manipulation Language is composed of the following commands:   SELECT (SQL Statement) INSERT (SQL Statement) UPDATE (SQL Statement) DELETE (SQL Statement)   Other statements include: SELECT INTO BULK INSERT TRUNCATE TABLE READTEXT WRITETEXT UPDATETEXT