I'm actually using SQL 2014. Anyhow, thanks for the advice. I'll look into that as I never used FILESTREAM.

Thanks for the response. The data is well structure. For a while we've been doing exactly what you suggested, storing the data in a table and creating the indexes...

dwain.c (3/17/2015)

---

N_Muller (3/17/2015)

---

Very good article. I would like to point out my preference for INSTEAD OF triggers on views. The view could be as simple as SELECT * FROM...

Very good article. I would like to point out my preference for INSTEAD OF triggers on views. The view could be as simple as SELECT * FROM

. I...

Michael Valentine Jones (2/26/2015)

---

I have done this in order to avoid having to create an index on the string column.

Since this column is a unique identifier, you would probably still...

Relatively static data can be clustered on any column that fits your querying criteria. However if you deal with a high transaction level OLTP where new records associated with the...

There is no problem letting them query the string directly. I'm mostly interested on the performance, though.

Thanks.

It seems to have done the trick but I need to test on my complex XML. One thing I didn't get is the "order by @@spid" in teh row_number(). ...

Thanks and here's a query:

SELECT

SequenceID = R.value('for $i in . return count(../*[. << $i]) + 1', 'INT'),

ObjectID = R.value('identifier[1]','VARCHAR(50)'),

Name = R.value('name[1]','VARCHAR(50)'),

Type = R.value('type[1]','VARCHAR(50)'),

IsCool = R.value('isCool[1]','VARCHAR(3)'),

StartDateTime = R.value('startDate[1]','DATETIME2(0)'),

EndDateTime = R.value('endDate[1]','DATETIME2(0)')

FROM...

As far as I know there are two ways of reading XML using SQL, either openxml or XQuery. There's a lot of debate on which is better or faster....

Thanks Craig and JeeTee for the responses.

The table variable was just an example. There are permanent tables. One table stores the current values - it has an order of...

curious_sqldba (8/19/2014)

---

Are there any other reasons except maintenance overhead if I split my partitions to separate file group? Reason I say that is if I have multiple file groups I...

You can't create partitioned tables across different databases. You can get significant performance improvements with partitioned tables on OLTP, even if it is just for managing and archiving data.

Thanks, Jeff. I'll give it a try.

Jeff Moden (8/13/2014)There's nothing preventing a non-partitioned index from doing this quickly. Index on the event as the lead column and add the partitioning column and it should work...