808 reputation
310
bio website drewstechnotes.blogspot.com
location Canada
age 35
visits member for 3 years, 11 months
seen May 14 at 19:05

Big SQL fish in a small SQL pond.


Jun
29
comment CONTAINS function in MS SQL 2008 R2
The only point I'll add is to be aware of the performance implications of both approaches. For the LIKE approach, SQL Server will have to scan every row when you use LIKE '%name%', but you may get away with range scans when you use LIKE 'Na%' assuming you have an index on the column. There is other overhead associated with the Full text indexing which I won't get into here, I just recommend you read up on both and test them to see what does (and will as data volumes grow) perform best :)
Mar
6
comment Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?
Short, simple, to the point so this gets my +1. Do be sure to check out the link to SQLSkills as there is a wealth of good info there.
Feb
23
comment How do I query this 20 million record view faster?
The question is tagged for MS SQL Server so rather than "materialized views" we should be talking about "indexed views" ;)
Feb
23
comment Is there a non-DDL way to preserve back-references when staging data into production?
Can you explain what you're doing to create #StagingOrders and what you do to put non-NULL values into OrderID on the Activity table?
Jan
24
comment Using SQL Profiler on a database that's in production
To minimize impact filter as best you can and trace to a file via sp_trace commands. The GUI running remotely will cause the most impact but you can use it to easily generate a script with all your filters that you can quickly modify to dump to files instead. Set the # of files and file size appropriately.
Jan
21
comment Is there any benefit of a primary key that comprises all columns of the table?
I usually tell people they should use a surrogate key unless they want to guarantee fragmented indexes and poor performance. There are always exceptions but very, very few in this case.
Jan
21
comment Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table?
You can't get a more accurate answer than one from the person who wrote it! Don't forget about the IO load associated with doing multiple rebuilds or reorgs at the same time, and be sure to use ALTER INDEX rather than the DBCC command going forward.
Jan
21
comment Is it better to store images in a BLOB or just the URL?
+1 as I'm also in the "don't put file blobs in the database" camp. It's messy, quickly unmanagable, and won't scale well without throwing money at the problem.
Jan
21
comment Is it better to store images in a BLOB or just the URL?
+1 for pointing out the issues with FILESTREAM as it is today. I'm interested in playing with what they're doing in Denali but honestly - the idea still gives me chills! ;)
Jan
19
comment What is the best query to use to monitor a SQL Server database's status?
I don't think the asker was looking for specific recovery information, but rather a query for a general health check. sys.databases is the better spot to find the db status assuming you're running with sufficient privileges to see offline databases. If it's not in sys.databases it's dropped or detached.
Jan
19
comment How should deletions be handled in the database?
I like the boolean and date field. Depending on how you implement the deletion logic you could even have a distinct table which holds the date and the unique key for the record that was "deleted". Stored procedures make this easy. It takes the additional space per row required down to 1 bit vs. 8+. You would also be able to report on deletions per day without touching the source table.
Jan
19
comment Where should one put indexes in a time dimension table?
Does anyone other than me hear Paul Randal's voice every time they say or read "it depends" with regards to databases? :p
Jan
19
comment How do you document your databases?
Also a +1 for extended properties. I use them for all sorts of management related tasks :D
Jan
19
comment Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space?
Interesting... I hadn't even considered sp_spaceused!! Thanks for this Marian, I think that's just what I was looking for.
Jan
19
comment Replicating a remote MySQL database to MS SQL Server 2008
I wasn't aware this was something that you could do with GoldenDate. I'll have to read more about this; thanks for sharing!
Jan
19
comment Replicating a remote MySQL database to MS SQL Server 2008
Remeber that truncating and pulling all the data over each time could be very slow depending on how much data is involved, network latency, etc. It's definitely not the way to go if you have small or slow network links between the two databases. I always try to devise a method that will allow me to pull deltas.