#sqlblog

[thinkcloudly]

Read our latest blog for the best practices for Azure in 2022. To Read: Link in Bio #blogpost #newblog #sqlblog #azure #cloudcomputing #cloudcomputingcourses #thinkcloudly #thinkcloudlyblog #onlinecourse #elearning

[gregdodd]

CTEs are not Staging Tables

CTEs are not Staging Tables #sqlserver #sqlblog

If you have a complex query, a CTE (Common Table Expression) can be a great way to break down the query into individual parts. What is often overlooked by developers is that CTEs can cause significant performance problems when used incorrectly.

A common pattern I have seen is joining to a CTE multiple times. This is done in the belief that the CTE will run once and stage the data, and then we…

[loganmerazzi]

Gap (pulo) no Identity, como evitar?

Gap (pulo) no Identity, como evitar? #SQLServer #SQLBlog

Desde o SQL Server 2012, o produto sofreu uma alteração no controle dos campos IDENTITY onde, logo na inicialização da instância, ele coloca em cache aproximadamente 1000 registros para agilizar o processo. Ao fazer isso, o identity é “pré-usado” internamente e o SQL se encarrega de entregar os próximos valores.

O ‘problema’ é que, ao reinicializar a instância, o cache é perdido e ele cria um gap…

[sqljoker]

Tweeted

🤓 - https://t.co/RWps5dfH94 - A new home for my SQLBlog content... https://t.co/lAwfg5MvRI #SqlServer #Sql

— SQL Joker (@sql_joker) January 19, 2018

[sqlmashup-blog]

PostgreSQL DBA

no more blogging at this site as I begin learning the ropes as a new postgresql dba. cheers!

[gregdodd]

Rollup and Cube Part 2

New Blog Post: Rollup and Cube Part 2 #sqlserver #sqlblog

Previously in Part 1, we looked at the ROLLUP keyword that can be applied to SQL Select queries. We are now going to look at the similar, but different CUBE keyword.

As we saw last time, the ROLLUP keyword would sum each part of the GROUP BY and give you a running total. The CUBE keyword is similar, in that it gives running totals, but it gives you a running total across all of the different…

[gregdodd]

Rollup and Cube Part 1

New Blog Post: Rollup and Cube Part 1 #sqlserver #sqlblog

I have recently come across these 2 keywords: ROLLUP and CUBE. These keywords allow you to get running totals when you group by some data. In this post we’ll have a look at how ROLLUP works, and in the next post we’ll see how CUBE works, and then in the final post we’ll look at how you might use this in the real world.

I have created a table with Population data for US Counties, and now I’m…

[gregdodd]

Runnable, Running and Suspended

New blog post: Runnable, Running and Suspended #sqlserver #sqlblog

When you send a query to SQL to be executed, the query is compiled and then run. When you have 1 person querying the database, everything runs fine, but when you ramp that up to multiple users, you start to fight for resources. One of those resources is CPU.

To manage CPU time, SQL Server tracks tasks in 3 basic states – Runnable, Running and Suspended.

Runnable:This is the default state of…

[gregdodd]

Sysadmin in SSRS doesn't have access to view reports

New Blog Post: Sysadmin in SSRS doesn't have access to view reports. #sqlserver #sqlblog

Short and simple post this week. When setting up a Reporting Services Server this week, I added a user as a SysAdmin as they should have full access to the server to maintain it in the future. After setting up the user they tried to click in to the end of month reports folder…and couldn’t get in.

Turns out that being a Sys Admin doesn’t give you access to report folders, you need to add access on…

[gregdodd]

Rollup and Cube Part 3

New Blog Post: Rollup and Cube Part 3 #sqlserver #sqlblog

In the previous 2 posts we have looked at how Rollup and Cube work. This time we’re going to look at how you might implement these in the real world.

I don’t think ROLLUP has a great use in the real world (feel free to comment below if you have a real use for it). The best I can see is that you have SQL Server emailing the results of a query to someone and they want subtotals. Adding totals…

[sqljoker]

Tweeted

The latest #Microsoft SQL Server News! https://t.co/Yhys1vk0lj Thanks to @LatestSTNews @DellEMCServers @nielsberglund #sqlserver #sqlblog

— SQL Joker (@sql_joker) January 10, 2018

[sqljoker]

Tweeted

#PowerShell Using .Contains with https://t.co/cmXoMx6F3p.DataRow #sqlblog #sqlserver https://t.co/lhPQ9scQtX http://pic.twitter.com/DaFpDMvpCj

— Shane O'Neill (@SOZDBA) August 18, 2017

[sqljoker]

Tweeted

🤓 - https://t.co/RWps5dfH94 - SQLblog is Moving to a New Hosting Provider https://t.co/XoHB48rfI7 #SQLblog #SqlServer #Sql

— SQL Joker (@sql_joker) July 14, 2017

[sqlmashup-blog]

T-SQL Tuesday #23 – Joins #tsql2sday

T-SQL Tuesday

The host for this week early blog party is Stuart Ainsworth. You can click on the image above to read the invitation and about the topic of JOINS. You can also find there within the comments, links to the posts of all participants. You could also just wait for the round-up to be posted. For a round-up of round-ups please see this page by the blog party godfather Adam Machanic, A Year of Tuesdays: T-SQL Tuesday Meta-Roundup

Joining

If you have experience with joining the military then thank you sir or ma’am but as a SQL Server DBA you are sure to have experience with joining tables using Transact-SQL. My first blog post was on this exact same subject and this post will be a condensed version of it, SQL Server Joins using Playing Cards There are three primary JOIN operators the Query Optimizer will pick from: Nested Loops, Merge Join and Hash Match.

  Nested-Loops

Imagine you were asked to find all the Aces in a deck of cards. How would you accomplish that? You would probably flip them all over and scan across them looking for the Aces. You would likely do the same if you were told that this would repeat for three, four or five different ranks. What if you were told it would be for eleven, twelve or even all thirteen different ranks?

  Hash Match

If you were going to match many different ranks then you would probably decide scanning would be too repetitive. Instead you might break the deck into a pile for each rank. The time it took to reorganize the cards will be made-up by not having to look at every single card when another rank is asked for. What if you were told to match all fifty-two cards one-to-one between two decks?

  Merge Join

We need to take a small leap of faith to continue using this card matching analogy. For now image that you have the ability to perform a perfect shuffle, where exactly one card from each the left and the right stacks fall together through-out the entire shuffle. If you had to find all fifty-two card matches you would likely go with a Hash Match but now that you are bestowed with the gift of Perfect Shuffle you have a better option. You quickly sort each deck then perform a perfect shuffle and BAM! The analogy is more appreciable when the cards are already sorted or the number of them is much higher than fifty-two.

  See Also

Physical Join Operators in SQL Server – Nested Loops - SQL Server Performance

Physical Join Operators in SQL Server – Hash Operator - SQL Server Performance

Physical Join Operators in SQL Server – Merge Operator - SQL Server Performance

Nested Loops Join - Craig Freedman's SQL Server Blog - Site Home - MSDN Blogs

Hash Join - Craig Freedman's SQL Server Blog - Site Home - MSDN Blogs

Merge Join - Craig Freedman's SQL Server Blog - Site Home - MSDN Blogs

  People

Stuart Ainsworth

Adam Machanic

Ami Levin

Craig Freedman

[sqlmashup-blog]

T-SQL Tuesday #22 – Data Presentation #tsql2sday

T-SQL Tuesday

Robert Pearl has heeded the call of hosting the T-SQL Tuesday blog party this month. Please click on the #tsql2day image above to learn more about why the topic is "data-presentation". If you want to read more party-style blog posts then also check out the summary by Adam Machanic called A Year of Tuesdays: T-SQL Tuesday Meta-Roundup.

Presents

Who presents the data to your users? There are occasions when the DBA will be asked to write T-SQL for Manipulating Result Sets. Mr. Pearl mentioned having experience with Using Common Table Expressions in the invitation. I had a similar experience with Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS. Who should present data to the users? Although this blog party has T-SQL in the title this post will be more theoretical than practical.

Tiers

There are always exceptions to any rule but as a general rule I subscribe to the N-Tier / Multi-tier architecting of applications. There are entire books about the benefits but for the sake of a short post I would like to share just two benefits of this architecture for a DBA. If you plan to stay for the soapboxing then please take a minute and look over this diagram by Damon Armstrong on Multi-tier architecture from his post .NET Application Architecture: the Data Access Layer.

Tools

Transact-SQL and SQL Server are used to scatter and gather data. CLR and Business Objects are used for control flow logic. Windows Presentation Foundation and Forms/Controls are used for user interface presentation. Each is a great tool for performing their intended task. You have NEVER used a crescent wrench as a hammer right? In the same tongue in cheek tone, you such NEVER use T-SQL to figure out the next user product suggestion and NEVER use T-SQL to display a dollar sign on the right side of a number.

Really?

Let us pretend that you inherited a stored procedure that does format the output so that there is a dollar sign displayed on the right side of a number. Who do you suppose would be tasked if the user wanted a different format?  (Euros, two decimal places, in millions, negatives in parenthesis)  Each of these has the potential to become a major time suck and also cause some wicked stored procedure sprawl. It has happened, it is still happening and will probably continue to happen but hopefully you are better informed for avoiding the problem.

People

Robert Pearl

Adam Machanic

Damon Armstrong

Welcome to the show

[sqlmashup-blog]

#sqlserver meta-URLs

Someone sent a request effectively asking for a whitelist of URLs for SQL Server DBAs.

  Instead of a long list of all URLs here is a short list of meta-URLs.

SQL Server Blog list: PASS - The Professional Association for SQL Server > Community > Blog Directory

SQL Server Community list: SQL Server - Related Sites

SQL Server Vendor list: SQL Server Product Categories

Q&A list: List of question and answer websites - Wikipedia, the free encyclopedia

  Do you see anything missing? Can you recommend other meta-URLs?