User profile for gokhanvarol – Support

Activity overview Badges (0) Posts (5) Comments (2)

Comments

I know how to make it work by removing the bracket, but in any case sql prompt should not crash and take ssms down with it. This piece of code was part of a longer (over 4500 lines) stored procedure which kept crashing on format, eventually I spent the time and find out what's crashing sqlprompt and posted here. / comments

I know how to make it work by removing the bracket, but in any case sql prompt should not crash and take ssms down with it. This piece of code was part of a longer (over 4500 lines) stored procedur...

0 votes

With very minor changes (see below, removing object_id function , putting subquery down) this query returned in 1:44 and returned rows as below. I can still provide the trace data I am collecting, but if you could put the changes into sql compare and give us a prerelease version or so I'll really appreciated. We are not able to use the tool currently since this query is hanging. Thank you --(3900848 row(s) affected) SELECT CONVERT(bit, CASE i.type WHEN 1 THEN 1 WHEN 5 THEN 1 ELSE 0 END) AS [Clustered], -- 5 is reserved as 'clustered columnstore' which doesn't exist yet CONVERT(bit, CASE i.type WHEN 3 THEN 1 ELSE 0 END) AS Xml, CONVERT(bit, CASE i.type WHEN 5 THEN 1 WHld EN 6 THEN 1 ELSE 0 END) AS Columnstore, i.is_unique AS [Unique], ic.is_included_column AS [Included], i.is_unique_constraint AS UniqueConstraint, i.is_primary_key AS [Primary], i.ignore_dup_key AS IgnoreDupKey, i.is_padded AS IsPadIndex, CONVERT(bit, CASE WHEN o.type='U' THEN 1 ELSE 0 END) AS IsTable, CONVERT(bit, CASE WHEN o.type='V' THEN 1 ELSE 0 END) AS IsView, CONVERT(bit, CASE WHEN fi.object_id IS NOT NULL THEN 1 ELSE 0 END) AS FullTextKey, fg.name AS FileGroup, o.name AS ParentName, os.name AS SchemaName, i.name AS IndexName, i.fill_factor AS [FillFactor], c.name AS ColumnName, ic.is_descending_key AS Descending, CONVERT (bit, 0) AS [Statistics], CASE WHEN i.type IN (3, 4) THEN i.no_recompute/*(SELECT nrp.no_recompute from sys.objects nro JOIN sys.stats nrp ON nro.object_id = nrp.object_id WHERE nro.parent_object_id = i.object_id AND nrp.name = i.name)*/ ELSE s.no_recompute END AS NoRecompute, i.data_space_id, fg.type AS dataspacetype, i.index_id AS indexid, x.using_xml_index_id AS [UsingIndex], xi.name AS [UsingIndexName], x.secondary_type AS [SecondaryXmlType], ic.key_ordinal, ic.partition_ordinal, i.allow_row_locks, i.allow_page_locks, i.is_disabled, i.filter_definition, si.spatial_index_type, si.tessellation_scheme, sit.bounding_box_xmin, sit.bounding_box_ymin, sit.bounding_box_xmax, sit.bounding_box_ymax, sit.level_1_grid, sit.level_2_grid, sit.level_3_grid, sit.level_4_grid, sit.cells_per_object, ps.data_compression, ps.partition_number AS data_compression_partition_number, CONVERT(bit, CASE kc.is_system_named WHEN 1 THEN 1 ELSE 0 END) As is_system_named, kc.[object_id] AS o2, -- hack! Can't see where else to get this value though -- added -- removed OBJECT_ID('.') AS ObjectID -- hack! Can't see where else to get this value though fi.property_list_id INTO #temp FROM (SELECT * ,(SELECT nrp.no_recompute from sys.objects nro JOIN sys.stats nrp ON nro.object_id = nrp.object_id WHERE nro.parent_object_id = i.object_id AND nrp.name = i.NAME AND i.type IN (3, 4)) AS no_recompute FROM sys.indexes i WITH (NOLOCK))i LEFT JOIN sys.key_constraints kc WITH (NOLOCK) ON kc.parent_object_id = i.object_id AND kc.unique_index_id = i.index_id LEFT JOIN sys.data_spaces fg WITH (NOLOCK) ON fg.data_space_id=i.data_space_id LEFT JOIN sys.objects o WITH (NOLOCK) ON o.object_id=i.object_id LEFT JOIN sys.schemas os WITH (NOLOCK) ON os.schema_id=o.schema_id LEFT JOIN sys.index_columns ic WITH (NOLOCK) ON ic.object_id=i.object_id AND ic.index_id=i.index_id LEFT JOIN sys.columns c WITH (NOLOCK) on c.object_id=ic.object_id AND c.column_id=ic.column_id LEFT JOIN sys.stats s WITH (NOLOCK) on s.object_id=i.object_id AND s.name=i.name LEFT JOIN sys.xml_indexes x WITH (NOLOCK) on i.object_id=x.object_id AND i.index_id=x.index_id LEFT JOIN sys.xml_indexes xi WITH (NOLOCK) on i.object_id=xi.object_id AND x.using_xml_index_id = xi.index_id LEFT JOIN sys.spatial_indexes AS si WITH (NOLOCK) ON i.object_id=si.object_id AND i.index_id=si.index_id LEFT JOIN sys.spatial_index_tessellations AS sit WITH (NOLOCK) ON i.object_id = sit.object_id AND i.index_id = sit.index_id LEFT JOIN sys.partitions ps WITH (NOLOCK) ON ps.object_id=o.object_id AND ps.index_id = i.index_id LEFT JOIN sys.fulltext_indexes fi WITH (NOLOCK) on fi.object_id = i.object_id AND fi.unique_index_id = i.index_id WHERE o.type IN ('U', 'V', 'TF', 'TT') AND i.is_hypothetical = 0 ORDER BY i.object_id, x.secondary_type, i.index_id, ic.key_ordinal, ps.partition_number / comments

With very minor changes (see below, removing object_id function , putting subquery down) this query returned in 1:44 and returned rows as below. I can still provide the trace data I am collecting, ...

0 votes

Total activity: 9
Last activity: February 05, 2025 15:16
Member since: February 05, 2025 07:36

Following: 0 users
Followed by: 0 users
Votes: 0
Subscriptions: 2