Voice of the DBA

Tag: SQLNewBlogger

  • Upper Casing All the Data–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    There was a post recently where a user asked about needing to update all the data in all their tables to upper case. A somewhat strange requirement, though one I could see in some environments where they want to ensure all  data matches in searches.

    There isn’t a good way to do this, since you have disparate table names and column names. The quick way to do this one time is with a cursor, though I’d worry about performance here if this ran at any scale, and more often than once in a very rare time.

    In any case, the way to get schema, table and column names is to query the INFORMATION_SCHEMA.COLUMNS view. I’d limit this to character columns of the non- and Unicode types. In the code below, I get that data into the cursor.

    DECLARE updatecurs CURSOR FOR
SELECT
      c.TABLE_SCHEMA
    , c.TABLE_NAME
    , c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE
      c.DATA_TYPE IN ( 'varchar', 'char', 'nvarchar', 'nchar' );
DECLARE
     @schema VARCHAR(100)
   , @table VARCHAR(100)
   , @col VARCHAR(100)
   , @cmd VARCHAR(8000);
OPEN updatecurs;
FETCH NEXT FROM updatecurs
INTO
     @schema
   , @table
   , @col;
WHILE @@FETCH_STATUS = 0
BEGIN
     SELECT @cmd = 'update ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) 
     SELECT @cmd = @cmd + ' set ' + @col + '= UPPER(' + @col + ')'
     SELECT @cmd
     FETCH NEXT FROM updatecurs
     INTO
         @schema
       , @table
       , @col;
END;
DEALLOCATE updatecurs;

    The code then loops through the cursor and builds an update statement for each column. This could be enhanced to get a single statement for all columns in a table, but it’s a quick and dirty piece of code.

    There is a SELECT @cmd statement in there that shows what command is executed. To make this work, that would be changed to EXEC(@cmd), but make sure the code is what you want.

    Not pretty, but effective.

    SQLNewBlogger

    Quick, effective code. This has caveats, but works.

    If you want to write about this, change the cursor to make one update per table.

  • Changing the Database Collation–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I was testing some changes recently and needed to verify how things worked in a different collation. Here’s the scenario: I had this situation and ran a query.

    2019-02-13 12_22_07-SQLQuery7.sql - Plato_SQL2017.sandbox2 (PLATO_Steve (64))_ - Microsoft SQL Serve

    As you can see, the query rates these two Unicode strings as equal, which makes some sense as this is a Case Insensitive, Accent Sensitive, default US English database.

    I then went to the database properties to change this. When I did so, I picked a case sensitive collation.

    2019-02-13 12_22_55-Database Properties - sandbox2

    I clicked OK, and a few minutes, later I get this:

    2019-02-13 12_25_38-Database Properties - sandbox2

    Hmmm, why is this locked on my workstation? I have a query window open. Changing a collation is potentially a disruptive operation and requires an exclusive lock on the database. This means no other users can be inside the database with a connection.

    The solution? Change the context of my query window. I can change this to master (or any other database) and make the change again.

    While collation isn’t something you often change, if you do find a database that needs this alteration, you will need to drain off any connections before you can make this change.

    SQLNewblogger

    I ran across this while doing testing and realized this was a nice, short piece of information that I should remember. Not because I do this often, but because as I automate changes and use pipelines, I’d need a way to remove all connections before this would deploy.

    An example of a nice short piece of information that I can document about my knowledge, and that might get asked about in an interview.

  • Getting Parameters Out From a Stored Procedure–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    One of the lesser used and known features of T-SQL are the output parameters from a stored procedure. I used one of these recently, so I wanted to blog about it.

    Getting a String

    I was working on part of the 2018 Advent of Code, which is a great set of programming exercises for anyone. I typically build a procedure to solve each puzzle, since that’s a common way of capturing code. If there’s a single numeric result, a RETURN code works fine.

    In one puzzle, I needed to return a string. If you try this in a procedure, it won’t work.

    2018-12-27 10_04_01-SQLQuery6.sql - Plato_SQL2016.sandbox (PLATO_Steve (58))_ - Microsoft SQL Server

    Instead, I need another solution. I could certainly SELECT back my string, but in this case, I wanted to have this assigned to a variable. I could do that in a few ways, but decided the easiest was an OUTPUT parameter.

    To add an output parameter to my procedure, I first add my variable as a regular parameter.

    CREATE OR ALTER PROCEDURE dbo.StringTest
       @s VARCHAR(10)
    
AS
    
BEGIN
         SELECT @s = 'Some Code'
    
END
    
GO

    Next, I add the OUTPUT keyword after the type.

    CREATE OR ALTER PROCEDURE dbo.StringTest
       @s VARCHAR(10) = '' OUTPUT
    
AS
    
BEGIN
         SELECT @s = 'Some Code'
    
END
    
GO

    My call to the procedure should also include the OUTPUT keyword.

    DECLARE @result VARCHAR(10);
    
EXEC dbo.StringTest @s = @result OUTPUT;
    
SELECT @result;

    This works fine, allowing me to pass some value back to the caller.

    2018-12-27 10_08_40-SQLQuery6.sql - Plato_SQL2016.sandbox (PLATO_Steve (58))_ - Microsoft SQL Server

    Not something I use often, but if I need to get some singular value back, this works.

    SQLNewBlogger

    This post was started at 10:00am one morning. I got back to this sentence at 10:09. That was the entire setup of the code, capturing screen shots, and writing the post. Easy for you to do as well.

    Give this a try. How would you use an OUTPUT parameter?

  • Sorting Values in a Column

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    This was a post that caught my eye, since I’d just written a piece on STRING_SPLIT(). Someone had this data:

    DECLARE @Names VARCHAR(8000) = 'Steve, Grant, Kathi, Kendra';

    They wanted this returned as a string that was sorted, so that the values would be:

    Grant, Kathi, Kendra, Steve

    String manipulation isn’t the strength of SQL Server, but we can do this with STRING_SPLIT(). If we use STRING_SPLIT(), we get a list of values. We want to remove the spaces after the comma, so we use REPLACE to remove that.

    SELECT *
    
FROM STRING_SPLIT(REPLACE(@Names, ' ', ''), ',')
    
ORDER BY value;

    We can then aggregate these back together in a variable assignment, adding the comma for each row.

    SELECT @newtext = @newtext + Value + ', '
    
FROM STRING_SPLIT(REPLACE(@Names, ' ', ''), ',')
    
ORDER BY value;

    This gives us:

    2018-12-21 15_05_08-SQLQuery1.sql - Plato_SQL2017.sandbox (PLATO_Steve (55))_ - Microsoft SQL Server

    SQLNewBlogger

    A quick T-SQL application of some skills I learned. Can you do something similar? Maybe order dates or numbers that are in a string in the wrong order?

    That should be a 10-15 minute post.