http://www.sqlmag.com/authors/author/author/5069886/rsscomment/5069886en-USFri, 25 May 2012 10:42:25 GMTFri, 25 May 2012 10:42:25 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/moving-lob-data-142636#commentsAnchorThu, 29 Mar 2012 11:19:58 GMT
Important to say that this workaround only apply to Entreprise Edition, because partitioning isn't a feature available on lower editons.]]>Maurice PelchatThu, 29 Mar 2012 11:19:58 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/moving-lob-data-142636#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-many-indexes-should-i-create-137127#commentsAnchorThu, 01 Mar 2012 08:53:44 GMT
Another great read !!! Varun | DB Architect]]>varundhawan45Thu, 01 Mar 2012 08:53:44 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-many-indexes-should-i-create-137127#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-happens-if-i-drop-a-clustered-index-137109#commentsAnchorWed, 29 Feb 2012 22:38:20 GMT
Hey Kim Aree so much 'Marcos', had you've been my teacher, I probably would be writing my own DB engine :-) You guys are AWSOME!!!! Varun | DB Architect]]>varundhawan45Wed, 29 Feb 2012 22:38:20 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-happens-if-i-drop-a-clustered-index-137109#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/solutions-vlt-concerns-statistics-maintenance-142233#commentsAnchorThu, 23 Feb 2012 17:41:47 GMT
Thanks Rowland! It's funny when you put everything together... often I'll end up with PVs and PTs together but rarely PTs alone. There are just too many benefits to splitting tables into smaller tables (even if they are partitioned). Cheers, kt]]>Kimberly L. TrippThu, 23 Feb 2012 17:41:47 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/solutions-vlt-concerns-statistics-maintenance-142233#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/solutions-vlt-concerns-statistics-maintenance-142233#commentsAnchorFri, 10 Feb 2012 08:04:22 GMT
Hard to argue against your proposition that partitioned views are better management tools for VLTs. Nicely done Kimberly. Rowland]]>Rowland GoslingFri, 10 Feb 2012 08:04:22 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/solutions-vlt-concerns-statistics-maintenance-142233#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/partitioned-tables-viewsndashwhy-142158#commentsAnchorWed, 08 Feb 2012 01:55:01 GMT
Few good reasons to still have partitioned views: 1) Backwards compatibility. 2) Not everyone has enterprise edition. Partitioned views works also on standard edition 3) Partition views can work on different databases and different servers. Partitioned tables can work on single database only. 4) Sometimes we need different indexes on the recent data and historical data. We can do it with partitioned views, but we cant do it with partitioned tables. Adi]]>AdicohnWed, 08 Feb 2012 01:55:01 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/partitioned-tables-viewsndashwhy-142158#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/development/whatrsquos-good-unique-filtered-index-141334#commentsAnchorMon, 21 Nov 2011 01:57:31 GMT
I use it to enforce data integrity in our data warehouse. Currently there are two business systems feeding the DW with data, and some tables (like Orders.SalesDetail) containts data from both systems. However, since the two systems have unique keys over a different set of columns, I use unique filtered index to maintain data integrity in the data warehouse for ETL processes and added an identity column as nonclustered primary key and put the ChangedDateTime and Identity column together as a clustered index.]]>SwePesoMon, 21 Nov 2011 01:57:31 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/development/whatrsquos-good-unique-filtered-index-141334#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/development/whatrsquos-good-unique-filtered-index-141334#commentsAnchorFri, 18 Nov 2011 12:39:24 GMT
With the presence of unique filtered index, if my query criteriion is something like "where ssn is null". Why sql server could not take advantage of the unique filtered index by except the these rows followed by the table scan or clusted index scan? Thanks, Pei Zhu]]>pzhu1968Fri, 18 Nov 2011 12:39:24 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/development/whatrsquos-good-unique-filtered-index-141334#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/development/whatrsquos-good-unique-filtered-index-141334#commentsAnchorThu, 17 Nov 2011 21:36:32 GMT
I've used them in a manner similar to Rafi, where a given entity has multiple attributes, only one of which can be active (IsActive = 1) at a time. In the past we used a trigger to enforce this, now I do it with a filtered index. Cleaner, with the added benefit of potentially improving performance.]]>tracymckibbenThu, 17 Nov 2011 21:36:32 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/development/whatrsquos-good-unique-filtered-index-141334#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183#commentsAnchorFri, 11 Nov 2011 12:56:16 GMT
Hey there pjcwik - The auto update stats setting is one that looks over the entire data set BUT not on the fly. Instead, when a certain percentage of rows have changed, SQL Server will review the entire set (possibly using sampling) to generate a completely new stat_header, density_vector and histogram. Because they're looking over the entire set, they can generate more accurate steps. So, in general, I do recommend leaving auto update stats on. However, some folks perform their own updates to stats more frequently and as a result, don't need auto updating. Hope that helps! kt]]>Kimberly L. TrippFri, 11 Nov 2011 12:56:16 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183#commentsAnchorFri, 11 Nov 2011 12:53:12 GMT
Hey there Mark - If you think of the specific possibilities for values between 403 and 407 the possible values are 404, 405 and 406. However, the statistic says there are "2" distinct values within the range (specifically in the table). But, what they don't know is which of the specific values 404, 405 or 406 - doesn't have values. They can only "estimate" 6 for ALL three of those values (if we were to run a query). What is NOT stored within the statistic are which of the 3 do have rows and which of the 3 don't. All we know is the estimated number of rows for any value within that step. Definitely let me know if that doesn't help and/or if you need more information! And, great point Shahchi1 - here's the link to our resources page for anyone that might be interested in watching my statistics video: http://www.sqlskills.com/T_MCMVideos.asp. Cheers, kt]]>Kimberly L. TrippFri, 11 Nov 2011 12:53:12 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183#commentsAnchorTue, 08 Nov 2011 08:29:26 GMT
Good insght, but it then begs the question: What good is the "Auto Update Stats" setting?]]>pjcwikTue, 08 Nov 2011 08:29:26 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183#commentsAnchorMon, 07 Nov 2011 17:22:28 GMT
Kimberly, The following sentence has confused me a little, the rest I get but this only bit throws me a little, "Between the values of 403 and 407 there are *3* possible values. However, this table currently only shows rows for 2 of the values? Which two is NOT stored within the statistic; it is only known at the time the data is analyzed. " If you do not mind could you explain what you mean by "Which two is NOT withing the statistic"? Thanks Mark]]>Mark ByrneMon, 07 Nov 2011 17:22:28 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183#commentsAnchorMon, 07 Nov 2011 09:37:07 GMT
Kimberly, When come to indexes and statistics; I find your insight into those areas as subject matter expert very helpful. Excellent. Anyone reading this article I encourage him/her to watch your MCM video on the statistics.]]>shahchi1Mon, 07 Nov 2011 09:37:07 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/canrsquot-sql-server-update-stats-fly-141183#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/administration/viewing-updated-date-statistics-140899#commentsAnchorThu, 20 Oct 2011 16:35:18 GMT
Very good, but spelling issue: "DBCC SHOW_STASTISTICS ('tablename', 'index or statistics name')"]]>carlje54Thu, 20 Oct 2011 16:35:18 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/administration/viewing-updated-date-statistics-140899#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/forced-parameterizationwhen-should-i-use-it-137149#commentsAnchorTue, 14 Jun 2011 10:20:57 GMT
We are running JDEdwards Enterprise One on SQL Server and one of their recommendations has been to use forced parameterization. Has anyone else had experience doing this for a third party application and did it actually make a difference? ]]>dianahaynwilliamsTue, 14 Jun 2011 10:20:57 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/forced-parameterizationwhen-should-i-use-it-137149#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-sql-server-lets-you-create-redundant-indexes-137128#commentsAnchorThu, 30 Dec 2010 11:19:09 GMT
Is there any other mechanism that can be exploited in SQL 2008 (not R2 specifically) that could be used to prevent dupes? I regularly use a script to add missing indexes and would like to "lock down" the index creation to eliminate unintentional index and statistic dupes from tuning and manual operations by the DBA and developer teams?
Thanks! You folks are my SQL heroes!
]]>sleakbugThu, 30 Dec 2010 11:19:09 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-sql-server-lets-you-create-redundant-indexes-137128#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-sql-server-lets-you-create-redundant-indexes-137128#commentsAnchorSat, 06 Nov 2010 11:22:05 GMT
Right. Thanks Paul. That’s something I forgot about until I realized that my rowcounts for sys.index_columns was twice what I’d expected.

Naturally I tested on an empty table. The overhead of creating and rolling back the index might not be acceptable on large tables.]]>russellbSat, 06 Nov 2010 11:22:05 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-sql-server-lets-you-create-redundant-indexes-137128#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-sql-server-lets-you-create-redundant-indexes-137128#commentsAnchorSat, 06 Nov 2010 10:52:12 GMT
The problem with using a DDL trigger is that it is an AFTER trigger, not an INSTEAD OF trigger. So although a trigger would ’prevent’ this, the index would still be created, and then it would be rolled-back.]]>Paul S. RandalSat, 06 Nov 2010 10:52:12 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-sql-server-lets-you-create-redundant-indexes-137128#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-sql-server-lets-you-create-redundant-indexes-137128#commentsAnchorSat, 06 Nov 2010 08:54:32 GMT
If one wishes to disallow this, a database trigger would do the trick:
[code]
CREATE TRIGGER test ON DATABASE
FOR CREATE_INDEX
AS

SET NOCOUNT ON;

DECLARE @xmlEventData XML
SET @xmlEventData = eventdata()

Declare @table sysname
Declare @indexColumns table (col sysname)
Declare @cmdText varchar(512)
Declare @tempStr varchar(512)
Declare @newIxColCount int
Declare @oldIxColCount int

select @table = Convert(sysname, @xmlEventData.query(’data(/EVENT_INSTANCE/TargetObjectName)’))
select @cmdText = Convert(varchar(512), @xmlEventData.query(’data(/EVENT_INSTANCE/TSQLCommand)’))

--print @cmdText

SELECT @tempStr =
LTRIM(RTRIM(REPLACE(REPLACE(
SUBSTRING(@cmdText,
charindex(@table, @cmdText) + len(@table),
charindex(’)’, @cmdText, charindex(@table, @cmdText) + len(@table))
),
’(’, ’’),
’)’, ’’)
));

WITH indexColumns(n, start, stop) AS (
SELECT 1, 1, CHARINDEX(’,’, @tempStr)
UNION ALL
SELECT n + 1, stop + 1, CHARINDEX(’,’, @tempStr, stop + 1)
FROM indexColumns
WHERE stop > 0
)
INSERT @indexColumns
SELECT LTRIM(RTRIM(SUBSTRING(@tempStr, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END))) FROM indexColumns

SELECT @newIxColCount = @@ROWCOUNT

--select * from sys.columns WHERE object_id = object_id(@table)
--SELECT * FROM sys.index_columns WHERE object_id = object_id(@table)
--select * from @indexColumns

SELECT @oldIxColCount = count(*)
FROM sys.columns c
JOIN sys.index_columns ic
On c.column_id = ic.column_id
And c.object_id = ic.object_id
FULL OUTER JOIN
@indexColumns new
On new.col = c.name
WHERE c.object_id = object_id(@table);


IF @newIxColCount * 2 = @oldIxColCount
BEGIN
Declare @err varchar(312)
SET @err = ’Index covering exact columns on [’ + @table + ’] already exists. Index creation failed’
RaisError (@err, 16, 1)
Rollback
END

--print @oldIxColCount
--print @newIxColCount
GO
[/code]]]>russellbSat, 06 Nov 2010 08:54:32 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-sql-server-lets-you-create-redundant-indexes-137128#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-sql-server-lets-you-create-redundant-indexes-137128#commentsAnchorThu, 21 Oct 2010 16:42:21 GMT
Perhaps adding a warning would be an improvement.]]>Robert L DavisThu, 21 Oct 2010 16:42:21 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-sql-server-lets-you-create-redundant-indexes-137128#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/is-the-entire-clustering-key-duplicated-in-the-nonclustered-indexes-137124#commentsAnchorTue, 12 Oct 2010 12:56:05 GMT
Ha! You’re not the only one! And, the script will help you to better recognize when indexes might be redundant.

Enjoy!
Kimberly]]>Kimberly L. TrippTue, 12 Oct 2010 12:56:05 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/is-the-entire-clustering-key-duplicated-in-the-nonclustered-indexes-137124#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/is-the-entire-clustering-key-duplicated-in-the-nonclustered-indexes-137124#commentsAnchorFri, 10 Sep 2010 21:32:21 GMT
Your article happended to answer a question I wondered for a long time. Many thanks.]]>pelsqlFri, 10 Sep 2010 21:32:21 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/is-the-entire-clustering-key-duplicated-in-the-nonclustered-indexes-137124#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-doesnt-sql-server-use-the-rid-for-lookups-from-a-nonclustered-index-into-a-clustered-table-137123#commentsAnchorFri, 03 Sep 2010 02:54:35 GMT
Nice Q&A topic.
Strangely it isn’t mentioned a clustering index actually holds the data pages. That’s what makes it fundamentally different compared to NCIs, hence all NCIs have been designed to use the uniquified clustered index key to address to the data, in stead of a RID. IMO the question should be "why did the sql team chose this topology?" If a clustering index were only to be used as a guideline for the data rows at index rebuild time (cfr db2ZOs), what would be the pros/cons ? Did they create/publish performance tests using both implementations ?
Another question might be, as shown in diffrent fora rather frequently, " can i rebuild a heap ? Should I rebuild a heap ? How can i reduce wast space in a heap ?"]]>ALZDBAFri, 03 Sep 2010 02:54:35 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/why-doesnt-sql-server-use-the-rid-for-lookups-from-a-nonclustered-index-into-a-clustered-table-137123#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-happens-if-i-drop-a-clustered-index-137109#commentsAnchorTue, 06 Jul 2010 23:16:32 GMT
Thank you very much for this article :)]]>BaburajTue, 06 Jul 2010 23:16:32 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-happens-if-i-drop-a-clustered-index-137109#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorSun, 16 May 2010 22:54:06 GMT
OK, I was going to post this as a follow-on Q&A article but it got a bit out of control (in terms of length) so I expanded it into a blog post. Here’s the link: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Determining-the-position-of-search-arguments-in-a-join.aspx.

Enjoy!
kt]]>Kimberly L. TrippSun, 16 May 2010 22:54:06 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorSun, 16 May 2010 21:15:39 GMT
That’s a great point Phil. In fact, I’m writing another article that relates to this point right now. I’ll link to it when it’s all done. I wasn’t really focusing on the data of the query as much as the specific placement of the SARGs and so I overlooked that point.

Thanks!!
Kimberly]]>Kimberly L. TrippSun, 16 May 2010 21:15:39 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorTue, 04 May 2010 08:56:33 GMT
Kimberly,
Agreed. We’re on the same page. I simply stated what you implied in your posting. You didn’t quite specify exactly that the outer joins turn into inner joins, so I wanted to get that comment out there for other readers.]]>brammpTue, 04 May 2010 08:56:33 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorTue, 04 May 2010 08:55:59 GMT
Kimberly,
Agreed. We’re on the same page. I simply stated what you implied in your posting. You didn’t quite specify exactly that the outer joins turn into inner joins, so I wanted to get that comment out there for other readers.]]>brammpTue, 04 May 2010 08:55:59 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorTue, 04 May 2010 08:10:13 GMT
Hey there Phil-But that’s kind of the point. The *only* difference in the actual code of the query is the position of the search argument. And, when moved - the query becomes completely different. However, people ask me this as a "performance" question (should I move it up into the join to make things faster). For inner joins it doesn’t really matter but for outer joins it makes a HUGE difference on the results and completely changes the meaning of the query...

So *YES* these are NOT the same. That’s the problem!

Cheers,
kt]]>Kimberly L. TrippTue, 04 May 2010 08:10:13 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorTue, 04 May 2010 07:37:18 GMT
In listing 3, the OUTER joins (except for the Person.Person outer join) are actually turned into INNER joins. Listing 3 is in no way the same as listing 4. The code does not match the output in listing 3, in which case I would make the developer rewrite the code if I were doing the code review.]]>brammpTue, 04 May 2010 07:37:18 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/determining-the-position-of-search-arguments-within-a-join-137108#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-happens-if-i-drop-a-clustered-index-137109#commentsAnchorThu, 29 Apr 2010 12:22:57 GMT
Well explained again Kimberly as always... Thanks]]>ManinderThu, 29 Apr 2010 12:22:57 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-happens-if-i-drop-a-clustered-index-137109#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-happens-if-i-drop-a-clustered-index-137109#commentsAnchorMon, 26 Apr 2010 05:41:15 GMT
Hi,

I wish all my teachers were like you.

Thanks again.]]>MarcosGalvaniMon, 26 Apr 2010 05:41:15 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/what-happens-if-i-drop-a-clustered-index-137109#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/changing-the-definition-of-a-clustered-index-137110#commentsAnchorThu, 22 Apr 2010 16:39:11 GMT
If anyone is looking for another way to handle this and similar situations, I have a script that generates drops and creates for candidate keys and referencing foreign keys, available here:

http://sqlblog.com/blogs/adam_machanic/archive/2010/04/04/rejuvinated-script-creates-and-drops-for-candidate-keys-and-referencing-foreign-keys.aspx

]]>Adam MachanicThu, 22 Apr 2010 16:39:11 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/changing-the-definition-of-a-clustered-index-137110#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/comparing-execution-plans-137105#commentsAnchorWed, 31 Mar 2010 16:56:33 GMT
Thanks for writing this. Reading Execution Plans has been somewhat of a mystery to me. This helps to begin the unlocking process. Keep writing & I’ll keep reading!]]>DonKolendaWed, 31 Mar 2010 16:56:33 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/comparing-execution-plans-137105#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/comparing-execution-plans-137105#commentsAnchorWed, 31 Mar 2010 09:00:28 GMT
Always a pleasure to read your work.
Keep writing.
]]>MarcosGalvaniWed, 31 Mar 2010 09:00:28 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/comparing-execution-plans-137105#commentsAnchorhttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/comparing-execution-plans-137105#commentsAnchorFri, 26 Mar 2010 16:45:05 GMT
Hi,

This is an excellent explanation. Thanks for sharing.

Regards,
Mohammed Moinudheen]]>Mohammed MoinudheenFri, 26 Mar 2010 16:45:05 GMThttp://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/comparing-execution-plans-137105#commentsAnchorhttp://www.sqlmag.com/article/configuration/database-design-for-performance#commentsAnchorTue, 23 Jun 2009 10:42:46 GMT
Wonderfull article, but I must say, I do not agree with some of your recomendations, for example, the index on foreign key columns. I see in SQL Server 2005’s dm_db_index_usage_stats that such a index in one of my databases is constantly updated and rarely used so I decided to not use it. Do you agree? Take Care.]]>MarcosTue, 23 Jun 2009 10:42:46 GMThttp://www.sqlmag.com/article/configuration/database-design-for-performance#commentsAnchorhttp://www.sqlmag.com/article/performance/essential-aspects-of-database-design#commentsAnchorFri, 19 Jun 2009 13:38:55 GMT
Wonderful article, I just download the MSDN webcast from Kinberly and I must say: I am impressive with your capacity to translate very complex features in terms everybody can understand and follow. Thanks a lot, and please, continue writing to SQL Magazine, I saw that your last article was 2 years ago. Take Care!]]>MarcosFri, 19 Jun 2009 13:38:55 GMThttp://www.sqlmag.com/article/performance/essential-aspects-of-database-design#commentsAnchorhttp://www.sqlmag.com/article/tsql3/datetime-for-durable-databases#commentsAnchorWed, 25 Mar 2009 09:54:15 GMT
Very basic article... Why don’t you put a flag in the articles to indicate the complexity of it? I would love to be able to identify it in advance so I will be able to choose to lost the time or not.]]>MarcosWed, 25 Mar 2009 09:54:15 GMThttp://www.sqlmag.com/article/tsql3/datetime-for-durable-databases#commentsAnchorhttp://www.sqlmag.com/article/tsql3/just-in-time#commentsAnchorWed, 18 Mar 2009 14:14:37 GMT
Again, this information is available on BOL. Kimberly, please, stop writing.]]>MarcosWed, 18 Mar 2009 14:14:37 GMThttp://www.sqlmag.com/article/tsql3/just-in-time#commentsAnchorhttp://www.sqlmag.com/article/tsql3/the-datetime-dilemma#commentsAnchorWed, 18 Mar 2009 11:49:17 GMT
Not new at all. Kimberly looks like a BOL’s commentator.]]>MarcosWed, 18 Mar 2009 11:49:17 GMThttp://www.sqlmag.com/article/tsql3/the-datetime-dilemma#commentsAnchorhttp://www.sqlmag.com/article/tsql3/manipulate-and-calculate#commentsAnchorFri, 13 Mar 2009 12:28:58 GMT
Looks like you are doing comments aboutBOL. A boring article for anyone who already did the homework and read the BOL. Regards.]]>MarcosFri, 13 Mar 2009 12:28:58 GMThttp://www.sqlmag.com/article/tsql3/manipulate-and-calculate#commentsAnchorhttp://www.sqlmag.com/article/tsql3/simplify-parameterize-reuse#commentsAnchorFri, 13 Mar 2009 12:10:12 GMT
Kimberly, Since I am the first to coment your article in more than 6 years, I don’t want to be rude, but I really don’t get the relevance of it. I mean, in my point of view this topic doesn’t even deserve an article. Regards.]]>MarcosFri, 13 Mar 2009 12:10:12 GMThttp://www.sqlmag.com/article/tsql3/simplify-parameterize-reuse#commentsAnchorhttp://www.sqlmag.com/article/log-files/filegroup-usage-for-vldbs#commentsAnchorWed, 19 Dec 2007 16:17:17 GMT
Concise Article thanks]]>DavidWed, 19 Dec 2007 16:17:17 GMThttp://www.sqlmag.com/article/log-files/filegroup-usage-for-vldbs#commentsAnchorhttp://www.sqlmag.com/article/tsql3/manipulate-and-calculate#commentsAnchorWed, 25 Apr 2007 14:09:21 GMT
Very useful article, provides information on date format and date related function. In real life scenario I have stumble into this problem.]]>ThirendraWed, 25 Apr 2007 14:09:21 GMThttp://www.sqlmag.com/article/tsql3/manipulate-and-calculate#commentsAnchorhttp://www.sqlmag.com/article/tsql3/just-in-time#commentsAnchorThu, 12 Apr 2007 09:07:34 GMT
I know this is an older article, but I wanted to know what anyone thought about using a datetime column as a clustered index. I realize the overhead of using a non-unique field, but the only other key fields are all GUIDs (using NEWID() because this was originally designed for SQL 2000). The GUIDs are have unique indexes on them and are set up as Foreign Keys in other tables. The apps using this design is about to be rewritten so I’ve been asked to help redesign the schema. My instinct says to add an Identity column and make that Primary Key and the Clustered Index, but I’m at odds about whether I should stick with the current design.]]>PhilThu, 12 Apr 2007 09:07:34 GMThttp://www.sqlmag.com/article/tsql3/just-in-time#commentsAnchorhttp://www.sqlmag.com/article/performance/essential-aspects-of-database-design#commentsAnchorMon, 12 Feb 2007 08:12:07 GMT
I enjoyed this article a lot...however, I have 2 comments: 1. It is my understanding that several narrower non-clustered indexes are actually a better approach in many cases than composite indexes since SQL Server 2000 and higher takes advantage of index intersection. 2. I agree with updating stats but I think a better idea than leaving auto-update stats on is to turn it off and then update stats on a nightly or even weekly schedule instead. This way, you don’t have to worry about a slow-down due to SQL Server deciding to update stats in the middle of a busy day. Thoughts?]]>BarryMon, 12 Feb 2007 08:12:07 GMThttp://www.sqlmag.com/article/performance/essential-aspects-of-database-design#commentsAnchorhttp://www.sqlmag.com/article/configuration/database-design-for-performance#commentsAnchorFri, 09 Feb 2007 15:04:08 GMT
Excellent aticle. I am a seasoned dba, IDMS, Teradata, DB2 and now SQL 2000/ 2005, I still pick up a lot of tips from the article. Thanks.]]>BRIGITTEFri, 09 Feb 2007 15:04:08 GMThttp://www.sqlmag.com/article/configuration/database-design-for-performance#commentsAnchorhttp://www.sqlmag.com/article/performance/essential-aspects-of-database-design#commentsAnchorFri, 09 Feb 2007 10:30:02 GMT
Even understandable to those of us without decades of experience (yet). Thank you.]]>ChrisFri, 09 Feb 2007 10:30:02 GMThttp://www.sqlmag.com/article/performance/essential-aspects-of-database-design#commentsAnchorhttp://www.sqlmag.com/article/performance/essential-aspects-of-database-design#commentsAnchorFri, 26 Jan 2007 12:16:28 GMT
Great to see an article like K. Tripps’ Essential Aspects of DB Design. I’ve added it to the material I share with our developers who end up designing databases without having much background. In the SQL Server world it’s all too common that databases are being designed by folks whose main expertise lies elsewhere; SQL Server Magazine can do a lot to mitigate this problem. You mentioned fragmentation. Just today I was looking at some very high fragmentation numbers. The thing is, the data and index files are stored on a dedicated Equalogics iSCSI disk array. These aren’t just iSCSI RAID 10 arrays. One reason for the high price is the device self balances I/O across the spindles as it runs. If it notices a trend that spindle 6 is overused and spindle 3 is underused, it will start migrating blocks from 6 to 3 to balance the load. So, does fragmentation have any meaning on this kind of a device? The analysis tools will think of that volume as one ordinary disk with cylinders and tracks and so forth - but it’s a fakeout. SQL Server is writing to the devices interfaces, which takes full responsibility for maintaining the write order and the atomicity of the write of blocks that are part of a single transaction - even if the power goes out. The real blocks may be all over the place. Clearly the one thing it won’t do is compact down multiple pages that can fit into one. With 2 TB of disk and 64 G memory, a cache hit rate of 99.8%, and disk queues under .2/sec despite 59 databases used by over 100 apps with nearly 3000 users I’m not sure compacting it has much point. Obviously, I’d enjoy a few less articles on how to get by with RAID 5 and some more real life stuff with higher end devices. --------------- all the best Roger Reid]]>DianaFri, 26 Jan 2007 12:16:28 GMThttp://www.sqlmag.com/article/performance/essential-aspects-of-database-design#commentsAnchorhttp://www.sqlmag.com/article/configuration/database-design-for-performance#commentsAnchorFri, 10 Nov 2006 09:50:06 GMT
The article was very well written and assisted with the meeting I had the very next day regarding our performance pitfalls. Thank you!]]>StevenFri, 10 Nov 2006 09:50:06 GMThttp://www.sqlmag.com/article/configuration/database-design-for-performance#commentsAnchorhttp://www.sqlmag.com/article/tsql3/t-sql-tutor-quick-tip#commentsAnchorMon, 25 Sep 2006 14:29:16 GMT
This tip helped me think of a way to overcome a problem I’ve been having with instead of triggers defined on views. If the view gets dropped, so does the trigger. This is good when you really want to delete the view. However, a common way to edit a view is with drop view and create view, which will delete the trigger as well. Enterprise Manager (2000 SP4) will make sure to preserve triggers on tables when table design changes are made which drop the and re-create the table, but it doesn’t do the same with views. It’s difficult to remember to not edit these views with Enterprise Manager or by using the Create option in Query Analyzer. So, I’m going to try this technique to protect the views on which I have created triggers for my application. Thanks for keeping the older articles on here!]]>DANMon, 25 Sep 2006 14:29:16 GMThttp://www.sqlmag.com/article/tsql3/t-sql-tutor-quick-tip#commentsAnchorhttp://www.sqlmag.com/article/tsql3/the-view-from-here#commentsAnchorMon, 17 Jul 2006 10:49:43 GMT
I have since discovered that though design view does not show comments, the comments are in fact saved and viewable from the properties option.]]>LareenMon, 17 Jul 2006 10:49:43 GMThttp://www.sqlmag.com/article/tsql3/the-view-from-here#commentsAnchorhttp://www.sqlmag.com/article/tsql3/all-about-raiserror#commentsAnchorWed, 05 Jul 2006 14:21:44 GMT
Most Excellent. Resource. Can’t find written documentation on level severity (You can see Microsoft.com: "Chapter 11 - Error Messages" but this is on 7.0) You can also view this when you create an alert --> General tab --> Error number --> click button. (2000, Enterprise Mgr).]]>ClaudiaWed, 05 Jul 2006 14:21:44 GMThttp://www.sqlmag.com/article/tsql3/all-about-raiserror#commentsAnchorhttp://www.sqlmag.com/article/tsql3/detailed-aggregates#commentsAnchorWed, 16 Nov 2005 01:03:26 GMT
I loved the way the T-SQL was presented at ever increasing complexity with an excellent description. Came away with an excellent understanding of how and where to apply Compute and Compute by.]]>INGEWed, 16 Nov 2005 01:03:26 GMThttp://www.sqlmag.com/article/tsql3/detailed-aggregates#commentsAnchorhttp://www.sqlmag.com/article/tsql3/the-datetime-dilemma#commentsAnchorMon, 25 Jul 2005 12:32:07 GMT
Thank you for this article. It helped me loads!]]>Anonymous User Mon, 25 Jul 2005 12:32:07 GMThttp://www.sqlmag.com/article/tsql3/the-datetime-dilemma#commentsAnchorhttp://www.sqlmag.com/article/tsql3/aliases-in-t-sql#commentsAnchorMon, 14 Mar 2005 06:56:04 GMT
Great help. Tnx so much.]]>Anonymous User Mon, 14 Mar 2005 06:56:04 GMThttp://www.sqlmag.com/article/tsql3/aliases-in-t-sql#commentsAnchorhttp://www.sqlmag.com/article/tsql3/manipulate-and-calculate#commentsAnchorFri, 03 Dec 2004 21:03:19 GMT
not usefule, what i really want is to get the date part of datetime in date form so that localization issues can be handled on client side]]>Anonymous User Fri, 03 Dec 2004 21:03:19 GMThttp://www.sqlmag.com/article/tsql3/manipulate-and-calculate#commentsAnchorhttp://www.sqlmag.com/article/log-files/log-backups-paused-for-good-reason#commentsAnchorTue, 16 Nov 2004 04:30:24 GMT
How would this work for Yukon? Please let me know at arkhas@gmail.com]]>Anonymous User Tue, 16 Nov 2004 04:30:24 GMThttp://www.sqlmag.com/article/log-files/log-backups-paused-for-good-reason#commentsAnchorhttp://www.sqlmag.com/article/tsql3/all-about-raiserror#commentsAnchorFri, 15 Oct 2004 10:35:49 GMT
The information is good but with most db developers, Query Anaylyzer is the tool of choice over osql. I’ve found that the utility of the RAISERROR command is when it’s used with the WITH LOG option in order to record events to the SQL Server log rather than just displaying an error message.]]>STEVENFri, 15 Oct 2004 10:35:49 GMThttp://www.sqlmag.com/article/tsql3/all-about-raiserror#commentsAnchorhttp://www.sqlmag.com/article/log-files/filegroup-usage-for-vldbs#commentsAnchorFri, 08 Oct 2004 13:56:09 GMT
Thanks for the SAN insight.]]>TomFri, 08 Oct 2004 13:56:09 GMThttp://www.sqlmag.com/article/log-files/filegroup-usage-for-vldbs#commentsAnchorhttp://www.sqlmag.com/article/tsql3/all-about-raiserror#commentsAnchorMon, 26 Jul 2004 09:39:48 GMT
What about placeholders in sysmessages? Where to find the explanation of their meanings?]]>rdjabarov Mon, 26 Jul 2004 09:39:48 GMThttp://www.sqlmag.com/article/tsql3/all-about-raiserror#commentsAnchorhttp://www.sqlmag.com/article/tsql3/all-about-raiserror#commentsAnchorWed, 09 Jun 2004 07:47:39 GMT
Where can I get a listing of the various severity numbers and what they mean ? Thanks]]>Prem Isaac Wed, 09 Jun 2004 07:47:39 GMThttp://www.sqlmag.com/article/tsql3/all-about-raiserror#commentsAnchorhttp://www.sqlmag.com/article/log-files/before-disaster-strikes#commentsAnchorWed, 09 Jun 2004 07:08:45 GMT
Wow, good article. Found it from a more current posting that referenced it. Thanks!]]>Curry, Patrick Wed, 09 Jun 2004 07:08:45 GMThttp://www.sqlmag.com/article/log-files/before-disaster-strikes#commentsAnchorhttp://www.sqlmag.com/article/tsql3/n-table-outer-joins#commentsAnchorFri, 14 May 2004 08:22:14 GMT
Great Article!! Thank you. The power of the left and right join has never been explained so clearly.]]>Bob Slydell Fri, 14 May 2004 08:22:14 GMThttp://www.sqlmag.com/article/tsql3/n-table-outer-joins#commentsAnchorhttp://www.sqlmag.com/article/tsql3/n-table-joins-and-search-conditions#commentsAnchorWed, 12 May 2004 18:08:26 GMT
Excellent!!!!!!!!!!Excellent!!!!!!!!!!Excellent!!!!!!!!!!]]>ragul_dc Wed, 12 May 2004 18:08:26 GMThttp://www.sqlmag.com/article/tsql3/n-table-joins-and-search-conditions#commentsAnchorhttp://www.sqlmag.com/article/tsql3/n-table-outer-joins#commentsAnchorWed, 12 May 2004 17:50:14 GMT
Excellent article, hats off to the author. Good job. I would like to see more documents like this. Thanks]]>JayWed, 12 May 2004 17:50:14 GMThttp://www.sqlmag.com/article/tsql3/n-table-outer-joins#commentsAnchorhttp://www.sqlmag.com/article/tsql3/restoring-after-isolated-disk-failure#commentsAnchorThu, 18 Mar 2004 04:29:56 GMT
Sir, How i get the latest Differential backup/log file number from the backup file. How i get this number through vb program..]]>Unnikrishnan Thu, 18 Mar 2004 04:29:56 GMThttp://www.sqlmag.com/article/tsql3/restoring-after-isolated-disk-failure#commentsAnchorhttp://www.sqlmag.com/article/tsql3/the-outer-join-limits#commentsAnchorWed, 25 Feb 2004 12:10:14 GMT
join article]]>ScottWed, 25 Feb 2004 12:10:14 GMThttp://www.sqlmag.com/article/tsql3/the-outer-join-limits#commentsAnchorhttp://www.sqlmag.com/article/tsql3/the-outer-join-limits#commentsAnchorWed, 25 Feb 2004 07:17:32 GMT
I thought this was a great article. I’ve been having trouble understanding how left/right outer joins differ from regular joins, and this is the first article that I have come across that clearly explains the difference verbally and visually. Thanks.]]>anthonyWed, 25 Feb 2004 07:17:32 GMThttp://www.sqlmag.com/article/tsql3/the-outer-join-limits#commentsAnchorhttp://www.sqlmag.com/article/tsql3/the-view-from-here#commentsAnchorThu, 02 Oct 2003 13:31:42 GMT
I had high hopes for finding out "everything I needed to know", but alas the article didn’t tell me how to enter a comment in a view and make it stick. I type a comment in Enterprise Manager 7.0 and it removes the line.]]>Lareen Lumsden Thu, 02 Oct 2003 13:31:42 GMThttp://www.sqlmag.com/article/tsql3/the-view-from-here#commentsAnchorhttp://www.sqlmag.com/article/log-files/log-backups-paused-for-good-reason#commentsAnchorMon, 21 Jul 2003 11:48:15 GMT
I can’t figure how to correct the situation Microsoft OLE DB Provider for SQL Server error ’80040e14’ The log file for database ’atrafdating’ is full. Back up the transaction log for the database to free up some log space. /left_frame_iframe.asp, line 42 Microsoft OLE DB Provider for SQL Server error ’80040e14’ The log file for database ’atrafdating’ is full. Back up the transaction log for the database to free up some log space. /members_main.asp, line 97 Please answer to amuraim@yahoo.com]]>Amuraim Rotem Mon, 21 Jul 2003 11:48:15 GMThttp://www.sqlmag.com/article/log-files/log-backups-paused-for-good-reason#commentsAnchorhttp://www.sqlmag.com/article/tsql3/detailed-aggregates#commentsAnchorTue, 03 Jun 2003 07:13:44 GMT
Paragraph 2 of the article states: "..you can cownload the Computeby Examples.sql commented script file at InstantDoc ID 38150." 38150 is the InstantDoc ID for the same article, and I am unable to find any means to access the script file. Can you help?]]>Justin Randall Tue, 03 Jun 2003 07:13:44 GMThttp://www.sqlmag.com/article/tsql3/detailed-aggregates#commentsAnchorhttp://www.sqlmag.com/article/log-files/log-backups-paused-for-good-reason#commentsAnchorTue, 19 Nov 2002 08:22:34 GMT
"(...)thus *WOULD* be transactionally consistent during a restore" (1st paragrph) Isn’t it *WOULDN’T* ?]]>Vinícius Oda Tue, 19 Nov 2002 08:22:34 GMThttp://www.sqlmag.com/article/log-files/log-backups-paused-for-good-reason#commentsAnchorhttp://www.sqlmag.com/article/tsql3/joins-101#commentsAnchorWed, 12 Sep 2001 11:49:22 GMT
None of the links seem to be working. It will not let me see any of the figures or download the code as indicated in the article. This is frustrating.]]>Cheril Rinebarger Wed, 12 Sep 2001 11:49:22 GMThttp://www.sqlmag.com/article/tsql3/joins-101#commentsAnchor