SSWUG.ORG SQL Server Information at SQLTeam.com Journey to SQL Authority with Pinal Dave | SQL, SQL Server, MySQL, Big Data and NoSQL SQL Server Tutorials and Tips SQL Server Backup DatabaseBackup is the SQL Server Maintenance Solution’s stored procedure for backing up databases. DatabaseBackup is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014. Download Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need. License The SQL Server Maintenance Solution is free. Parameters Databases Select databases. Directory Specify backup root directories, which can be local directories or network shares. DatabaseBackup creates a directory structure with server name, instance name, database name, and backup type under the backup root directory. BackupType Specify the type of backup: full, differential, or transaction log. DatabaseBackup uses the SQL Server BACKUP command: BACKUP DATABASE for the full backup, BACKUP DATABASE WITH DIFFERENTIAL for the differential backup, and BACKUP LOG for the transaction log backup. Verify Verify the backup. CleanupTime Compress Compress the backup. CopyOnly CheckSum
Database object naming conventions (For SQL Server databases, tables, views, triggers, indexes, primary keys, foreign keys and constraints, cursors, stored procedures, user defined functions [UDFs], columns, defaults, variables): Narayana Vyas Kondreddi's NEW!!! Subscribe to my newsletter: There exist so many different naming conventions for database objects, none of them is wrong. It's more of a personal preference of the person who designed the naming convention. However, in an organization, one person (or a group) defines the database naming conventions, standardizes it and others will follow it whether they like it or not. I came up with a naming convention which is a mixture of my own ideas and views of SQL experts like Joe Celko! This article references Microsoft SQL Server databases in some examples, but can be used generically with other RDBMSs like Oracle, Sybase etc. too. Tables represent the instances of an entity. So, name your customer table as 'Customers'. This is a more natural way of naming tables, when compared to approaches which name tables as tblCustomers, tbl_Orders. SL_NewLeads SL_Territories SL_TerritoriesManagers You could name all your tables related to Human resources department as shown below: Cust_CustomerID
Calculating Running Totals By Garth Wells on 7 May 2001 | 25 Comments | Tags: SELECT Roger writes "Is there a way to retrive a field value from the previously read row in order to use it to calculate a field in the current row . . ." For example: day sales cumu_total 1 120 120 2 60 180 3 125 305 4 40 345 In order to calculte the cumulative total I need to know the previous cumulative total (cumulative total could be any other calculation). I did this in MySQL by using variables to temporarily hold values between rows but SQL server doesn't assinging variables in retrieval statements. The answer to the questions is "yes", you can solve this problem with a single SELECT statement. Creating the Test Data I used the code shown below to create the base table and a few thousand rows of test data. The Three Solutions The three different solutions I tested are shown below. Solution 1: Temp Table/Cursor (NoIndex = 2 secs, Index = 2 secs) Solution 2: The "Celko" Solution (NoIndex = 25 secs, Index = 20 secs)
Using Table Valued Functions in SQL Server 2005 to Implement a Spatial Data Library Gyorgy Fekete and Alex Szalay Johns Hopkins University Jim Gray Microsoft (contact author) November 2005 Applies to: Microsoft SQL Server 2005 Summary: This article explains how to add spatial search functions (point-near-point and point in polygon) to Microsoft SQL Server 2005 using C# and table-valued functions. It is possible to use this library to add spatial search to your application without writing any special code. The sample code for this article is included with SQL Server 2005. Contents Introduction Spatial data searches are common in both commercial and scientific applications. For this article, we have added the latitude/longitude (lat/lon) terrestrial sphere (the earth) grid to the astronomer's right ascension/declination (ra/dec) celestial sphere (the sky) grid. Greenwich Latitude-Longitude, called LatLon. Astronomers use arc minutes as their standard distance metric. We believe this approach is generic. Table Valued Functions: The Key Idea Figure 1 key between 'nwsw' and 'nwse'
MSDN Blogs - MSDN Blogs SSIS Junkie : SSIS Nugget: Engine Threads There is a property of each data-flow task called EngineThreads which dictates, quite simply, the number of threads that run in the data-flow pipeline. But what does that mean exactly and how can it affect your data-flow? Well BOL doesn't have much on the subject simply saying "An integer that specifies the number of threads that the data flow task can use during execution". Well that doesn't help much does it? It doesn't tell you what an engine thread actually is so by way of clarification I set about trying to find out more about them. So I think we can make some simple assumptions. You can see that there are 8 completely independant data paths here right? There are only 5 data paths executing in parallel. So then I started to wonder if you can have more than 1 thread when the data paths are not independant. Here I've got a single source adapter and a multicast component that splits that into 8 seperate data paths. So what do we know at this point? So what have we learned here? -Jamie
Muhammad Shujaat Siddiqi: SSIS Multithreading (Parallel execution in SSIS) I have always been curious how I could control the parallelism of different flows in Integration Services Packages. But I was not able to find about it earlier. Microsoft gives us enough ability to execute our packages concurrently. MaxConcurrentExecutables:This specifies the maximum number of execution flows allowed in parallel at any point in the package. The default value is -1. You might be wondering about scenarios where the number of parallel execution flows might be greater than the number of threads specified. EngineThreads:This property is used to incorporate parallelism into data flow tasks. Additionally two properties of Dataflow tasks can be used to specify Temporary storage to any faster disks.
SSIS How to Process Data as Fastest,Parallel , Multithreaded or in Very Efficient Way !!! « (B)usiness (I)ntelligence Mentalist Home > BI Hot, Server, SQL > SSIS How to Process Data as Fastest,Parallel , Multithreaded or in Very Efficient Way !!! I have recently been curious to implement parallelism of different flows in Integration Services Packages in my current project. Where project requirement is to process Data as fast as we can with all parallel, Multithreading or by any other way in very less span of time. After digging in to a lot of things I realize there are n number of ways and n number of post available I can use Multicasting of My Task ComponentsI can use Multithreading in my similar TasksI can use Balance Data Load Distributer where ever requiredData flow pipeline engine can divide a Path (which was called Execution Tree in SSIS 2005) into several Sub Paths – each one can be executed on its own processor/thread. How to use SSIS MaxConcurrentExecutables property ? Let say I have a master package with 10 execute package tasks that I need to execute in parallel. Some More Tips and Link Hope this Helps !!
Daily DBA Monitoring Tasks In order to keep your SQL Server up and running smoothly you need to constantly be performing routine maintenance and monitoring work. If you do not keep a watchful eye over your SQL Server instances performance and stability might suffer. Or worse yet you might not be able to recover your server should you have a total server melt down. In this article I will be discussing some of the daily tasks a DBA should be performing. Additionally I will be providing a few scripts and suggestions to help minimize the amount of time you have to spend performing these daily tasks. Keep in mind every environment is a little different and requires different sets of daily tasks. Verifying Services Availability You need to make sure your critical SQL Server services are available. Ideally I would implement a monitoring tool that will automatically email you when one of your critical services is not working. Monitoring SQL Agent Jobs: Listing 1: List jobs that have failed in the last 24 hours