There are some folks just starting out to SQL Server, maybe they come from another flavor of database or perhaps they have no history with SQL at all. The terminology we use to talk about the work we do can get a little confusing, and I was lost when I began learning about schema structures. This is my “Explain it like I’m five – on how join statements are semantically structured.”. This is geared to new users who are in the first 1-6 months of learning SQL Server. Continue reading “ELI5 how are join statements semantically structured?”
How to stop the SQL Scheduler with T-SQL
How to learn and implement Change Tracking in SQL Server
Why would you pick Change Tracking as a technology to use?
This allows you to detect changes in a lightweight manner via T-SQL functions to extract information about DML changes to Change Tracking enabled tables. Change Data Capture is more about auditing or creating a historical view utilizing the Transaction Log and Temporal Tables are the next step up from there which became available in 2016 versions of SQL Server. Change Tracking is primarily used for finding only things that have changed. Not necessarily why, how, or who changed it, but what has changed and what it is now. Continue reading “How to learn and implement Change Tracking in SQL Server”
T-SQL Tuesday #108 Learning non SQL Server technologies
This month’s T-SQL Tuesday is hosted by Malathi Mahadevan. Our topic comes during the PASS Summit, a SQL event filled with learning, networking, and the #SQL Family! So it is only fitting that our topic is regarding learning and education. Thank you Malathi for hosting this month and for the great topic!
The topic is to pick one thing that is not SQL Server that we want to or already know. How do we or did we learn this skill and how do we add it to our resume? Continue reading “T-SQL Tuesday #108 Learning non SQL Server technologies”
How do I dynamically populate a dropdown list for users to filter a report in SSRS?
I was helping someone who had an interesting data set. Their tables were coming from a vendor and they had to report off of those tables. The problem was that the tables were all named and logically split by MMYYYY. This meant that if they wanted to query data between a date range, they would need to maintain views daily or get creative in SSRS.
Now this option can be used for many things, you just need to tweak the parameters a little. I’m showing you conceptually how to use the template to execute a dynamic SQL Script that populate a data set from a user selecting drop downs in a report. Continue reading “How do I dynamically populate a dropdown list for users to filter a report in SSRS?”
How to keep track of how many records were truncated?
Someone had an issue where a third party app would truncate a table for maintenance and they had no control over the truncate and when it happened. The issue was that they needed to know how many records were in the table before the truncate occurred. Continue reading “How to keep track of how many records were truncated?”
What is the optimal way to order a clustered index?
Someone recently was asking me about indexes and how the order of the columns affected the index.
I have a table, and I want to create a clustered index on it. SSMS lets me choose the order of the columns when I make a clustered index. Logic would dictate that this must have some effect or else it wouldn’t be offered as an option. What factors are considered when determining the optimal order?
I read on Stack Overflow I think, someone said that the best way is to start with the column that has the most distinct values, and then decrease from there, but then a guy below him said he’s wrong. I haven’t been able to find a good source of into on this. Any help appreciated!
Can you guess my answer? Continue reading “What is the optimal way to order a clustered index?”
T-SQL Tuesday #106 Trigger headaches or happiness?
Triggers are both a useful tool with a specific niche… and the devil. Thank you Steve Jones for hosting this week’s topic on triggers!
When used appropriately, it’s a great skill to have in your repertoire. When used inappropriately, you will probably ruin someone’s day. Continue reading “T-SQL Tuesday #106 Trigger headaches or happiness?”
T-SQL Tuesday #105 The Wall
In my career, there are three categories of brick walls I have seen that stand out. The most common situations are; when you are unable to figure something out but is possible to resolve, when you become blocked because of a person, and finally, when you become blocked because of policy. I’ve been fortunate to work with amazing folks in the past and I can’t say there was a single person trying to block me from achieving a task because of a vendetta or even really ideologies. I would like to consider myself a reasonable person however, so I can typically find a middle ground. At the end of the day, does the solution you present achieve the goals of the organization? Continue reading “T-SQL Tuesday #105 The Wall”
How I resolved “Recovery_Pending State” which occurred while moving SQL Server files
I ran into an issue lately while moving some files around. “Recovery Pending State” in SQL Server is what I kept bumping my head against.
My goal was to move my files on my desktop to a new drive since I received a replacement. TempDB needed to be altered for the location but I didn’t need to move those files since SQL Server re-creates them. (You can’t back them up either!) Continue reading “How I resolved “Recovery_Pending State” which occurred while moving SQL Server files”