Reputation
4,239
Top tag
Next privilege 5,000 Rep.
Approve tag wiki edits
Badges
3 20 38
Impact
~217k people reached

May
9
comment Strange SSIS-Package problem ( ASYNC_NETWORK_IO)
Set Bad Habits to kick - putting NOLOCK everywhere - it is NOT RECOMMENDED to use this everywhere - quite the contrary!
Apr
15
comment Restoring a backup of a database from SQL Server 2014 into SQL Server 2012
@BuahahaXD: NO - the compatibility level does NOT change the internal database structures. This does not help - you just CANNOT do this.
Apr
14
comment How to restore mdf file in sql server 2005?
what error message?!?!? I don't see anything .....
Apr
1
comment Execution Plan : Table Insert Cost 90% vs Clustured Index Insert Cost 86%
For a great many reasons, I would argue any serious table in SQL Server should always have a clustered index - therefore, that discussion is kinda moot..... heaps (tables without CI) have serious drawbacks and performance penalties - too many to even bother - use a CI - always.
Mar
25
comment Differentiate SQL Server Express from SQL Server installed in a server
@RayofCommand: it might violate the Principle of Least Surprise - but it's possible ! :-)
Mar
11
comment Keep Execution Plan in Cache - SQL Server 2012
It's going to automatically stay in memory - if you use it frequently enough, and if there isn't any "memory pressure" (too little memory) on the server. You cannot really force a plan to stay fixed in cache - it will be tossed out if memory is too tight....
Feb
21
comment Different runtimes using parameter vs. constant
WHY is DATE_CREATED not a proper DATE datatype?? Please always use the most appropriate datatype - and for a date - that's DATE but certainly not a VARCHAR type!
Feb
10
comment NVARCHAR column as PRIMARY KEY or as UNIQUE column
Actually, NVARCHAR(20) is 40 bytes in size (max), and since it's variable-length column, it's not really the best choice for a clustered index. ID_CODE being a BIGINT IDENTITY would be the much better choice here!
Jan
30
comment Soccer league ER diagram (implementing a many to many relationship)
have some m:n relationships which I know shouldn't exist - WHY do you think m:n relationships shouldn't exist?!?!?!?! Those are just a fact of life! Nothing wrong with that! Where did you "learn" that???
Jan
29
comment Trigger AFTER INSERT on tables with different columns
It's a column (not collumn - one l is quite enough!)
Jan
29
comment When does SQL Server acquire locks?
Another point: by default SQL Server will use the finest grained locks possible - row-level locks for individual rows, or possibly page-level locks when selecting ranges in a clustered table. But if your session acquires more than 5000 locks, SQL Server will do a lock escalation and switch to a table-level locking, which is undesirable in a production system, since not even reads are possible from that table anymore, until the locks are released (until the transaction is done). So try to keep your updates in chunks of less than 5000 at a time
Jan
29
comment When does SQL Server acquire locks?
The exclusive locks for update and delete operations are acquired when needed and retained until the end of the transaction. The main point is trying to keep your transaction short and sweet to minimize locking time
Jan
20
comment T-SQL — Why do ALTER and UPDATE statement only run when separated into their own batch?
Just a brief side note: GO is not a (SQL) statement - it's a separator that is specific to SQL Server Mgmt Studio.
Jan
15
comment Subquery returned more than 1 value. This is not permitted + Trigger Error SQL Server
Your trigger has MAJOR FLAW in that you seem to assume it'll be called once per row - that is not the case. The trigger will fire once per statement, so if your UPDATE statements affects 25 rows, you'll get the trigger fired once, but then Inserted and Deleted will each contain 25 rows. Which of those 25 rows will your code select? It's non-deterministic. You need to rewrite your trigger to take this into account!
Dec
26
comment Will a non-clustered index on a column with covering index on all the columns of table affect the Delete statement Records)?
Generally, SQL Server will use row-level locks to lock those rows that are being manipulated (and that includes rows being deleted). However, if you manipulate more than 5000 rows in a single transaction, then SQL Server will do a lock escalation and lock the entire table that's being deleted from exclusively - no other operations (not even SELECT) are possible anymore. So you might want to break up your delete operation into chunks of less than 5000 rows each
Dec
19
comment How to identify unused databases on my sql server
If in doubt, just take the database offline, and if no one complains about it for a week - nobody is using it ....
Dec
18
comment Microsoft recommends using Rowversion over timestamp , but i can not find “rowversion” data type inside my sql server 2008 R2
You should AVOID using reserved keywords (like TIMESTAMP) as column names - use something more meaningful! Then it'll work.
Dec
2
comment Why could a SQL Server table grow far quicker than the data to it?
Since it appears to be mainly LOB_DATA - could it be that the newly added rows have significantly larger entries into the AuditData column? Since it's a nvarchar(max), it could theoretically hold up to 2 GB for each row.
Nov
29
comment Column name or number of supplied values does not match table definition error
@codeOholic: no - you just need to insert valid values for those two foreign keys....
Nov
29
comment If a database is in memory does it still need indexing?
OF COURSE! even if it's in memory, if you have to scan tens of thousands of pages for a full table scan, that's vastly less efficient than being able to walk through an index tree on a handful of pages..... also: it's much more likely that the tens of index pages are kept in memory, rather than ALL of thousands or tens of thousands of actual data pages