October 23, 2017 at 7:48 am
You say you've tried in your latest post (in your duplicate topic). Moving the topic back to your original thread, what is it you've tried since further input on what tables to use, and how to get data from them? Can you post your latest SQL please?
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 23, 2017 at 8:14 am
SELECT distinct
object_name(sp.object_id)
, rows AS NoofRows
FROM sys.partitions sp
INNER JOIN sys.tables st on st.object_id = sp.object_id
WHERE index_id NOT IN (5) -- clustered index or heap
and rows > 1000000
and st.schema_id not in (4) -- not in sys scheme
but this is not fulfilling requirement 1 .
October 23, 2017 at 8:30 am
No, like I said, let's get the row counts right before we look at the indexes. Why did you change index_id IN (0,1) to index_id NOT IN (5)? You're only interested in the table data itself, which is index_id 0 or 1 - the heap or clustered index. If you have any filtered indexes on your table then your query is going to give unexpected results. So change that one line, and then we can think about the indexes.
You now need to add a join to sys.indexes. Since you're interested in something that doesn't exist, (the columnstore index) you want to do an outer join (LEFT JOIN) on index_id = index_id and type_desc LIKE '%COLUMNSTORE', and then filter WHERE indexes.index_id IS NULL. Try that, and then we'll be ready to tweak your query so it satisifes the second requirement as well.
John
October 23, 2017 at 8:39 am
SELECT distinct
ss.name,
object_name(sp.object_id)
, rows AS NoofRows
FROM sys.partitions sp
INNER JOIN sys.tables st on st.object_id = sp.object_id
INNER JOIN sys.schemas ss on ss.schema_id= st.schema_id
LEFT JOIN sys.indexes si on si.object_id = st.object_id and si.type_desc LIKE '%COLUMNSTORE'
WHERE sp.index_id IN (0,1)
and rows > 1000000
and st.schema_id not in (4) -- not in sys scheme
and si.index_id IS NULL
Please validate the query
October 23, 2017 at 8:42 am
The other thing to note is John used GROUP BY in his query, to cater for partitions. You've used a DISTINCT and dropped the GROUP BY. If you have partitions, this is still going to give multiple results per partitions, unless they all have exactly the same amount of rows (and even then, the answer would be wrong. If you have 4 sets of 4 apples, you have 16 apples; not 4).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 23, 2017 at 8:42 am
ramrajan - Monday, October 23, 2017 1:09 AMI have to design a script to find all tables in a database with below criteria.
1. Identify all tables should have more than one million records and doesn't have any columnstoreindex on it.
2. Identify all the tables which contain both columnsstoreindex and non-clustered index.
Requirement 1, SO how do you expect to identify tables that SHOULD HAVE more than one million rows of data? We can determine what tables HAVE more than one million rows of data. In fact, you are doing just that in the post above.
October 23, 2017 at 8:43 am
even after including the group by it is not giving the right result.
October 23, 2017 at 8:47 am
Just to reiterate the whole discussion below is what I want to achieve.
1. Identify all tables should have more than one million records and doesn't have any columnstoreindex on it.
2. Identify all the tables which contain both columnsstoreindex and non-clustered index.
October 23, 2017 at 8:47 am
ramrajan - Monday, October 23, 2017 8:43 AMeven after including the group by it is not giving the right result.
Show us the query then, please, and how the results differ from what they should look like.
John
October 23, 2017 at 8:54 am
Since you seem to be having such a hard time with this and against my best instincts, give this a try:
WITH BaseTables AS (
SELECT
[t].[object_id]
, TableName = [t].[name]
, rowcnt = SUM([p].[rows])
FROM
[sys].[tables] AS [t]
INNER JOIN [sys].[partitions] AS [p]
ON [p].[object_id] = [t].[object_id]
AND [p].[index_id] IN (0,1)
GROUP BY
[t].[object_id]
, [t].[name]
HAVING
SUM([p].[rows]) >= 1000000
), ColumnStoreIndexes AS (
SELECT
.[object_id]
, IndexName = .[name]
FROM
[sys].[indexes] AS
WHERE
.[type] IN (5,6) -- columnstore indexes
)
SELECT
[bt].[object_id]
, [bt].[TableName]
, [bt].[rowcnt]
, [csi].[object_id]
, [csi].[IndexName]
FROM
[BaseTables] AS [bt]
INNER JOIN [ColumnStoreIndexes] AS [csi]
ON [csi].[object_id] = [bt].[object_id];
October 23, 2017 at 9:03 am
for requirement 2. i am doing below .
select distinct ss.name , object_name(st.object_id) , * from sys.indexes si
INNER JOIN sys.tables st on st.object_id = si.object_id
INNER JOIN sys.schemas ss on ss.schema_id= st.schema_id
left outer join sys.indexes si1 on si1.index_id = si.index_id and si.index_id IN(2)
left outer join sys.indexes si2 on si1.index_id = si.index_id and si.index_id IN(5)
where si1.index_id is null and si2.index_id is null
and st.schema_id not in (4) -- not in sys scheme
October 23, 2017 at 9:03 am
Too early on a Monday morning:
Try this:
WITH BaseTables AS (
SELECT
[t].[object_id]
, TableName = [t].[name]
, rowcnt = SUM([p].[rows])
FROM
[sys].[tables] AS [t]
INNER JOIN [sys].[partitions] AS [p]
ON [p].[object_id] = [t].[object_id]
AND [p].[index_id] IN (0,1)
GROUP BY
[t].[object_id]
, [t].[name]
HAVING
SUM([p].[rows]) >= 1000000
), ColumnStoreIndexes AS (
SELECT
.[object_id]
, IndexName = .[name]
FROM
[sys].[indexes] AS
WHERE
.[type] IN (5,6) -- columnstore indexes
)
--SELECT
-- [bt].[object_id]
-- , [bt].[TableName]
-- , [bt].[rowcnt]
-- , [csi].[object_id]
-- , [csi].[IndexName]
--FROM
-- [BaseTables] AS [bt]
-- INNER JOIN [ColumnStoreIndexes] AS [csi]
-- ON [csi].[object_id] = [bt].[object_id];
SELECT
[bt].[object_id]
, [bt].[TableName]
, [bt].[rowcnt]
FROM
[BaseTables] AS [bt]
WHERE
NOT EXISTS(SELECT 1 FROM [ColumnStoreIndexes] AS [csi] WHERE [bt].[object_id] = [csi].[object_id]);
October 23, 2017 at 9:31 am
Can you also post the query fo rrequirement 2
Identify all the tables which contain both columnsstoreindex and non-clustered index.
October 23, 2017 at 9:43 am
ramrajan - Monday, October 23, 2017 3:53 AMCan you please share the complete query instead of just half ?
You won't learn anything that way... You need to learn to start experimenting with what you're given...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 23, 2017 at 9:48 am
WITH ColumnStoreIndexes AS (
SELECT
.[object_id]
, IndexName = .[name]
FROM
[sys].[indexes] AS
WHERE
.[type] IN (5,6) -- columnstore indexes
), NonClusteredIndex AS (
SELECT
.[object_id]
, IndexName = .[name]
FROM
[sys].[indexes] AS
WHERE
.[type] = 2
)
SELECT DISTINCT sc.name, OBJECT_NAME(csi.object_id)
FROM ColumnStoreIndexes csi
INNER JOIN NonClusteredIndex nsi on nsi.[object_id] = csi.[object_id]
INNER JOIN [sys].[tables] AS [t] on t.object_id = csi.object_id
INNER JOIN sys.schemas sc on sc.schema_id = t.schema_id
Please let me know if this is correct ?
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply