Geeks With Blogs
DevJef's Mumbo-Jumbo «There's a bit of SQL in all of us» SQL Server
Join vs Sub Query
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: ......

Posted On Tuesday, November 1, 2011 2:26 PM

Create XMLA file for OLAP Cubes
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... ......

Posted On Tuesday, October 25, 2011 2:13 PM

Count records in all database tables
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 --=========================... ......

Posted On Friday, October 21, 2011 1:28 PM

SQL Server source control
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 ......

Posted On Thursday, October 20, 2011 10:30 AM

Extended properties of database objects
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 ......

Posted On Wednesday, October 19, 2011 8:59 AM

Query order of execution
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 ......

Posted On Saturday, October 15, 2011 9:36 PM

SQL Server Temp Tables
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 ......

Posted On Saturday, October 8, 2011 4:16 PM

Quick performance test in SQL Server
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 ......

Posted On Wednesday, September 28, 2011 2:33 PM

Time-outs and locks on objects
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 ......

Posted On Saturday, September 24, 2011 3:36 PM

Copyright © DevJef | Powered by: | Join free