Filtered index X cannot be created on table Y because the filter expression contains a comparison with a literal NULL value. Rewrite the comparison to use the IS [NOT] NULL comparison operator to test for NULL values
@PaulWhite good question. I'm just thinking it might happen depending on what they are using the UID for. There is very little detail in the question, so I'm trying to not assume too much.
@MaxVernon I just cannot imagine any scenario in which that index would be used for anything except point lookups. Also, UUID uniqueness always needs to be enforced. The current index is non-unique.
Perhaps the UID field is used to describe some field data gathering equipment, and he wants to return results for all pieces of equipment that exist in a certain set. I don't know, I'm likely stretching it a bit.
@PaulWhite if the IO engine has to read the entire index into memory, and it is physically fragmented on disk, then surely seek time on disk will come into play much more than it would for an unfragmented index, no?
And I guess as long as the machine has tons of RAM, and can keep the index in memory, then it won't matter at all.
@PaulWhite the new values would be unique from that point on, but surely there might be UID generated that already existed in the table prior to changing to NEWSEQUENTIALID(), no?
@PaulWhite I certainly don't mean to argue or belabor the point Paul - I clearly defer to your experience. And I agree a UID is supposed to be universally unique, by design.
@MaxVernon There are certainly use cases where a uniqueidentifier column would contain duplicates (e.g. as a foreign key) but these cases would not be suitable for NEWSEQUENTIALID, by definition.
@PaulWhite what advice would you give him? I was leaning towards not changing the field simply because it would involve a fair amount of work to implement, with little perceived benefit. I posted my answer mainly because he is dealing with a single disk, and I like to have data non-fragmented in that scenario.
if it was me, I think I would fry the much bigger fish first. Like getting it on an SSD for instance.
@MaxVernon I don't think there's much in it either way for the situation he describes. As I mentioned, the biggest 'pro' from my point of view is being able to change the fill factor to 100% so the index would be smaller, and maintenance would be unnecessary. Whether that's worth the effort is his decision.
If he had a very high rate of inserts, the discussion could be very different.
@PaulWhite agreed. What's your thoughts on his 80% fill factor? Seems kind of random to me, just wondering if anyone has ever looked at the statistics for that for NEWID() columns.
@MaxVernon Ideally he would be monitoring page fullness over time and doing maintenance only when necessary. 20% free space for random insert points is a fairly common guesstimate.
Again, ideally, his monitoring would lead to tweaks in fill factor until he hits the sweet spot where the index becomes evenly full just when index maintenance was due to start.
I reindex, then reorganize my indexes every night for optimal perfomants. I then shrink the database down so my san admin doesn't yell. My question: why mssql performants so bad?
Well it is good, but I'm not sure death is a price worth paying :)
@MaxVernon Wouldn't it be better to work any points you found persuasive into your answer instead? From a future-viewers point of view, if nothing else?
I am trying to set up a Database first project using the Sybase ADO.Net provider in VS 2013. The first step would be registering the ADO.Net Provider, but haven't had any luck. I have ASE 15.7 installed, and have tried to register the provider via the commandline:
AseGacUtility4 -i Sybase.AdoN...
Please let me know if this is the wrong forum for this question, but got very little traffic on Stack Overflow.
I am trying to use the Visual Studio Server Explorer to connect to a Sybase Database using the ASE provider. The first step would be registering the ADO.Net Provider, but haven't had a...
