December 9, 2013 at 8:46 am
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.
December 9, 2013 at 9:01 am
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
December 9, 2013 at 9:01 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2013 at 9:24 am
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?
December 9, 2013 at 9:57 am
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
December 9, 2013 at 10:31 am
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.
December 9, 2013 at 11:31 am
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
December 9, 2013 at 4:23 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 10, 2013 at 4:40 am
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.
December 12, 2013 at 2:19 am
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!
December 15, 2013 at 12:21 am
MysteryJimbo (12/9/2013)
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
great, thanks Mystery for sharing the link
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply