Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Select from table and alter result Expand / Collapse
Author
Message
Posted Monday, December 9, 2013 8:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:30 AM
Points: 256, Visits: 773
How do I select from a SQL Table and change the result?

For example..

declare FindFragment cursor for
SELECT object_id, name FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')

--Cursor to go through each index which are between 5% and 40% fragmented and rebuild

open FindFragment
fetch next from FindFragment into @ObjectID, @result

I want to change the 'name' field I have selected from a temporary table, which is put into @result in the cursor to have closed brackets around it.
Post #1521143
Posted Monday, December 9, 2013 9:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:47 AM
Points: 1,361, Visits: 15,263
If you are developing an in-house index degfragmentation tool, I personally wouldn't invest the time reinventing the wheel. There are several options already developed and freely available on the web.

We use this one in house, courtesy of Ola Hallengren.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Post #1521164
Posted Monday, December 9, 2013 9:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:40 AM
Points: 42,419, Visits: 35,482
declare FindFragment cursor for
SELECT object_id, '['+name+']' FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')

Or don't spend time re-inventing the wheel and use http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1521165
Posted Monday, December 9, 2013 9:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:30 AM
Points: 256, Visits: 773
It was working fine until it came across an index on a view earlier, since i referenced sys.tables instead of sys.objects it didn't pick these up. This is now resolved. Then I noticed it didn't take into account if an index had a '.' in the name of it.. so this is where this change has come from..

--Create temp table for list of indexes

CREATE TABLE #IndexFrag(
database_id int,
object_ID int,
index_id int,
name ntext,
page_count int,
avg_fragmentation_In_Percent real )

--Fill the table with indexes with a page count higher than 10 and fragmented more than 5%

insert into #IndexFrag (database_id, object_ID, index_id, name, page_count, avg_fragmentation_In_Percent)

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name, ps.page_count,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() and ps.page_count > 10 and ps.avg_fragmentation_in_percent > 5
ORDER BY ps.OBJECT_ID

--Selecting all index's between 5% and 40% fragmented

declare @cnt int
declare @Result varchar(200)
declare @cmd nvarchar(500)
declare @tablename nvarchar(500)
declare @objectID int

declare FindFragment cursor for
SELECT object_id, '['+name+']' FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')

--Cursor to go through each index which are between 5% and 40% fragmented and rebuild

open FindFragment
fetch next from FindFragment into @ObjectID, @result
while @@fetch_status = 0
BEGIN
select @tablename = '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' FROM sys.indexes i
INNER JOIN sys.objects AS t ON i.object_id = t.object_id WHERE i.name = @Result AND i.object_id = @objectID

set @cmd = N'ALTER Index ' + @result + ' on ' + @tablename + ' REORGANIZE '

print @cmd

--EXEC sp_executeSQL @cmd

fetch next from FindFragment into @objectID, @result
END

close FindFragment
deallocate FindFragment

--drop table #IndexFrag

declare FindFragment cursor for
SELECT object_id, '['+name+']' FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 40) and (name not like 'null')


--Cursor to go through each index which are over 40% fragmented and rebuild

open FindFragment
fetch next from FindFragment into @objectID, @result
while @@fetch_status = 0
BEGIN
select @tablename = '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' FROM sys.indexes i
INNER JOIN sys.objects AS t ON i.object_id = t.object_id WHERE i.name = @Result AND i.object_id = @objectID

set @cmd = N'ALTER Index ' + @result + ' on ' + @tablename + ' REBUILD '
PRINT @cmd

--EXEC sp_executeSQL @cmd

fetch next from FindFragment into @objectID, @result
END

close FindFragment
deallocate FindFragment

drop table #IndexFrag

Thats the full script, works well for what I need, but changing it to '[' + name + ']' brings back the error

''The data types varchar and ntext are incompatible in the add operator.''
We can't do a select as in this scenario can we?
Post #1521179
Posted Monday, December 9, 2013 9:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
CREATE TABLE #IndexFrag(
database_id int,
object_ID int,
index_id int,
name varchar(max),
page_count int,
avg_fragmentation_In_Percent real )

Change your NAME column to varchar(max) and I think your code will work.

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1521199
Posted Monday, December 9, 2013 10:31 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
First, see the notes above about Ola Hallengren's work. Second, you could also use the QUOTENAME function like this:

select QUOTENAME(name) from sys.indexes;

Another thing to keep in mind is that if you fire an index REBUILD, the index isn't available while the rebuild is running. If you're using Enterprise Edition, you can specify that you want it kept online by using the ONLINE option, but there are some restrictions you have to step around. See http://technet.microsoft.com/en-us/library/ms188388.aspx, which I'm sure you have already seen if you're writing this process.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1521221
Posted Monday, December 9, 2013 11:31 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 980, Visits: 1,324
So I had a little time to play with this script. I found where you introduced a bug when you added the square braces around your table name, where by you would never the table name on the following statement:

WHERE i.name = @Result

However by doing a compare on object_id should be sufficient so I changed your where clause to read"

WHERE i.object_id = @objectID

Also, I'm no big fan on looking for nulls the way you described in this construct:

 and (name not like 'null')

When I look for nulls (or not null) I would use the following construct:

and name not null

So there is my 2 cents worth. These types of scripts teach you a lot about SQL Server. If you are fluent with the system tables and can spin up code rather quickly, then by all means roll your own. However there are a great number of resources where fellow DBAs have plowed that road before you and you can get something up and fully functional with little to no effort. So in other words, no need to reinvent the wheel as a number of others have pointed out before.

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1521248
Posted Monday, December 9, 2013 4:23 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:50 PM
Points: 1,946, Visits: 2,877
FYI: the wheel's been re-invented countless times, or we'd all be riding around on wooden wheels with no rims!

SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1521348
Posted Tuesday, December 10, 2013 4:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:30 AM
Points: 256, Visits: 773
Appreciate your feedback guys, I will make the changes this afternoon.

Thanks for taking the time to test it Kurt, appreciate it and I prefer your way around nulls too.

I appreciate it has been done before, but instead of implementing something somebody has already done, I saw this as a good way to learn.
Post #1521460
Posted Thursday, December 12, 2013 2:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:30 AM
Points: 256, Visits: 773
Kurt, Making those adjustments still came back with the same error - very strange I can't see where it is getting it from.

Ed, "using select QUOTENAME(name) from sys.indexes" worked a treat and seems to be coming back with the right results!

Appreciate your help!
Post #1522201
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse