by

They said “If you don’t let it out, You’re going to let it eat you away” – CHARINDEX with SUBSTRING

Greetings and other Awesome Salutations,
It has been a while because I am learning oodles of new stuff. There are a few things that I don’t want to forget so here goes.

CHARINDEX is a super easy way to get the starting location of something within a string. For example, I was looking for the last half of Superhero names. My column is SuperheroName in the Comics Table and pretend there are dashes in their names, please just for this example – I know there aren’t really dashes please continue to love me with my flaws.
So…
SELECT TOP 4 SuperheroName
FROM Comics

Results:
Bat-man
Bat-girl
Super-man
Super-girl

Now the magic, CHARINDEX will give you the location of the character you specify.

SELECT TOP 4 CHARINDEX('-', SuperheroName)
FROM Comics

Results:
4
4
6
6

How cool is that?!! But I want to pull back everything after the “-” character, which means I have to get tricky because CHARINDEX will only give me the location number of where it is within the string. SUBSTRING will save the day (Tada!). SUBSTRING is really cool. I am going to pass it ColumnName, Starting Position (using CHARINDEX), Ending Position (using LEN and the column name that will get me the length of that column on that row). So it looks like this:

SELECT TOP 4 SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName), LEN(SuperheroName))
FROM Comics

Results:
-man
-girl
-man
-girl

But that still isn’t quite what I want…so I am going to add 1 to the Starting point position number (this will allow me to skip over the character I am using):

SELECT TOP 4 SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName)+1, LEN(SuperheroName))
FROM Comics

Results:
man
girl
man
girl

Oh but wait, there is more! I think it is really silly to have the dashes in the first place so check this out:
SELECT TOP 4
SUBSTRING(SuperheroName,1,CHARINDEX('-',SuperheroName)-1) + SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName)+1, LEN(SuperheroName))
FROM Comics

Results:
Batman
Batgirl
Superman
Supergirl

Holy Rusted Metal Batman! What did we do? This time in my substring I used the first position and then the CHARINDEX -1 to tell me what was right before the dash. Then I combined them! With these super powers combined…well awesome things happen.

Enjoy!

by

You’ve been Thunderstruck and can now Find any Table in an Instance

Have you ever remembered what Intstance a table was on but not the database name? This will solve your problem. First you will create the table, then the procedure that will look for it and finally, you will execute the procedure with your table name. (At the end you can drop it so no one knows your secret.)
It returns the database, the schema, the table name and a type.

SET NOCOUNT ON
CREATE TABLE SearchTable(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, TABLE_TYPE varchar(50)) --If you haven't ever used this.
GO 
 
CREATE PROC usp_FindMyTable (@TABLE_NAME sysname = null)
AS
SET NOCOUNT ON 

TRUNCATE TABLE SearchTable -- If you are using it again.
IF @TABLE_NAME IS NULL
BEGIN
PRINT 'No Table to look for. Please supply a table name. Like: ' + CHAR(13)
+ ' EXEC usp_FindMyTable Orders'
GOTO usp_FindMyTable_Exit
END 
DECLARE @MAX_dbname sysname, @dbname sysname, @sql varchar(8000) 
SELECT @MAX_dbname = MAX([name]), @dbname = MIN([name]) FROM master..sysdatabases 
WHILE @dbname @dbname
END 

SELECT * FROM SearchTable 
usp_FindMyTable_Exit:
SET NOCOUNT OFF
RETURNGO
 
EXEC usp_FindMyTable 'Unicorn' -- put in the table you are searching for here. GO
 
SET NOCOUNT OFF
DROP PROC usp_FindMyTable --Clean up so no one knows
DROP TABLE SearchTable --Really, it is our secret. More clean up.
GO

by

Don’t you cry no more…you can find the TCP Port

Greetings to the Kingdom!

Huge thanks to my friends Dan and Tamie for this one.  It will help you determine the TCP Port for an instance.  I love my friends in the SQL Community and am so happy they are willing to share!

DECLARE @key VARCHAR(50), @RegistryPath VARCHAR(200)
IF (SERVERPROPERTY('INSTANCENAME')) IS NULL
BEGIN
SET @RegistryPath='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
END
ELSE
BEGIN
SET @RegistryPath='Software\Microsoft\Microsoft SQL Server\'+CONVERT(VARCHAR(25),SERVERPROPERTY('INSTANCENAME'))+'\MSSQLServer\SuperSocketNetLib\Tcp\'
END
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @RegistryPath, 'tcpPort'

Have a magical day!

by

Like some kind of day dream I’ll never forget…I made the “Who would be your top speaker choices?” for PASS Summit 2013!!!!

This is just a quick post to say thank you for whomever put me on this list.  I can’t believe it, and am totally bouncing off the walls right now.  The survey results are here:


http://www.sqlpass.org/LinkClick.aspx?fileticket=u_budRInYfA%3d&tabid=1476

I will submit my abstracts now.  I was debating on if anyone would want to listen to me, thank you for making me feel important!  Keep your fingers crossed that I get accepted to speak. I have a different dress for each day of the PASS Summit, I just have to figure out how to pack them.  I hope to see you all there!

Have a magical day, I totally am!

THANK YOU!!!!

by

Conditional Formatting in Reporting Services

I’m certainly no Reporting Services master, and far from a Business Intelligence Developer, but I do like to tinker around with reports for the bosses from time to time

I like Excel for it’s conditional formatting feature, especially in building heat-map type charts.

So I was looking for a way to replicate the functionality in SSRS and figured I’d pass it on:

Just as an example, boss comes and asks “Hey, what’s the busiest time of day for our order-entry website?”

This is easily accomplished by changing the Properties of the cell ( in my case textbox2 ) Fill > BackgroundColor > and click on Expression…

Properties

Expression

From here it is pretty intuitive… I select a color from the values field and using the SWITCH function can apply green for < 100,  yellow for 100 – 500, red for 500-1000, etc.

I end up using the more colors link down right to select a visually appealing color range.

HeatMapReport

This technique can also be used to create alternating background colors that makes a report more readable as used here…

AlternatingRows

It’s not magic, but logic. However, as Andrea says “Have a magical day!”

by

Let Me Riddle You a Ditty It’s Just an Itty Bitty Little Thing on My Mind About a Group of Unused Indexes Being Disabled All at the Same Time…

Now the funny thing about it, not a story without it, but the story is mine and it ended just fine…Happily Ever After

Today I have a Vendor Application that has WAY too many indexes and they need to rebuild them all tonight in a very tight window.  I am disabling all the unused indexes to allow for a faster rebuild and then will rebuild the disabled indexes tomorrow night after all the heavy work is done.  The Vendor is nervous about us disabling indexes that haven’t been used in over a month.  The good news is that you can simply disable them and then rebuild them if they are needed.  I know the rebuild will take some time, but we have all agreed that if they haven’t been used in over a month, they most likely won’t be used in the next 24 hours.  Now the good part. Running this t-sql code not only tells you what indexes have not been used since the last reboot, but it also gives you a rebuild indexes script that can easily be changed to a disable script. The scripts can all be run at once and will save you oodles of time.

SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
I.NAME AS IndexName,
'ALTER INDEX [' + I.Name + '] ON ' + OBJECT_NAME(I.OBJECT_ID) + ' ReBuild' --' Disable'
AS SQLToRebuildOrDisable
FROM sys.indexes I
WHERE /* only get indexes for user created tables*/
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
/* making sure it is not a primary key*/
and i.is_primary_key = 0
and i.is_unique = 0
/* If the are returned in this table, they are in use so we are getting everything not in this table.*/
AND NOT EXISTS (
SELECT index_id
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = I.OBJECT_ID
AND I.index_id = index_id
-- This will limit to the db you are on.
AND database_id = DB_ID())
-- The Index must have a name for this to work.
AND I.name IS NOT NULL
ORDER BY SchemaName, ObjectName, IndexName

I hope it helps you as much as it has helped me today!
Have a magical day!

by

Say what you mean, tell me I’m right and reset the identity….

Today I wanted to change the number in my identity column to be way higher. My plan is to union my new table with another table that has a low id and be able to tell what is coming from where.  I know that my old table will never got over 100,000 records, but to be safe I set the number in the new table to start at 700,000.  This is called seeding the identity.  You can also reset your identity this way.  Use caution when you are doing this because if you reseed the identity and it is lower than an existing identity in the table, it will complain when you hit that identity if you are inserting.   Here is my example:

DBCC CHECKIDENT ([Schema.TableName], RESEED, 700000)

Happy Planting!