background preloader

SSWUG.ORG

SSWUG.ORG

SQL Server Tutorials and Tips grid computing Main » TERM » G » A form of networking. Unlike conventional networks that focus on communication among devices, grid computing harnesses unused processing cycles of all computers in a network for solving problems too intensive for any stand-alone machine. A well-known grid computing project is the SETI (Search for Extraterrestrial Intelligence) @Home project, in which PC users worldwide donate unused processor cycles to help the search for signs of extraterrestrial life by analyzing signals coming from outer space. Grid computing does require special software that is unique to the computing project for which the grid is being used.

SQL Server Journey with SQLAuthority 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! 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. If your database deals with different logical functions and you want to group your tables according to the logical group they belong to, it won't hurt prefixing your table name with a two or three character prefix that can identify the group. SL_NewLeads SL_Territories SL_TerritoriesManagers

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). 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) SELECT DayCount, Sales, Sales+COALESCE((SELECT SUM(Sales) FROM Sales b WHERE b.DayCount < a.DayCount),0) AS RunningTotal FROM Sales a ORDER BY DayCount

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. 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 The key concept of relational algebra is that every relational operator consumes one or more relations and produces an output relation. Figure 1

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". 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? On this data-flow I had EngineThreads=2. Now OK, there's alot of yellow boxes on here but the important thing to look at is the rowcount annotations. 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. There are a few properties of package (Just right click control flow designer and select Properties). 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. This threading optimization promises to have many more enhancements on performance in other parts of SSIS such as packages with deep parallel execution paths. Some More Tips and Link

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. 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. It is especially important to verify your services are running after OS patches or Service Packs have been installed. Monitoring SQL Agent Jobs: Mostly likely you are running at least one nightly SQL Agent job on each SQL Server instance you manage. Listing 1: List jobs that have failed in the last 24 hours Monitoring Database Backups

T-SQL PIVOT Statement - Pivoting (Crosstab) Data From a Database Table - T-SQL (Transact SQL) Tutorials Introduction The PIVOT statement is used for changing rows into columns in a SQL Query (Crosstab). The PIVOT Statement is generally written in this form: SELECT columns FROM table PIVOT ( Aggregate Function(Measure Column) FOR Pivot Column IN ([Pivot Column Values]) ) AS Alias Note: You must use brackets around each of the Pivot Column Values Beginning Example - Example #1 Download This Script This example will use the following table: SELECT Country, Variable, VariableValue FROM #temp123 Here is a resultset from the above SELECT query: Now we can use the PIVOT Keyword to create a crosstab result. SELECT * FROM #temp123 PIVOT ( SUM(VaribleValue) FOR [Variable] IN ([Sales],[Expenses],[Taxes],[Profit]) ) AS p Here is a resultset from the above PIVOT query: If for the above example, we just want the Sales and Expenses columns for each country, then we can use this query: Another Example - Example #2 Now we can use the PIVOT Keyword to create a crosstab result. DECLARE @query VARCHAR(8000)

Five cloud-computing takeover stocks By Nigam Arora In the world of information technology, the megatrend of cloud computing is in its infancy. Cloud computing entails providing software as service. Cloud computing stocks are very expensive, face stiff competition and are subject to rapid technological changes. However, these stocks are being taken over at a rapid clip. For example, Oracle /quotes/zigman/19452757/delayed/quotes/nls/orcl ORCL +0.34% just announced that it will buy Taleo Corporation . Previously SAP /quotes/zigman/126928/delayed/quotes/nls/sap SAP +1.17% announced purchase of SuccessFactors at P/S of about 11.5. Oracle and SAP are the two big software behemoths. Here are five cloud software application stocks that are ripe for a takeover. Salesforce.com /quotes/zigman/338061/delayed/quotes/nls/crm CRM +0.20% Salesforce.com is the leader in customer relationship management software as a service (SaaS). Salesforce.com was founded in 1999 by Marc Benioff, Frank Dominguez, Dave Moellenhoff and Parker Harris.

Related: