May 9, 2012 at 7:02 pm
Hi,
I want to define a policy to make sure every table and every index (including those for indexed views) in the database has "PAGE" compression.
I could only find a condition that checks if a table has compressed partition. Is it possible at all?
Also would it be possible to enforce the use of (nolock) for every SP or function in a database whereever they access a table or indexed view?
Thanks.
June 19, 2012 at 12:47 am
That policy is certainly possible, but why you need that is somewhat not understandable. There are several factors to be considered before implementing the compression on the table.Anyhow, the sys.partitions view let know the compression state i.e 0 = NONE, 1 = ROW, 2 = PAGE.
So for the table part, if you choose the table as facet then the following script evaluation against the value "0" would give you the desired result
ExecuteSql('Numeric', 'select COUNT(*)
FROM sys.partitions P
JOIN sys.tables T
ON (P.object_id = T.object_id)
JOIN sys.schemas S
ON (T.schema_id = S.schema_id)
WHERE T.name = @@ObjectName
AND S.name = @@SchemaName
AND P.data_compression <> 2'
)
Something the same could be done for VIEW facet.
As far as (nolock) is concerned, I do not think that is possible with some laborious work and If I were you, it is something I would not want to look into, unless I have loads of free time.
June 20, 2012 at 7:36 am
Roust_m (5/9/2012)
Hi,I want to define a policy to make sure every table and every index (including those for indexed views) in the database has "PAGE" compression.
I could only find a condition that checks if a table has compressed partition. Is it possible at all?
Also would it be possible to enforce the use of (nolock) for every SP or function in a database whereever they access a table or indexed view?
Thanks.
why do you want to persist the use of NOLOCK a.k.a dirty reads across your database code. If you have data access concurrency issues check your database and query design and maybe employ an alternative Isolation Level above READ COMMITTED!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply