| A community of more than 1,600,000 database professionals and growing |
| | | What's Your Backup Speed? I ran across a thread recently where someone was looking to estimate the time it too to perform a 600GB backup. I've seen other threads with similar questions. The general advice is that you need to run a backup and see how long it takes. After all, your hardware, your workload, the compression settings, and more will affect your backup time. In researching speeds, I didn't find much info out there, so I thought I'd start a poll. This might be fun for you, so I'm hoping quite a few of you will answer. How long does it take you to back up 1TB? I know some of you don't have a 1TB database, and probably a few of you have much larger ones. However, look at your backup and extrapolate (or interpolate) the time it would take to complete a 1TB backup. Assume that your hardware would scale appropriately to allow you to run a 1TB backup if you actually had that much data. If you can share your setup, or your configuration, that would be very interesting. I know some of you use striped backups, some compress, some use third party tools to help, and maybe you even have another way to actually get data from a live system into a backup file. Over the years I've seen some amazing improvements in the backup and restore functionality that SQL Server provides. I think it's one of the most solid subsystems in SQL Server, and I'm looking forward to seeing just how fast some of you can create backup files. Steve Jones from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums |
| | The Voice of the DBA Podcast Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and Libsyn. The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. |
|
|
|
| | ADVERTISEMENT | | | | Not enough hours in your day? The SQL Toolbelt lets you reduce the time spent on SQL Server development and administration. Cambridge University developer David Spaxman, for example, says: “I’m saving 10-12 hours a week using the SQL Toolbelt.” Learn how you can double your productivity, speed up deployments and protect your data. Download a free trial. |
| | | | Free SSMS add-in: SQL Search Redgate SQL Search is a free SSMS add-in that lets you find fragments of SQL across multiple objects and multiple databases. Once installed, SQL Search appears on the SSMS toolbar, allowing you to quickly navigate to objects wherever they are on a server. Download SQL Search now. |
| | | | How to track every change to your SQL Server database See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more. |
|
|
|
| | | | | Divya Agrawal from SQLServerCentral.com Read and document multiple SSIS packages in an Excel file. The document can be used to do further code analysis. More » |
| | Additional Articles from SimpleTalk Developing JavaScript for web pages can be perplexing. You will get errors that seem to make no sense, You will be given nuggets of advice about how and when you can use JavaScript to manipulate the DOM or make Ajax requests. It is far better to understand the reason for these rules; the single-threaded nature of JavaScript, and how it loads the page and manages the event loop. How can you achieve parallel processing and what is the best way? Igor makes it all clear and obvious. More » |
| | Learn how to use Redgate’s DLM solution to improve your database change management process in our free webinar on November 15th. Microsoft Data Platform MVPs, Steve Jones, and Grant Fritchey, will demonstrate how Redgate’s tools plug into Subversion, TeamCity & Octopus Deploy so that you can build, test and deploy your database alongside your application code. Register now. More » |
| | 2016 is going to be a special year in my life. There was an article on Oscar awards a while... More » |
| | Koen Verbeeck from SQLServerCentral Blogs I was preparing a demo for a session about the new features of Master Data Services in SQL Server 2016.... More » |
|
|
| | | Today's Question (by Mike Tanner): | I build a table with the integers 1-100 and compare the results of queries with several apparently similar restrictions. First let's build our table of values 1-100 (courtesy Itzik Ben-Gan). WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0) ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4 ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16 ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256 ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv3) SELECT top(100) n into x FROM Tally ORDER BY n; The question we are trying to answer is how many integers between 1 and 100 inclusive are divisible by 2 and 3 but not 5? Our first two versions are using simple 'and' clauses, in different orders: -- a select count(*) from x where n % 2 = 0 and n % 3 = 0 and not n % 5 = 0; -- b select count(*) from x where n % 2 = 0 and not n % 5 = 0 and n % 3 = 0; Now we use set arithmetic. In this case, just intersect and except (not union). Again we are interested in those values where our value 1-100 is divisible by 2 and divisible by 3 but not divisible by 5. -- c select count(*) from ( select * from x where n % 2 = 0 intersect select * from x where n % 3 = 0 except select * from x where n % 5 = 0 )z; And then as previously, moving the 'not divisible by 5 clause'. -- d select count(*) from ( select * from x where n % 2 = 0 except select * from x where n % 5 = 0 intersect select * from x where n % 3 = 0 )z; To save you from mental strain, I'll tell you that the answer from a is 13. And I'll tell you that the answer from c is 13. What are the answers for b and d? |
Think you know the answer? Click here, and find out if you are right. We keep track of your score to give you bragging rights against your peers.
This question is worth 3 points in this category: T-SQL. We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center. |
|
|
| |
| ADVERTISEMENT | | Pro Power BI Desktop This book shows how to deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. You can now take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience’s attention. Slice and dice the data with remarkable ease then add metrics and KPIs to project the insights that create your competitive advantage. | |
|
|
|
|
|
|
| Yesterday's Question of the Day |
| Yesterday's Question (by Steve Jones): I have this statement in SQL Server 2016. What happens when I execute this: CREATE TABLE Users ( userID INT IDENTITY(1,1) , Username VARCHAR(200) PRIMARY KEY NONCLUSTERED , emailaddr VARCHAR(300) , INDEX useremailNDX CLUSTERED (emailaddr) ) GO
Answer: The table is created with a nonclustered PK and a clustered index on the emailaddr colum. Explanation: In SQL Server 2014, the inline index creation syntax was added to T-SQL. This means you can define an index as part of your CREATE TABLE statement. Ref: CREATE TABLE - https://msdn.microsoft.com/library/ms174979(SQL.130).aspx » Discuss this question and answer on the forums |
|
|
| | | Database Pros Who Need Your Help |
| | Here's a few of the new posts today on the forums. To see more, visit the forums. Adding Space to the Database - Adding space to the Database , when the db is 75% full here i done some script for finding free space select s.FILEID... SSMS 2016 slow connection (Windows 10) - After upgrading from Windows 7 to Windows 10 - and after some updates of windows a very slow connection (10 - 20s)... Total Sum is not Calculating in event of Multiple Clock events - Dear Experts, OT sum is not computing correctly in case of multiple clock events, find below my sql code, example. image... Concurrency - Please Provide Comments - Hi all I have a question about concurrency. Let me provide some set-up code first. [code="sql"]DROP TABLE IF EXISTS dbo.Test1 CREATE TABLE dbo.Test1 ( PK... DB compression testing in SQL 2014 - I need to test DB compression where the SQL DB on SQL2008R2 is taking around 2TB and most of the... Installation errors - exeption type: System.ArgumentException - File format is not valid. - I'm getting this error when I try to install SQL 2014 on a Windows 2012 machine. There may have been... WITH INDEX hint - I was recently trying to optimize a big SQL statement (a dozen of JOINs and APPLYs), commenting some joins and... Include the first zero when converting time from datime into INT - I have [quote]declare @t datetime = '2016-11-02 07:22:26.320' SELECT LEFT(CAST(REPLACE(CONVERT(VARCHAR, @t, 108), ':', '') AS INT), 6)[/quote] which gives me INT value of 72226, and... Total by Group. - Dear All, I am trying to bring the group total .below my query, [code="sql"]WITH Prep AS (SELECT preffered_name as Department ,email_address1 as subdepartment,friday,saturday,government_num... Huge tempdb log file - in the wee hours of the morning, something blew up our tempdb log file to 170+GB, filling the disk. the... Insert XML into SQL Server table - Hi All, I will get XML input from the UI and need to insert the same into one of my SQL... Filling the missing rows/gap in the data - Hello, I have been setting up a database where I can store our sales and activity data, and struggling at some... Big data: transfer x-number of rows per batch? - Hi all, I must transfer a big table (231k rows not that big, but 735GB big because of blob) to another... is anything using the sa account? - I'm reviewing an estate i've just taken over. I have some servers where the sa account is not disabled. I... Remove decimal from varchar field - How can I use SQL to remove a decimal from a field value. Current value = 1.66770 Correct value = 0166770 Query to find all procedures that uses functions in the where clause(left operand) - Hi, I was just trying to find out all the modules (procedures, functions, triggers) that uses functions in the WHERE clause this... Counting field values per day between specific date ranges - Hi All, thanks in advance for any help. I have a table where I store holiday bookings, one row per booking.... Delete files that are older than 7 days - I have a folder in d drive called Archive (d:\archive) which stores all data load files. The name of these... Easy way to search for a string any where within XML column? - Is there a really easy way to search for a string in an XML column? All the examples I come... stored procedure running slower than sql query - Hi I have one stored procedure and its taking 10 mins to execute. My stored procedure has 7 input parameters and... |
|
| This email has been sent to [email protected]. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the [email protected]. | 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 ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: [email protected] |
|
|
