 |
Unmanaged Memory Allocations
|
| Total Hit (7411) |
If you are going to be using pointers, it may make sense to allocate from an unmanaged heap. For the vast majority of needs is not any better than the using the garbage collector, but if you are allocating a large amount of memory, especially for short periods of time, need better control of when me
....Read More |
Rating
 |
|
|
|
|
|
 |
How to use ORDER BY clause in the definition of a view?
|
| Total Hit (2514) |
SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, we can't fully explain th
....Read More |
Rating
 |
|
|
|
|
|
Rotate a Table in SQL Server
|
| Total Hit (1659) |
This article describes how to rotate a SQL Server table. Suppose you have a table that is named QTRSALES. The table has the columns YEAR, QUARTER, and AMOUNT with the data in the following format (note that there is no row for the fourth quarter of 1996):
Year Quarter Amount
------
....Read More |
| Rating
|
|
|
|
|
|
How to Fix Inconsistent Metadata Errors (SQL Server 7.0)
|
| Total Hit (3347) |
Why does changing a table on a SQL Server 7.0 server cause "OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time." to occur when query the table from a SQL Server 2000 server via link to other.
BASIC UNDERSTAND
....Read More |
| Rating
|
|
|
|
How to perform update on multiple tables of linked server.
|
| Total Hit (2458) |
This code demonstrates
1) How to write update trigger which checks modification for a specified field (e.g. here DistrictId)
2) How to handle Errors during update
3) How to update data on linked server.
4) How to do update which requires joining tables
5) How to use XACT_ABORT option to handle
....Read More |
| Rating
|
|
|
|
|
|
|
|
Selective Defrag/Reindex and Log
|
| Total Hit (3125) |
SQL 2000 Only
This script will create a stored procedure and a table in the master database. Simply run the stored procedure and supply a database name as a parameter. It will do the following:
1. Gather/Log DBCC SHOWCONTIG statistics (pre defrag)
2. Defragment all user indexes in the dat
....Read More |
| Rating
|
|
|
|
Create Excel XLS from T-SQL
|
| Total Hit (3998) |
This is a T-SQL script that uses OLE, ADO, Jet4 ISAM, and Linked Server to create and populate an Excel Workbook (XLS) file from T-SQL query. If the Excel Worksheet exists, the query will append to the "table".
The code is designed to be used by SQL Agent and to append to the step output with verb
....Read More |
| Rating
|
|
|
|
Get all user tables with size
|
| Total Hit (1555) |
Returns User Tables by SIZE in DESCENDING ORDER
Add TOP n clause in SELECT to get selective results (i.e. TOP 10 TABLES)
|
| Rating
|
|
|
|
Monitoring Disk Space and Sending Alerts with TSQL
|
| Total Hit (5727) |
Monitoring disk space is one important task for SQL Server DBAs. To proactively monitor disk space, we want to be notified when disk space is below certain level. We also want to collect database file size information over time for trend analysis, for which Gregory Larsen has an excellent article "A
....Read More |
| Rating
|
|
|
|
Avoid Auto Close and Auto Shrink.
|
| Total Hit (3037) |
I was on-site with a client, whose had a server which performing very sluggishly. It was a beefy brute with heaps of memory and processing power, so clearly something was just not what it should have been.
For me step 1 in doing any sort of trouble-shooting is to look at the logs. Yup, always a
....Read More |
| Rating
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
How to implement nested transactions
|
| Total Hit (4242) |
SQL Server allows you to nest transactions. Basically, this feature means that a new transaction can start even though the previous one is not complete. Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The @@TRANCOUNT automatic variable can be queried to
....Read More |
| Rating
|
|
|
|
How to do error handling in transaction.
|
| Total Hit (2225) |
The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do is halt the sequential processing of the code and either branch to another code segment in the proc
....Read More |
| Rating
|
|
|
|
SAVE TRAN and Save Points.
|
| Total Hit (2536) |
Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the SA
....Read More |
| Rating
|
|
|
|
How to script SQL tables using SQL DMO
|
| Total Hit (2255) |
«B»Introduction«/B»
SQL-DMO encapsulates SQL Server components and presents them as attributes of the component piece to you as properties of an object instance. You would have to alter the properties of the instance or use object methods to automate SQL Server administration. This article takes
....Read More |
| Rating
|
|