|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - Too Much Data |
|||||||||||||||||
Many data professionals these days feel like they are working with too much data. Queries run slow, storage is constantly a concern, and our hardware is outgrown on a regular basis by the sheer amount of data that we need to manage. It's a good thing that "Big Data" is in the news since it focuses more attention on the issues of data management and growth in our systems. Some companies are taking the idea of managing their data more seriously. Boeing and Nike are talked about in this piece about coping with large amounts of data. With the vast amount of growth in the data stores these two companies work with, they had to find ways to ensure that business people could effectively find the information they needed in a sea of data. It's an interesting read and one that you might pass on to your management. The solutions aren't new and innovative; they're back to basics ideas. Have a central warehouse, use a data strategy that includes strong models and enforces data quality. Integrate your systems to allow a better view of your entire enterprise. These are the ideas that many data professionals preach, but are rarely found in many companies. Sometimes a new perspective helps, and articles like the one above can help you convince managers of the need to raise your data quality and enforce some standards. » Join the debate, and respond to today's editorial on the forums The Voice of the DBA PodcastsWe publish three versions of the podcast each day for you to enjoy.
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
You can also follow Steve Jones on Twitter: » To submit an article, rant or editorial, log in to the Contribution Center |
|||||||||||||||||
|
|
|||||||||||||||||
Question of the Day |
|||||||||||||||||
|
Today's Question: In the code below what will be the result set? The first result gives the values of @trancount1 and @trancount2 in a single row. The second result gives the data from #temp
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
select 1 as a,2 as b into #temp
BEGIN TRAN tran2
DECLARE @a int=3,@trancount1 int,@trancount2 int
WHILE(@a>0)
BEGIN
BEGIN TRY
BEGIN TRAN
declare @tempvar table (a int,b int)
insert into #temp
output inserted.a,inserted.b into @tempvar
select 7 as a,8 as b
union all
select 5 as a,6 as b
raiserror('test',16,0)
COMMIT TRAN
END TRY
BEGIN CATCH
set @trancount1=@@trancount
If(@@trancount>0)
ROLLBACK TRAN
END CATCH
SELECT @a=@a-1
END
INSERT INTO #temp
select 100 AS a,200 as b
set @trancount2=@@trancount
if(@@trancount>0)
ROLLBACK TRAN tran2
SELECT @trancount1 AS trancount1,@trancount2 AS trancount2
select COUNT(*) FROM #temp
Think you know the answer? Click here, and find out if you are right. This question is worth 1 point in this category: transactions. We keep track of your score to give you bragging rights against your peers. We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center. |
|||||||||||||||||
SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon! |
|||||||||||||||||
|
|
|||||||||||||||||
Yesterday's Question of the Day |
|||||||||||||||||
|
I have the following table CREATE TABLE #Users1 ( UserId INT IDENTITY , UserName VARCHAR(8) , Sales Decimal(6,2)) Which contains the following data:
I then execute the following T-SQL statement
SELECT
UserId
, UserName
, LEAD (Sales,1,( SELECT MIN(Sales)
FROM #Users1 ))
OVER (ORDER BY Sales) salesgoal
FROM #Users1
ORDER BY Sales
SELECT 2 Answers
Answer:
Explanation: If you scroll down to C. Specifying arbitrary expressions in http://msdn.microsoft.com/en-us/library/hh213125.aspx, it says: The following example demonstrates specifying a variety of arbitrary expressions in the LEAD function syntax. |
|||||||||||||||||
SQL Server Execution Plans shows you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including: Which indexes are being used, and where no indexes are being used at all. How the data is being retrieved, and joined, from the tables defined in your query. How aggregations in GROUP BY queries are put together. Grab your copy today from Amazon! |
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
SCD Implementation with TSQLETL for a SCD using only TSQL, without hash functions More » |
|||||||||||||||||
|
|
|||||||||||||||||
Database Pros Who Need Your Help! |
|||||||||||||||||
|
Here's a few of the new posts today on the forums. To see more, visit the forums. SQL Server 2005 : AdministeringMSDB database not responding - Sql server 2000 I am getting below error, when i start backup Backup database cannot be used on database opened in... SQL SERVER AGENT SERVICE NOT STARTED - Error: SQL SERVER 2000 ENterprise sp3.. Could not start SQLserverAgent Service on local computer. The service did not return an error. This... splitting tempdb to multiple files - max size? - i have noticed tempdb contention on our tempdb, so i want to split the data file to multiple files.We have... Removing mirroring session on a partner database in Mirror, Disconnected / In Recovery state - I am trying to remove mirroring on a partner server database due to a network issue. Here is the scenario. We... SQL Server 2005 : Business IntelligenceCreated Excel files dynamically in SSIS - Can we load the data from multiple flat files (located in different folders) into multiple excel files (NOT excel sheets)... ssis xls and xlsx files - Hi friends i have i small doubt in ssis plz tell me how to solve this issuse a folder contains... SSRS - Deployment - dbo.Catalog - I have a report file named "Employee Detail.rdl" in the below location, C:\Report_Types\Employee Info Report\Employee Info Report I deployed this report using... SQL Server 2005 : DevelopmentCopy DB Tables from PRod to DEV - Hello, I have situation that my developer wants some tables from Prodction DB server to their desktop m/c. We have already sql... dynamic WHERE clause - Hello, I have a query that have a dynamic WHERE clause. The query's WHERE clause is built based on the web... SQL Server 2005 : Working with OracleHow to corelate oracle dba concepts and sqlserver dba concepts - Hello Team, How to corelate between oracle dba concepts and sqlserver dba concepts Please provide books or any links Thanks in advance SQL Server 2005 : SQL Server 2005 General DiscussionDB Growth and Production DB Log FIle - can any one give suggestion below issues... 1) can any one give sql script for to get the how DB Size... SQL Server 2005 : SQL Server 2005 SecurityNo login access to SQL - Hi, I have received a new laptop with sql server 2005 installed and configured. Unfortunately I do not know the sa... SQL Server 2005 : SQL Server 2005 StrategiesKeeping Application Cache in synce - The current request is to have buckets of cache so when multiple people are looking at specific data statically we... SQL Server 2005 : SQL Server 2005 Performance TuningA DIFFERENT FILEGROUP FOR THE INDEXES/TABLES OF MERGE-REPLICATION - Hi all, I have a 350+ GB database that is replicated using MERGE replication to over 350 sites accross the country... Huge cost issue with Temp DB using 2008r2 sp2 - Hi Guys, I have an issue that is completely baffling I need a guru's guidance / help. so a little bit of... how to find the utilization is high while we are running performance monitor - HI 1.Can any one say how to find [or how we can know ]the utilization is high while we are... SQL Server 2005 : SQL Server 2005 Integration ServicesPerformance issue using UDF in bulk load jobs (SSIS) - Hi All, We have bulk load jobs configured using SSIS packages. All the load jobs were working perfectly fine until 2... Is derived column better for performance of a package ? - I have a scenario where an excel have thousands of records with say 20 columns. In which their is a... Trying to pull from many tables with similar names using one for each loop container - Hi. I have a situation where I have various tables named like, for example, mytable_t1, mytable_t2, ... and so on. I'd like... SQL Server 2005 : T-SQL (SS2K5)CREATING A COLUMN TO RETURN THE MAX VALUE FOR EACH GROUP WITHIN A SUBQUERY - I would like the column PROJECT_LEVEL to consists of the MAX value within the column DASHBOARD_STATUS_LEVEL for each group (groups... Audit Triggers - Hi, I need to create two triggers on my small lookup tables for audit purposes. The triggers are for any inserts and... Dealing with Daylight Savings Time - Hi all, I'm having an issue regarding dates that are stored in one format vs another, as far as Daylight Savings... SQL Server 7,2000 : AdministrationSQL SERVER AGENT SERVICE NOT STARTED - Error: Could not start SQLserverAgent Service on local computer. The service did not return an error. This could be an internal Windows... What is extended proc sp_releaseschemalock - Does anyone have documentation or know what sp_releaseschemalock does? I had an issue that occurred last week when I was on... SQL Server 2008 : SQL Server 2008 - GeneralConfigure sql profiler to show queries that are timing out - Hello, How can I configure sql profiler to show me the actual query that has timed out. Im getting a few... Generate schedule table - I'm trying to generate a backup schedule table where I do FULL backups starting tonight and then do another FULL... Full & Differential backups - I would really appreciate it if someone could help understand the following. I have a stored procedure to backup all user... CURSOR strangeness - I have a cursor behaving strangely. Please consider the following SQL: DECLARE @condition bit SET @condition = 0 DECLARE X CURSOR FOR SELECT a + CASE... Parent Package does not wait on Execute Package Task??? - I am slightly confused... Everything I read seems to indicate that executing a sub-package through the Execute Package Task is... LinkedServer for Excel Worksheet - Hello comunity I need to install the package "Microsoft Access Database Engine 2010 Redistributable" with the Microsoft.ACE.OLEDB.12.0 on My SQl server 2005... Sql server 2012 connection - Hi All, I've got installed two sql server instances on a server: slq2008 r2 and sql2012. sql2008 r2 listens on default port... Stack Dumps - I know generally speaking Stack dumps have to be sent to Microsoft to for interpretation. But generally what are the... Raid Configuration ???? - is the Raid Configuration is important ? Why this is used ? changing my domain to SQL SERVER - I'm cuurently working on VC++ platform. Im trying to switch to SQL SERVER domain. What i really want to do to become... Problem in Viewing Standard Reports in SQL Server 2008 R2 - Dear, When I try to see any report in SQL Server 2008 R2, the system generates the following message: Error: Index (zero based)... Which one is best 'CURSOR' or 'TEMP' table, to fetch multiple columns????? - I want to fetch multiple columns from a table 'table' and compare with Table 'table2' .then update the value in... Ideal Shrink Size - Dear, I have a database of 400 tables. I set the default size of database file is 20 GB and log... OLAP security Roles - Hi all, is there a way to make a backup of the OLAP security roles ? Every time we add a... creating query - Hi! I have to create sql query for searching accouts in my database. Parameters for search i enter in textboxes (id,name,last... Passing of user defined tables between databases - Hi, I've the same user defined data type in 2 databases. When I declare it in one database and pass to... Auto Backup and Restore in SQL Server 2008 R2 - Dear, I have two servers such as A and B. A is my main server and B is my backup server. My purpose... Question about replication - We have transactional replication setup some time ago. Recently we created a new table, it has a PK, and I... Trigger on Insert for a unique row - Hi All, i'm looking to try to create a trigger that will just create a txt file in a folder when... SQL Server 2005 - Hi, Is there any easy way by which we could roll back a service pack installation in SQL Server 2005 without... NOLOCK Hint Corrupts Results from SELECT - Good day fellow SQL Server Enthusiasts, I'm having an issue with the NOLOCK query hint causing erroneous joins. In a nutshell,... how to insert image in sql server 2005 - hi, i want to insert image into sql server using sql query. please help. thanks in advance sanjay Release unused space in the mdf file - We recently upgrade to sql standard to sql Enterprise. We ran a maintenance job which defrags, reindex and cleans the... Continuing career with SQL - Hello, I hav just joint this forum. I have to ask few things. I have worked with SQL 2000 and basic 2005.database design. I've... Activity monitor loads slow - Hi, I have a very general question. Does anyone know why the activity monitor in SSMS 2008 and/or 2008R2 in a... SQL Server 2008 : T-SQL (SS2K8)Doing something like an Excel FillDown function in SQL looking for suggestions??? - I have a recordset that returns the following data: [code="plain"] ItemNumber DisplaySeqNum 10 1 2 3 4 20 5 6 7 8 30 9 10 11 [/code] It's like an invoice sheet where there's multiple parts to... No record count in PIVOT - Hi, I have the following query to count the number of employees per costcenter using the pivot command. The @Columns parameter declares... duplicate rows - hi my table has following data productno productname des quantity 1 borin 4x-mal 2 1 borin 5x-cal 3 2 hypoid 4-5cal 4 2 hypoid 4-5cal 4 here,i... duplicate rows - hi my table has following data productno productname des quantity 1 borin 4x-mal 2 1 borin 5x-cal 3 2 hypoid 4-5cal 4 2 hypoid 4-5cal 4 here,i... Update one table rows with another table if match found - Hi, I'm having 2 tables, One table holds some numbers of type varchar and the other table holds the same numbers... Identity column reseed cause problem with other database - We have a data warehouse team that uses the identity column (surrogate key) as primary keys for many cubes tables... Subquery - Hi guys Trying to gear up my T-SQL skills When dealing with subqueries, like below [code="sql"]select Col1 from Tbl1 where Col1 = '52' and... Is it possible to create a script that will generate a create table script for all tables in a database at once? - Say I have a database called MyDB with 100 tables. I need to create these tables on another db. Is... Formatting date of birth using South African ID number - Hi All I need help creating date of birth using ID number the ouput that im looking is a follows e.g. RSA... Creating index - I want to create non clustered index on a view to check the limit that how many non clustered index... Conversion failed when converting from a character string to uniqueidentifier. - Hello I keep getting the above error when running this script. any ideas? It's driving me nuts! It's probably something... SQL Server 2008 : SQL Server NewbiesJOIN for beginners - Hi all! I have two tables: Dutyrostershift, which as a field called id, which is defined as an int with identity(1,1) and... how do i get the column values? - I have this table called parent table. Id | LastName | FirstName | Gender | ParentID 1 | Jones | Bob | M | NULL have no parent 2 | Allen | Larry... Can't restore transaction logs - Hi Everyone, I cannot restore the transaction logs after restoring the full backup. It shows the transaction log is too early to... Linq to Sql tutorial codes and SQL Server Express 2008 : - Linq to Sql tutorial codes and SQL Server Express 2008 : I am testing some tutorial sample codes for Linq to... Newbie Questions - I am an SQL Server Express and Server Management Studio newbie. I am currently on pg 380 of the 2012... Convert int to time - I have a call accounting database for our telephone system the duration field is int and I need to convert... Report Server Blocking - We have production issue. Our ReportServer database is causing blocking. The dbcc inputbuffer results show: "ReportServer.dbo.WriteLockSession;1" and "ReportServer.dbo.CreateChunkSegment;1". Does anyone know how... SQL Database moved under System Databases by mistake - I have a sw wich has a tool to configure replication between my 2 dbs I have. When the tool... SQL Server 2008 : Security (SS2K8)how to create a sql login and add to database user which already exists - hi everyone , how to create a sql login and add to database user which already exists 1. user "appp" is... SQL Server 2008 : SQL Server 2008 High AvailabilityAutomation scripts - i need to configure mirroring for multiple db's can any one send me the automation scripts to configure mirroring for... Transactional replication with updatable subscription - Hi, when setup transactional replication with updatable subscription we can see the linked servers created automatically. What is the role of... SQL Server 2008 : SQL Server 2008 AdministrationSQL Server 2008 Stage Server - Hello, We're planning to setup a stage server that will mirror the production server. I'm familiar with SQL and have some... Nonclustered Indexes - Hi All Please help me clear up something regarding nonclustered indexes A non-unique Clustered index has a 4byte uniquifier column added to... rebuild on clustered index - We have a heavy used table. We rebuild the indexex every night on this table. When the clustered index fragmentation... What would be the best RAID configuration for this hardware? - We have a brand new DELL MD1200 with 12-spindles - These are 15Krpm, 600GB SAS drives The server this is going to... Programming : GeneralExecution Plan Reuse - We have SPs that take several parameters; when I check the plan cache using ... FROM sys.dm_exec_cached_plans AS ECP CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS ESQL ... I... SQLServerCentral.com : Anything that is NOT about SQL!Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You... Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene... Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in... Reporting Services : Reporting ServicesBlank Matrix Table Columns - Hi I am have created a price list report which uses a matrix table within a list box to list... How to sum report items values in the body of the report? - Hi guys! I'm the beginner in SSRS and I have a problem, which I'm not able to manage with. I know that... Database Design : Disaster RecoveryData Warehouse Disaster Revcovery Options - I'm in the process of implementing a Data Warehouse. I have a Staging Database and a DatawArehouse Database. I have data feeds... Data Warehousing : Integration ServicesTruncating HTML. - I've an odd problem truncating HTML. It's taken from as NTEXT which I'm then attempting to truncate by replacing the field... Bulk Insert Task, set default date format - Guys, I am writing a Package that will do a number of bulk inserts. The files are flat file Pipe delimited... exec storedProc get return value - Hi guys, I am very experienced in T-SQL but am still fairly new to SSIS and am trying to run before... Date change before insert to database - I have a file with the date 20121025171203. I have created a database table with the field date in SQL... Data Warehousing : Strategies and IdeasBudget Fact Table and Date Dimension - We have a fact table for budgets. That table consists solely of the Budget Amount measure. How can I join... Use the surrogate key - Here is the scenario. I have a fact table that looks like the following: [u]factEmployeeWorkHours[/u] PK ID LastName FirstName Hours The fact table joins to a... Data Warehousing : Analysis ServicesSelecting a constant using MDX - Hi, I'm a real newbie when it comes to writing MDX. I'm trying to build an ssrs report off a cube... Microsoft Access : Microsoft Accessdisplace input row in Access 2003 and SQL Server 2000 - Hi, Output in Access 2003: ID | [b]Description[/b] | Quantity | Title | Obj --- ---------------- -------------------------------------------------- 17 | [b]6 [/b]| 253000.00 | | 18 | [b]7 [/b]| 330000.00 | | 19 | [b]1 [/b]| 340000.00... |
|||||||||||||||||
|
|
|||||||||||||||||
|
To be removed from this list, please click here If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it here. This transmission is ©2012 Red Gate Software Ltd. All rights reserved. Contact: webmaster@sqlservercentral.com |
|||||||||||||||||
Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets
Day 4 of The OLAP Sprint
Designing Databases for Rapid Resilience
Enable Hyper-V on Windows Server 2012
