I think one of the best things I took away from college was that I learned how I learn. I still have a love of math, but I learned how I learn best… Reading. I know that’s not for everyone, but the start of all exploration I do starts with my nose in a book or in a blog post. So…. I try to make time to read in my daily life, and by proxy learn....
You ever ask yourself: “Hey self, who updated this row last?” or “When was this row updated last?” Well bad news/good news. Bad news. SQL Server doesn’t store this by default. Good news. You can setup logic to store it anyways. The way I store this is when I build a table I add a LastUpdated datetime column and an UpdatedBy varchar(70) column. I then set these so they have a default constraint of GETDATE() for LastUpdated and SYSTEM_USER for UpdatedBy....
Archiving data that is ready to be archived will help you get results faster. It’s one of those things that improves every aspect of the database engine. Less data to look at on storage. Less data to bring into memory. Less data to run a calculation on for CPU. Less data to send over the network to the client. Less data to keep stats on for plans. Every resource SQL Server relies on for generating a result set is improved when there’s less data to evaluate....
A bit tongue in cheek and light-hearted this time. SQL Server can sort. It doesn’t generally do it well. You pay a whole lot per SQL Server CPU. Why should we spend a lot of money to have it do something it does poorly? When possible you will get performance gains from sorting in app, and make all the other database users happier.
Stored procedures are T-SQL that is compiled for later execution. They are able to accept parameters (these parameters can even be table types that you pass a table variable (see T-SQL Tip 16)). One of the benefits of stored procedures is everything that uses the SP does the same work, the same way. More likely to have metrics, and plan reuse. Since they accept parameters on input (and output), these parameters in execution plans get parameterized....