I developed some complicated reports for my company lately, and the query can be slow up the 10 seconds. using with nolock or readpast really do the trick. ex: reading only commited rows select * from some table with(readpast) src=http://articles.techrepublic.com.com/5100-10878_11-6185492.html
Archive for the ‘Database’ Category
MS SQL copy table structure without data
June 3rd, 2008
No Comments
SELECT * INTO newTABLE FROM oldTABLE WHERE 1=2
replace field value using isNull and NullIf
May 28th, 2008
No Comments
select IsNull(Nullif(ifFieldValue1qual0, 0), ReplacewithFieldValue2) also, in MS Sql 2005, use NewID() to get random recordset such as: select top 3 * from someTable Order By NewID()
Recursive Queries in SQL:1999 and SQL Server 2005
May 16th, 2008
No Comments
query:WITH tree (data, id, level, pathstr) AS (SELECT VHC_NAME, VHC_ID, 0, CAST(” AS VARCHAR(MAX)) FROM T_VEHICULE WHERE VHC_ID_FATHER IS NULL UNION ALL SELECT VHC_NAME, VHC_ID, t.level + 1, t.pathstr + V.VHC_NAME FROM T_VEHICULE V INNER JOIN tree t ON t.id = V.VHC_ID_FATHER) SELECT SPACE(level) + data as data, id, level, pathstr FROM tree ORDER BY [...]
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) error
April 9th, 2008
1 Comment
This error involves in calling a recursive trigger, function or procedure in the database. To disable the recursive or nested triggers, you need to execute the following in the MS SQL 2005 SP_CONFIGURE ‘nested_triggers’,0 GO RECONFIGURE GO or, you can set TRIGGER_NESTLEVEL() < 2 List list ‘recusive trigger config. SP_CONFIGURE ‘nested_triggers’ To list all trigger [...]
Store Procedure craziness
March 30th, 2008
No Comments
Four guys in two days convert over a couple of hundred sql statement into procedures. I wish there is software smart enough to do the job, but the sql sometime is too complex in logic. Anyways, my friend, D uses some of these functions to establish connection and query which I think very helpful. Sub [...]
SnowTweet
Xe Dap Oi !