Management Studio – Faster with multiple select

Management Studio – Faster with multiple select

In SQL Server Management Studio (SSMS for close friends) but also in a multitude of text editors (such as Notepad++ for example), you can make multiple selections.

I thought it was something everyone knew, but I realize that I often look like a magician every time I do it.

I’m delighted to pretend to be Harry Potter, but I think it’s time for this little game to stop!

Look by yourself how simple it is!

How?

You should press the keys [ALT] + [Shift] simultaneously and move your cursor [Up] and / or [Down] to select your text.

One useful case

Sometimes we have to surround our text with single quotes. (for example: to add multiple codes to an IN clause in a test query)

No need to add them one by one and make sure you do not have space at the end.
Here is the method:

Like mentioned above, this tip is not an exclusivity in SSMS, you can also do the same in many different text editor.

SSIS – Create Environment from Packages variables

SSIS – Create Environment from Packages variables

I created this script to automate some “not funny” tasks with our SSIS Catalog. If you have several SSIS projects configured in project mode (with a project.params file), when deploy them on your servers you unfortunately have to manually create the different environments.

After deploying SSIS packages, you can run the following query and use the generated SQL code.

the generated SQL script:

  • Create the different environment (Based on the name of the projects)
  • Create variables with default values (value available in packages)
  • Assigning Environments to Projects
  • Assign the environment variables to the project variables.

The following code is not clean, but it does the work! 🙂

    Pie Chart is bad!

    Pie Chart is bad!

          During my presentation at Power Saturday in Paris, I wanted to demonstrate to an already very optimistic public that Power BI is a great tool and that modeling is a must-have for their reports. (Data Modelling is not the subject here)
    Power BI a super tool? Yes, and to complete this idea, I added some slides to highlight each piece of the tool.

    One of the slides showed the ease and elegance of the visuals.
    After a quick search on Google images, I copied a screenshot created by a user in Power BI and added it to my presentation.

    In the audience, two friends looked at me with a horrible face. The damage was done, I had slipped the image of a Pie Chart!

    Nothing crazy, isn’t it? But when you know a bit of the history of the Pie Chart, we realize that this visual component is not recommended in the world of Data Viz. This blog post also follows some more or less tense discussions between colleagues.

    I wrote this article for many reasons:

    • This point is already super documented on internet, but some people never read them before. (I provide links at the end)
    • I wanted to convinced myself first, and created a Power BI file to confirm this point. (CFR the following screenshot)

    I was a Pie Chart lover

    I know that its use is not recommended and I try not to use it.
    But yes, I liked it!
    • It’s beautiful!
    • It’s colorful!
    It gives a professional look to our reports!
    • It shows information!
    It gives me some pride to not use tables or matrix. (Yes, I like numbers, not you?)

    I think we are used to seeing this kind of visualization since our earliest childhood in commercial brochures, professional documents.

    The Pie Chart is visual, and help us to return informations quickly. But unfortunately, this information is not always the right one.

    Why?

    To compare groups, we evaluate the difference of the amplitude of the angles while a histogram will request us to compare lengths between them.

    A long description is not needed, I would prefer to show you 2 set of data, both represented in a Pie Chart and histogram.

    Your first look will give you some information, but not as much precise compared to the two histograms below. It’s possible to have quick information, but very hard to have a clear interpretation of them.

    If I didn’t convince you, try to sort each portion from the smallest to the biggest. Try to do the same with the histogram and compare the time elapsed for both.

    SSMS – Query Shortcuts : Feel like a superman developer

    SSMS – Query Shortcuts : Feel like a superman developer

    SSMS Query Shortcut

    Dear BI Developer,

    I’m pretty sure you would be happy to improve your productivity. If not, you should at least read this article to look like a superman (superwoman) developer.

    When I start a new mission, the first thing I do is to set up SSMS (SQL Server Management Studio). And because I’m the kind of guy who acts like a Microsoft BI evangelist (and also for running), I replicate my configuration on my colleague’s machines.

    Example

    In this example – CTRL + 4 – COUNT

    Select statement you want to execute, Press CTRL and 4

    SSMS will give you Nb impacted rows.

    In BI (and not only!), it’s very important to test if we have unexpected behavior with our joins.

    Does our INNER JOIN filter too much data? Or worst, does our join multiply our result set?

    A quick and easy CTRL + 4 will ensure you to respect your grain.

    How to configure Management Studio

    Open Management Studio, Go to Tools > Option…

    Under Environment > Keyboard > Query Shortcuts

    You have a list of existing shortcuts. (I don’t change them, but I neither use them too).
    You should now fill each text box with a query.

    (See image and table below)

    Queries are available on the next section

    Query Shortcuts

    Do not forget to add a space after each query.

    Tips

    CTRL + 3

    1000 First Rows

    SELECT TOP 1000 * FROM

    CTRL + 4

    Nb Rows

    SELECT COUNT(1) AS Nb FROM

    CTRL + 5

    All Rows

    SELECT * FROM

    CTRL + 6

    Describe Table

    EXEC sp_executesql N' SELECT schemas.name ,tables.name ,columns.name ,types.name ,columns.max_length ,columns.is_nullable ,columns.is_identity FROM sys.tables tables INNER JOIN sys.schemas schemas ON schemas.schema_id = tables.schema_id INNER JOIN sys.all_columns columns ON columns.object_id = tables.object_id INNER JOIN sys.types types ON types.system_type_id = columns.system_type_id WHERE UPPER(RTRIM(LTRIM(tables.name))) = UPPER(RTRIM(LTRIM(REPLACE(REPLACE(@objname, '']'', ''''), ''['', '''')))) ORDER BY tables.object_id, columns.column_id',N'@objname nvarchar(776)', @objname =

    Select a table and show a quick description of attributes (Name, Datatype, size, nullable, identity)

    CTRL + 0

    All Running queries

     

    a better SP_Who! List all running queries (Process ID, Status (blocked or running), users, …)

    For more informations check this article

    SELECT SPID = er.session_id ,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END ,ElapsedMS = er.total_elapsed_time ,CPU = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,Executions = ec.execution_count ,CommandType = er.command ,LastWaitType = er.last_wait_type ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,SQLStatement = qt.text ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,DBName = DB_Name(er.database_id) ,StartTime = er.start_time ,Protocol = con.net_transport ,transaction_isolation = CASE ses.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme ,DatetimeSnapshot = GETDATE() FROM sys.dm_exec_requests er LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt OUTER APPLY ( SELECT execution_count = MAX(cp.usecounts) FROM sys.dm_exec_cached_plans cp WHERE cp.plan_handle = er.plan_handle ) ec OUTER APPLY ( SELECT lead_blocker = 1 FROM master.dbo.sysprocesses sp WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND sp.blocked = 0 AND sp.spid = er.session_id ) lb WHERE er.sql_handle IS NOT NULL AND er.session_id != @@SPID ORDER BY er.blocking_session_id DESC, er.logical_reads + er.reads DESC, er.session_id