Are you managing a lot of SQL Server instances? I was setting up a standardized set of SQL Server Alerts on a number of servers and soon realized this was something that could be automated by using a script.
In SQL Server Management Studio, it is possible to execute a multi server query against several servers at the same time. The description from TechNet says most of it:
This topic describes how to query multiple servers at the same time in SQL Server 2012, by creating a local server group, or a Central Management Server and one or more server groups, and one or more registered servers within the groups, and then querying the complete group. The results that are returned by the query can be combined into a single results pane, or can be returned in separate results panes. The results set can include additional columns for the server name and the login that is used by the query on each server. Central Management Servers and subordinate servers can be registered by using only Windows Authentication. Servers in local server groups can be registered by using Windows Authentication or SQL Server Authentication.
Here I’ll show how to create a multi server query from a group under registered servers.
When SQL Server evaluates if an index should be used to retrieve data in a query, selectivity is one of the most important factors. Of course, given that the index column matches the filter or join columns.
If a column only has one or very few unique values, index selectivity is low. True/false columns and gender is common examples.
If there are only unique values in a column, index selectivity is high. Primary keys meets these requirements as they are always unique.
SQL Server favors indexes that are built on columns with high selectivity, since this may return fewer rows and therefore at a lower cost.
If the index has multiple columns, it is preferable (if possible) that the columns are in the order of their selectivity (most selective first).
So how is selectivity computed?
When you submit a query to SQL Server – before the query is executed – SQL Server tries to optimize the way data is retrieved from the tables and indexes to create an execution plan that is as effective as possible. One of the decisions the optimizer in SQL Server has to make is which of the join methods (if any) to use.
There are three join methods: Nested Loops Join, Hash Join and Merge Join.
They all have their relative strengths and weaknesses, which will be covered in this blog post.
Windows environment variables are a great way to store server specific information that may be accessed from different systems on the server. Connection strings, folder paths and ip addresses are common examples of what may be suitable to store as environment variables on the server.
A XML document is hierarchical. A value gets meaning from its position in the document. Nodes can be nested so that a parent node can contain children nodes. This way the child nodes inherits all the values from the parent.
In a relational database the same thing can be achieved, but by using a different technology. Here we use key values in separate tables instead. Information from the parent or child can be retrieved by using relations between tables.
XML is commonly used in applications to store information about objects, and a relational database is often used to persist that information.
So how can a hierarchical XML document be inserted to relational tables? In this blog post I will show one possible technique, using only T-SQL and XQuery. In the example the tables will have their own internal primary keys defined which will add to the complexity, but it is a rather common scenario.
QlikView is a data analysis tool from QlikTech. They call it Business Discovery, or self-service BI.
One of the strength of QlikView is that it is quick to set up and get started with QlikView in a proof of concept.
Here I’ll show how to load data from AdventureWorks2012 into QlikView and how to create a simple interactive chart in QlikView.
Need to get data from Excel to SQL Server?
Normally this can be achieved by using SSIS. Excel can be used as a source and/or target in a Data flow. But there may be situations where you need more granular control over which data gets imported to SQL Server.
Perhaps there is a process where a user is involved in deciding which data gets inserted to the database, and the import may be initiated be the user. Or maybe some advanced calculations needs to be made to decide which values get inserted.
Anyway, using VBA (Visual Basic for Applications) in Excel macro, it is rather simple to open a connection to a SQL Server database, generate the statements that will select values from the Excel cells and import them to a table. This blog post will show a simple example of how to export data from Excel to SQL Server.
I am using Excel 2013 and SQL Server 2012 in the example.
Ever needed to generate insert statements for the data in a table? This feature is actually included in SQL Server Management Studio (from version 2008), but is somewhat hidden.
This guide will show how to script schema and data to a file.
This system stored procedure returns information on windows accounts or groups in SQL Server. One of the more useful features of this procedure is that it’ll show any group membership that allows access to the account.