| |
|
|
|
| | Troubleshooting Failed Reporting Services Subscriptions | Most of the time subscriptions run fine, but what happens when they fail - how you figure out what is going wrong? Profiler might help you figure it out, depending on where in the process the subscription is failing, but it's not the first place to look. It's one of those times when you hope for a cool answer, but in the end it's what you would expect, a log file hidden away on disk that at least gives you a starting point. We'll show you where, and mention an interesting bit of maintenance you should be doing as well. | Andy Warren |
| | Using ClearTrace to Analyze Profiler Results | Profiler is a great tool, but it's easy to get overwhelmed by the volume of results on a busy server. It's also not easy to aggregate the results because of literals passed in as parameters. You can do it, but it's work. Instead, we recommend you try a great free application called ClearTrace that will give you a terrific view of the results in a way that is easy to understand. | Andy Warren |
| | Adding a Custom Subject Line to a Data Driven Subscription | It's a small tip, but it might prove useful one day, showing you the trick to building a custom subject line for each row in the subscription. It's also a very quick refresher on building data driven subscriptions. | Andy Warren |
| | Adding Users and Setting Permissions in Reporting Services | Here's a quick how-to on how to add users to Reporting Services and then how to set permissions on a folder. Did you know that by default if you add someone to the top level home folder they will have access to all the sub folders? We'll show you how to break the default inheritance and talk a little about best practices for managing users and permissions. | Andy Warren |
| | Moving DSN's | DSN's (data source names) are used to store connection string information in either the registry or the file system. When you upgrade to a new server or move a job or process to a different server, you may have to move related DSN's as well. The process isn't hard, just remember that moving the DSN doesn't move any required libraries or client code, you have to track those down and get installed on the new machine as a separate step. | Andy Warren |
| | Perfmon Tips - Loading a Log File, Saving an Image, and Zooming | So you've taken the time to set up a baseline capture using a data collector set, do you know how to view it? Loading it back into the graphical Perfmon UI is a great way to see what performance looked like over a longer period, and it lets you do that by zooming in on an area, and then you can easily save that view as an image - handy for sending to the boss! | Andy Warren |
| | Renaming Views - Part 2 | Did you know that if you rename a view using SSMS you can wind up in a situation that causes a different view to get over written later in the future? That's right, change the name of a view by appending something like '_old' and later on when someone runs sp_refreshview you will change the original view! Unexpected and incredibly bad, watch the lesson to see how it happens. | Andy Warren |
| | Renaming a View - Part 1 | It's easy to create a view and realize after working with it some, or tweaking it's definition, that you've named it badly. Good names are worth having, so you just select the view in SSMS, click F2, edit the name, and you're done, brand new name. Right? Well, you can do that, but you're setting yourself up for a really unexpected problem later on. Today we'll show you why renaming in SSMS is a bad idea, and then in Part 2 we'll show you how it can really cause pain! | Andy Warren |
| | How to Create an ODBC DSN | A DSN (data source name) is a simple way to externalize the information required for an application to connect to a data source. For SQL Server, that means we need to provide the name (or IP address) of the server and credentials, and usually we'll specify the target or default database. Now we have our application pull the connection from the DSN at run time, giving us an easy way to change the name of the server or database or the credentials at any time without rebuilding the application. | Andy Warren |
| | Restoring a Compressed Backup | If you've made a compressed backup using SQL 2008 Enterprise Edition or SQL 2008 R2 Standard Edition you've got it made when it comes to restores - no change in syntax is required to restore from a compressed backup. Not only that, even if you used the Enterprise Edition you can restore the backup to an instance running Standard Edition. But wait, there's more! On most systems the time it takes to restore the compressed backup will be FASTER than from an uncompressed backup. That's as close to a free lunch as we're likely to get in the database world. | Andy Warren |
| | Using Backup Compression in SQL Server | Backup compression may not sound exciting, but it easily reduce the space you need for backups by 50% or more. If you look at the cost of storage that's interesting by itself, and being able to keep two days on disk instead of one is always attractive to a DBA. It's easy to use, effective, but not without a cost of its own in licensing. | Andy Warren |
| | How to Do a Split Backup in SQL Server | A split backup is one where we create more than one backup file instead of the usual one, 'splitting' the total backup across each file. Usually we'll do this because we don't have enough space to create a single large backup file, or because we need to get the backup done faster and we want to leverage the disk IO of using additional drives. It's an easy technique to learn and one you should know. | Andy Warren |
| | Setting A Database to Read Only | You probably won't do this often, but it's a quick and easy way to make sure nothing changes. It's a lot simpler than modifying permissions within the database, and so it's easy to undo the change when it's time to do that. I use this technique when I'm pretty sure a database isn't being used, first setting it to read only and then later changing it to offline. | Andy Warren |
| | Using the KILL Statement | Perhaps it's not named in a politically correct way, but KILL is the command we use in TSQL to end the work on a connection. Interestingly there are spids we cannot kill, and spids that even though we kill may take some time to rollback, and even restarting the service won't make it get done any sooner. We tend to think of KILL in the context of blocking, but it happen in simpler scenarios such as when you want to detach a database and someone has a connection open, even if it's doing no work. | Andy Warren |
| | Bypassing Blocking using NOLOCK | Blocking, like life, happens. Blocking is caused by locking and locks are good. We need locking to give us a transactionally consistent view of the world. But sometimes we need more speed and less consistentency, and for those times using NOLOCK (or it's alias READUNCOMMITTED) are a simple way to bypass the blocking. We'll show you how, just remember that with great power comes great responsibility. | Andy Warren |
| | Adding SQL Agent Operators Using TSQL | This is one of my favorite patterns, setting up two operator accounts so that I be selective about how and how often I get notified when things go wrong. A simple bit of TSQL will set up the agents, a lot faster and accurate than going server to server to server. | Andy Warren |
| | Blocking, SP_Who, and SysProcesses | Today we're taking a look at diagnosing a simple case of blocking by using sp_who and sysprocesses. We'll show you how to create a simple blocking scenario that emulates a long running query, and then we'll "fix" the problem by issuing a KILL statement so that other the blocked queries can run to completion. | Andy Warren |
| | SP_Who & SP_Who2 | In an earlier lesson we looked at the sys.sysprocesses view and how we can use that to see what's occurring on our server. It works, but sometimes it's nice to get a slightly more filtered view and that's where we find value in sp_who and sp_who2. They provide slightly different output but do about the same thing, returning a result set that shows us what's going on and who is doing it, things that I think you'll agree are key questions for any DBA! | Andy Warren |
| | Using the Sysprocesses View | It's a core task for a DBA, knowing how to quickly see who and what is connected to a SQL Server instance. There are many ways and many tools that can do it, but none more straightforward than running a quick query against sysprocesses. You can see what machines are connected, what application they are using, and whether the connection is doing any work or just 'sleeping'. It's not hard to use either, you can start with the basics and then expand your skills as you dig into all the data it provides. | Andy Warren |
| | DBCC InputBuffer - Seeing the Last Executed Command | A really common DBA task is to figure out what a blocking spid is executing that is causing the problem. If you know the spid, DBCC Inputbuffer is a fast and simple technique for looking at that process, and it works at least back to SQL 7! | Andy Warren |
| | Using the TSQL Convert Function | Converting to and from various data types is every day stuff for most of us, things like converting numbers to strings so we can concatenate them. We'll show you a few different examples of using Convert, and we'll look at an unusual result you can get in a couple uncommon situations. Good basic TSQL, stuff it's worth while to master. | Andy Warren |
| | Covering a Query That Includes an XML Column | It's great to type a column as XML to leverage schemas and XML indexes, but right now we can't add an XML column to an index, even as an included column. So what do you do when you have a query that is generating a bookmark lookup because of the XML column? One solution is to stop using XML, not a great solution, and another is the one we demonstrate - creating a computed column that we CAN include in an index. It's not a technique to use casually, but it will remove the bookmark lookup! | Andy Warren |
| | Views, Indexed Views, and NOEXPAND | Views are a nice abstraction layer, letting us re-use a query multiple times in multiple ways. Indexing them is a way to pay for the work of executing that query up front instead of when we query it. It comes with a cost, but the pay off can be big. Enterprise Edition automatically figures indexed views into it's optimization process, but on Standard you have to tell it do that by adding in the NOEXPAND hint to your query. We've got a longer than usual video that tries to show you enough about each of these concepts to show you how they work together. | Andy Warren |
| | Using SET LANGUAGE | You may never need to execute this particular SET statement, but if you do, do you know what it changes? The biggest change is that it changes the default format of dates, including the names and abbreviations for months and days of the week. If you change the language, you're then changing the results of things like CONVERT when you convert a date to a string. It doesn't affect the data (as long as you stored the date as a date of course), but it can definitely affect the display. Usually that's a good thing, but it can break things if you're looking for 'OCT' and get back 'OKT' because the language is set to German! | Andy Warren |
| | CONVERT - Changing a Date to a String | Without a doubt the best practice is to store dates as dates (or something similar such as smalldatetime, date, or time) rather than strings. That works well as we write queries and do date based math, but users usually care more about the way the data looks than all the stuff we do behind the scenes. We'll show you how to use the 'style' parameter to control the formatting when you need to show date values in something other than the default date format. | Andy Warren |
| | Understanding the Two Digit Year Cutoff in SQL Server | By default the two digit year cutoff in SQL Server is set to 2049, which means if you supply a date with a two digit year it uses 2049 as the dividing line to determine if it should prepend '19' or '20' to the year you provided. You can change this, and in some cases you might have to, but relying on it is tricky, it's an instance wide setting that someone might change to fix something else. It's important to know about it, but play it safe - use four digit years. | Andy Warren |
| | Using CONVERT - String Literals to Date Data Types | The CONVERT function has many uses and in this lesson we focus on just one aspect, converting string literals to various date based data types. CONVERT is amazingly versatile, understanding a wide range of string formats that can be converted to dates, and we'll show you how some of these work. It's hard to work with data and not find a time when you'll need to do these types of conversions and if we could offer one tip it would be to always specifiy the four digit year! | Andy Warren |
| | Using CAST to Change Data Types | Converting from one data type to another is a pretty common task for both DBA's and developers. TSQL gives us the CAST function, which allows us to do any conversion that's legal (we can't convert 'Andy' to an int!). We'll show you quite a few different examples and it's worthwhile to spend a few minutes trying it out, especially to see the error you get when you try to do a CAST that fails. | Andy Warren |
| | Using sp_HelpText To View Object Defintions | Want to know what a stored procedure or a view or a function does beyond just what is implied from the name? You browse to find it in Management Studio, you could use the OBJECT_DEFINTION function, or you could use my favorite technique sp_helptext. It's simple to use, just pass in the name of the object and you get back the definition. It's fast, easy to use, easy to remember! | Andy Warren |
| | Indexed Views and the NOEXPAND Hint | In most cases views are expanded at run time to be replace with the underlying definition. But if you have an indexed view, you can force SQL Server to treat the view as if it were a real table, essentially disabling expansion on that view for that query. We'll show you a normal view and then contrast that query plan with one against an indexed view, and then add the NOEXPAND hint to show you the final plan that is possible. Remember that indexed views are a powerful and valuable technique, but they do add overhead, so use only when you have too! | Andy Warren |
| | Creating a Computed Column as a DATE Data Type | Today our lesson is based on a reader question - how do you get a computed column to be of type DATE instead of DATETIME? We'll show you the original implementation and then a solution that uses the CONVERT function. Short and sweet, you might find this idea useful one day. | Andy Warren |
| | Get the Name - Using the OBJECT_NAME Function | Object_Name is one of the functions I use over and over as I work with system views, it's easy to use way to get the name of an object from an object_id, and it's far more concise than a join to sys.sysobjects. We'll show you both techniques in this lesson just in case you need more than just the object name on occcasion. | Andy Warren |
| | UNION and UNION ALL | UNION is an elegant bit of TSQL, allowing you to to combine (stack) result sets together into one. We've put together some examples to show you the difference between UNION and UNION ALL, and we'll look at changing the column names in the first select in order to set the column names of the final result set. | Andy Warren |
| | Get The Definition of a SQL Object Using Object_Definition | The Object_Definition function is easy to use, just pass in an object id and you get back the text of the stored procedure, trigger, function, or check constraint that you need to examine. Don't happen to remember the object id? We'll show you how to combine the Object_ID function with Object_Definition to get the answer fast! | Andy Warren |
| | Using the POWER Function | Need raise a number to a power? You need the POWER function! Easy to use and intuitive, at least with positive powers. Need to raise a number to a negative power? Watch the video so that you understand that the answer you get may not be the one you expect - but we'll show you how to get it right! | Andy Warren |
| | A Quick Look at View Expansion | Views are great tools for security and abstraction, and because they look like tables we tend to think of them as tables. But what really happens when you write a query against a view? SQL Server "expands" the view before the optimization begins, essentially putting the view definition right into your query. Watch the video for a quick example! | Andy Warren |
| | Exploring CEILING, FLOOR, and ROUND | This lesson is a basic introduction to the TSQL functions that you can use for rounding numeric values. ROUND provides the traditional result we learned in school (with one odd option you might not expect), and CEILING and FLOOR allow us to easily round up or down without regard to whether a value is over or under the ".5" rounding point. Good basic stuff, but stuff you'll find occasion to use quite a bit. | Andy Warren |
| | Using the RAND Function | Need to generate a random number? The RAND function is the TSQL tool for the job! What about needing to generate the same random number each time for a given seed? RAND can do that too. We'll do a short demo of both techniques so that if the boss calls for a random number - perhaps to decide how much of a raise you'll get - you'll be ready to go! | Andy Warren |
| | Using the TSQL Math Operators | TSQL has built in support for multiplication, division, addition, subtraction, and modulus. We'll show you how all those work and what happens with various data types, and we'll look at the order in which the operators are evaluated. Great lesson to make sure you know your options and get the correct answers when you do math in TSQL. | Andy Warren |
| | Options for Displaying Query Results in SSMS | Most of use the default setting of displaying the results of a query in a grid and it works well. Do you know how to get the data from there into Excel? Or have you experimented with the options to display as text, to automatically display results in a different tab, or even send the results directly to a file? It's worth 10 minutes of experimenting to learn what all the options are and find out not just which ones work best for you on a day to day basis, but which ones you might use in different situations. | Andy Warren |
| | Using the USE Statement | The USE statement allows us to easily change to a different database on a server. If you currently change databases by clicking the drop down list in SSMS, behind the scenes you're executing a USE statement. We'll show you a technique for making sure you're in the database you expect and if not, how to change to the correct database. We'll also show you how to change (or not) the default database for a login. | Andy Warren |
| | Installing the Adventureworks 2008R2 Sample Databases | Many of the demos we do here on SQLShare use the Adventureworks sample database and for beginners it's not intuitive about where to find them. Back 'in the old days' of SQL 2000 we had Northwind and Pubs installed by default, and then most admins would delete them to save space. Since SQL 2005 it's required a separate install after downloading the installer from CodePlex. The install itself isn't hard, and we'll walk you through to show you what to expect. Remember to get the version that matches your install, and to read the install notes before starting! | Andy Warren |
| | Using the STUFF Function | STUFF is an unusual function. It feels a bit like REPLACE (and you can certainly use it that way), but it's really designed to do something different - push x characters into a string based on an index and length. Andy was surprised to realize it was the only string function he had never used to solve a problem. Not useful, or just a gap in his game? Watch the video to learn more and if you've found a great scenario for using it, post it in the comments! | Andy Warren |
| | Getting Good Performance using SOUNDEX | This a follow up to our lesson on using the SOUNDEX and DIFFERENCE functions. It's easy to see opportunities to do searches on 'sounds like', but if you start running SOUNDEX on a big table without some preparation you're probably not going to like the performance. We'll show you one way to deal with it, and along the way revisit computed columns, indexing, and reading query plans. This isn't meant to teach all the concepts in detail, just show you one way to solve a specific performance problem. | Andy Warren |
| | Using SOUNDEX and DIFFERENCE | It's easy enough to search for exact matches, or even something close to matching using various wild card combinations. But what if you need to find names that sound like Andy but are spelled differently? SOUNDEX is one way to do it and it's built-in. Used along with the DIFFERENCE function you can identify items that are 'close' matches and decide how close a match you need. Using SOUNDEX is a great way to add power to the search options you provide to your end users. | Andy Warren |
| | Using the STR Function | STR (pronounced "string") is a built-in TSQL function to convert numbers to strings or text. It has some interesting features you'll want to see, and we'll contrast it with using CONVERT. | Andy Warren |
| | Using the QUOTENAME Function | QUOTENAME does much what you would expect, wraps some text with quotes. Or single quotes. Or brackets! We'll explore QUOTENAME and wrap up with a reminder that we can always do old fashioned string concatenation to get the same result. One of the many TSQL string functions, make sure you know what it can - and can't - do. | Andy Warren |
| | Using the SPACE and REPLICATE String Functions | If you need to put more than a space or two into a column or a variable SPACE is a lot more elegant (and readable) than just embedding a whole line of spaces. REPLICATE is the big brother to SPACE, able to generate copies of any character needed. Both have their quirks though, and we'll explore at least some of those in our lesson today. | Andy Warren |
| | Using ASCII, CHAR, UNICODE, and NCHAR | ASCII and CHAR allow you to convert a character to a number and a number to a character respectively. UNICODE and NCHAR are the unicode (nchar/nvarchar) equivalents. Probably among the lesser used of the string functions, but easy to master and you will find a use for them here and there - one example we demonstrate is using CHAR to embed a 'non-printable' character in a string. | Andy Warren |
| | String Handling Functions Part 7 | Reverse! I've used this as an interview questions many times, challenging a candidate to reverse a string using code. It's a good way to test basic string skills. It's posible to do it with no code in TSQL, which doesn't make for much of a test (assuming you know the function exists). But is it useful beyond that? The answer is a definite yes, you'll find times when having a built-in REVERSE can enable some very elegant solutions to string problems. | Andy Warren |