SQL Server Restrict Memory Usage

EXEC sp_configure ‘show advanced options’, 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure ‘max server memory (MB)’, 200

RECONFIGURE WITH OVERRIDE

SQL: Get Comma Seperated Values

 

Select @CSV = isnull(@TRX_REF + ‘,’,”) + seqID from table

Calling webservice from SQL 2000

 Sending  XML data over HTTP to a web service, using XMLHTTP object

 Sqlteam.com/forums/topic.asp?topic_ID=63292

SQL: Group data by weeks

– Group by weeks
starting on Mondays

select count(OrderID) as numOrders,

min(dateadd(day, datediff(day,’19000101′,OrderDate)/7*7, ‘19000101’)) as WeekStarting,

min(OrderDate) as EarliestOrder, max(OrderDate) as LatestOrder from
Northwind..Orders

group by datediff(day,’19000101′,OrderDate)/7

order by WeekStarting

 

via [SQLTeam]

 

SQL Server: Data Source name not found

Recently, I had a problem when SQL Server 2000 Enterprise Manager fails to register (local)database; the error message was “Data Source name not found and no default driver specified”

 

 

This was a problem with faulty installation of SQL Server or somehow ODBC/Data Access Components configurationgone bad. The solution thatworked in my case is to export and import HKLM\Software\ODBC\ODBC.INST\SQL Server registry key from a normal system with ODBC drivers installed to the faulty SQL machine’s registry

 

A Visual Explanation of SQL Joins

SQL: Truncate vs Delete

Truncate:

– deallocates the data pages in a table and only this deallocation is stored in transaction log

– aquires only table and page locks for the whole table. since no
row locks are used less memory is required (lock is a pure memory
object)

– resets identity column if there is one

– removes ALL pages. NO empty pages are left behind in a table

– fast(er)

– doesn’t fire delete triggers

Delete:

– removes one row at the time and every deleted row is stored in the transaction log

– aquires table and/or page and row locks for the whole table

– leaves identity column alone

– can leave empty pages in a table since empty page removal requires a table lock which doesn’t necessarily happen

– slow(er)

– fires delete triggers

[ref]