Tag Info

Hot answers tagged

3

First I'm assuming since you didn't specify that you want your primary key to be clustered. It's the default and most common usage. If not then the order of your column will depend more on the selectivity of the two columns and your usage. However, if you are making your primary key clustered then if you look in this Technet article on the aligning ...


2

Partitioning should work better than index if the number of rows is large. If the query needs to scan only one partition, it is more efficient than finding current rows through index. If SRC_CURRENT is updated to 0 when newer rows are loaded, you need to enable row movement for the table. This will allow rows to move from current partition to historical ...


1

After a little bit more digging on the MySQL website, I found out about Exchanging Partitions and Subpartitions with Tables, which seems to be a new MySQL 5.6 feature: ALTER TABLE x EXCHANGE PARTITION y WITH TABLE z; It seems to be exactly what I was looking for. After running some benchmarks on my server, I found out that exchanging a partition with a ...


1

Normally I would do somehthing like this using T-SQL, even if Powershell was running that T-SQL, eg SELECT * INTO dbo.copyTable FROM dbo.originalTable WHERE 1 = 2 GO CREATE CLUSTERED COLUMNSTORE INDEX _CS ON dbo.copyTable GO However I did get the following Powershell to work as a simple example: $instance = ".\sql2014" $server = New-Object ...


1

I know you already accepted the answer, but it seems to me, since you're constantly aging out the active rows, you could simply create a function based index on the SRC_CURRENT column. It'd be something like this: create index src_current_active_fbidx on <table>(decode(src_current, 1, 1, null)); This will create a compacted index of only the rows ...



Only top voted, non community-wiki answers of a minimum length are eligible