DevJef's Mumbo-Jumbo

«There's a bit of SQL in all of us»


News

 

If you work with SQL Server you will eventually encounter a JOIN statement. This JOIN statement can be stated as an ordinary JOIN, or as a sub select. In most cases I try to use JOINS (even though it’s just for readability), but in some cases it’s quicker to do it in a sub select (for example with production code, in which you don’t want to change the output but filter output). Curious about the performance differences between the two, I tried to create a test. I created 2 tables with country information: ......

 

In order to refresh your OLAP Cubes (SQL Server Analysis Services), you need to create XMLA files. These XMLA files can be used in a SQL Server Agent Job, so that the Cubes are refreshed and re-processed. To accomplish this, you need to follow the next steps: 1) Deploy your Cube to your SQL Server 2) Right click on the database, and choose “Script database as” –> “Alter To” 3) Add the following node to the top of the XML: <Batch xmlns="http://schemas.micro... ......

 

Every ones in a while you want to know the record count of all objects in your database. If you have 10 objects or less in your database, you can choose to type out the statements yourself, but once it’s more then that it gets quite annoying. Because I needed this for a production issue (make sure that all objects contain records after import) I wrote a cursor to query all the database objects, and count the content of it: USE Sandbox --=========================... ......

 

For (almost) all developers, there’s no greater feeling, then knowing your source code is (relatively) safe. The same counts for T-SQL script. Especially if you know that you need the same code next year (argh). So first thing I did after the initial release of scripts, was looking for a way to check-in T-SQL from SQL Server Management Studio (SSMS). So a colleague of mine, Dries (Blog | Twitter), dropped by with a solution: TFS MSSCCI Provider 2010 from the Visual Studio Gallery. It’s a free plugin ......

 

When you work with SQL Server, you regularly want to know when an object is last executed, last modified, etc.. Well, at least I do! In most cases you need to use the sys.objects to obtain this information, join it with other sys tables, search for your information, and so on. But there is an easier way. The script below will get you the latest information of any object in your database. USE SandBox SELECT DB_NAME(Txt.dbid) AS DatabaseName, OBJECT_NAME(Txt.objectid, Txt.dbid) AS ProcedureName, Objects.create_date ......

 

If you write a SQL query, it’s important to know when certain pieces of you query are executed. For example, it’s possible that some statement in your query interferes with another part of your query, because of the execution time of a specific piece. The order of execution is: 1. FROM Clause 2. JOIN / APPY / PIVOT / UNPIVOT Clause 3. WHERE Clause 4. GROUP BY Clause 5. CUBE / ROLLUP Clause 6. HAVING Clause 5. SELECT Clause 6. DISTINCT Clause 7. TOP Clause 8. ORDER BY Clause So in many cases this ......

 

In SQL Server we know 3 kinds of temporary tables: Local Temp Tables, Global Temp Tables and Table Variables. In this blog I will try to explain the differences, and tell you how to use it. Local Temp Tables The local temp table is the most commonly used temp table. This temp tables is created with the following statement: CREATE TABLE #TempTable (ID INT IDENTITY(1,1) NOT NULL, Description VARCHAR(10) NULL) The table that the script above created is a temporary table that is stored on disk. To be ......

 

In order to test Performance in SQL Server, normally you would take a look at how may I/O or how many cycles a statement takes to complete. To make it less complex, you can take a look at how long a statements takes, just by looking at the execution-time. In most cases, network latency, SQL Server hardware and workstation performance are also included in this result (time to create result set, and time needed to send across the internet or internal network). And if a statement takes les then a second ......

 

How often does it happen: you write an application that uses a SQL Database as source, and when you want to fetch data you get a time-out. Damn! But when you need to get a time-out (for example, if you want to test an exception), you don’t get one. In SQL Server you can create your own time-outs if you want. One of the simplest ways to generate a time-out, is to lock an object, and running a query against the object in another session. You can lock an object with a simple query: SELECT * FROM Company ......