|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - Remote DBAs |
|||||||||||||||||
This editorial was originally run on Sep 19, 2007. It is being re-run as Steve Jones is out on the SQL in the City US 2012 tour. There's still hope for all you DBAs out there looking for a telecommuting job that allows you to work in your pajamas. Someone sent me this case study about remote DBAs and I decided to pass it along. It's a one page PDF, and it's a fluff piece to some extent for Bluewolf, a company that has outsourced IT staffing, but has a section on remote DBA work as well. I've wanted a remote DBA company for a long time. It's something I thought was easy to do, most all DBA work is remote to the server anyway, and we tend to work alone. It's a specialized skill and for the most part, a production DBA is an insurance cost. Face it, as a production DBA, I've typically had periods of time where we work really hard on upgrades, stabilizing systems, responding to issues, etc. Then we have some fairly quiet periods where we work on tuning things that don't work well. There are definitely exceptions for environments that are understaffed and have perpetual problems, but a lot of the time we are insurance for the bad times. Which makes for a pretty good job. I've usually enjoyed my jobs; they haven't had too much stress on a daily basis, and I could handle the tedious nature of production systems. If I had 4 or 5 of these jobs I did from home, that would be amazing. I've just struggled finding managers that were comfortable with a DBA working from home 3 or 4 days a week. For some reason they just want to see you there every day. There are definitely jobs and companies out there making this work. A few friends of mine from Colorado Springs even have a company that does this: SQL On Call. They can help in many areas, but they're happy to fill in for a DBA on vacation. If you're a one-DBA shop, give them a call. I know, it's a plug, but I have no affiliation. They're friends of mine and good DBAs. If you want to telecommute, especially on a part-time basis, be sure you save the case study link along with any others you come across. A lot of preparation and good evidence that it works will go a long way to convincing your boss to let you try it. » Join the debate, and respond to today's editorial on the forums » To submit an article, rant or editorial, log in to the Contribution Center |
|||||||||||||||||
|
|
|||||||||||||||||
Question of the Day |
|||||||||||||||||
|
Today's Question: What 4 values are returned by the following: DECLARE @Date1 DATETIME = '2012-08-28 11:53:00' , @Date2 DATETIME = '2012-08-29 13:25:00' SELECT DATEDIFF(day, @Date1, @Date2) , CAST(@Date2 - @Date1 AS INT) , CAST(@Date2 AS INT) - CAST(@Date1 AS INT) , CAST(CAST(@Date2 AS FLOAT) - CAST(@Date1 AS FLOAT) AS INT) Think you know the answer? Click here, and find out if you are right. This question is worth 2 points in this category: T-SQL. 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. |
|||||||||||||||||
Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks. Get your copy from Amazon today. |
|||||||||||||||||
|
|
|||||||||||||||||
Yesterday's Question of the Day |
|||||||||||||||||
|
I create the following 2 tables (#A and #B) and insert the data as shown into the -- respective tables
CREATE TABLE #A(S VARCHAR(10),R VARCHAR(10))
INSERT INTO #A
VALUES('Alpha','D1234'),('beta','A1122'),('charlie','D1234'),('bravo','C1342'), ('Doug','B1964'),('harry','A1122')
CREATE TABLE #B(R VARCHAR(10),S INT)
INSERT INTO #B
VALUES('D1234',1001),('A1122',4001),('D1234',2001),('C1342',5001) ,('A1122',3001);
Next I execute the following T-SQL
SELECT tblA.S, tblA.R, subQuery.S
FROM #A tblA
OUTER APPLY (SELECT R, S
FROM #B tblB
WHERE tblA.R = tblB.R) subQuery;
The question is how many rows are returned when I execute the above SELECT statement?
Answer: 10 Explanation: There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function. Ref: OUTER APPLY - http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx |
|||||||||||||||||
Optimize your queries—and obtain simple and elegant solutions to a variety of problems—using window functions in Transact-SQL. Led by T-SQL expert Itzik Ben-Gan, you’ll learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner. Ideal whether you’re a database administrator or developer, this practical guide demonstrates ways to use more than a dozen T-SQL querying solutions to address common business tasks. Get your copy from Amazon today. |
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
Find Primary Column of all tables in databaseThis code helps in finding Primary Keys for all tables in a database. 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 : AdministeringDifferential backup failure - Hi Team, I am facing an issue with differential back up . The differential backup is failed only on sunday . we... Logship Add file. - Hi All, In logship, at primary server, someone had added a data file to the database. how to add the same... SQL 2005 Cluster Problem - Hi Guys, I'm wondering if anyone can help me, i really don't wanto to reinstall one ofe the nodes in the... DBA Interview Questions needed - Serious only please. - Hi dudes and dudettes... The boss and I are conviced that we need a dba since the 3rd party firm providing... SQL Server 2005 Profiler Column Filter - Hi All I want to filter a trace of SQL Server 2005 Profile with the IP address of the client system... SQL Server 2005 : Backupst-logs - i have 2 db whose t-log backups occurs everyhour. but everyweekend the log file fills...needs to to be shrunk to... Odd Filegroup backup behavior - We have a particularly large database that is partitioned across multiple filegroups. We have nightly file group backups on the... DDL trigger for RESTORE DATABSE - Hi, I have to create a user in each user database after a database has been created or restored in SQL... SQL Server 2005 : Business IntelligenceSSIS Pakage Error - Executing SSIS package i am getting following error. [Execute SQL Task] Error: Executing the query "exec proc_BusinessRulesValidation @UserName = ?, @..." failed with the... Condition in Drillthrough action - I have a cube with 2 measures ActivityCount | MissingGender 10 | 2 At backend in fact table it goes like this ActivityCount | MissingGender 1 | 0 1... SQL Server 2005 : SQL Server 2005 SecurityDatabase role - In SQL Server Management Studio > SQL server > Databases > (My Database) > Security > Users > Right click... Deny delete and drop permission to the sql user - Hi, how to deny delete and drop permission to the sql user for a particular database in sql server 2005 I tried... SQL Server 2005 : SQL Server 2005 Performance TuningTemp table for Performance Impact - Hi, We are using the table variables inside the sp. It is working fine at QA and staging server. When It... How to increase indexes performance for fast queries results - Hi All, I have a table named "Tbl_Inv" in my database that contains 4.7 Lacs records with six non clustered indexes... SQL Server 2005 : SQL Server 2005 Integration ServicesImport file from HTML source - Hi I have a subscription to a company that provides a csv file from a HTML link which i currently download... How to find packages which include a specific database as a datasource - Problem : Database needs to move from one server to another. Before moving the database, I would need to get a list... How to configure OLE DB to use dynamic connection? - Good afternoon, Using the following excellent site as my inspiration [url]http://sql-ution.com/[/url] to build my own DBA repository from scratch. Progress so far: See... SSIS logging, Event Handler, and OnVariableValueChanged - I am trying to write a generic script task that will log the changes to any variable (properly enabled, etc)... SQL Server 2005 : T-SQL (SS2K5)create variable for IN statement - hi i have a query which i need to group some codes into one code. so... [code]case when ProdGrpCode in ('500','510','5201','580','630','460','470','480','490') then '82' ....[/code] is... Default Constraint Not Reflected on Table - Hi Guys, I have created a Default Constraint for my table. But this is not reflected on my table. EX: Column ImportDate... SQL Server 7,2000 : Data CorruptionHow to Recover Deleted Rows from sql 2000 Database - How to Recover Deleted rows from sql 2000 Database using with some period parameter eg: from 01/01/2012 - 20/05/2012 Display all... SQL Server 7,2000 : In The EnterpriseSQL 2008 x64 clustering active / active - Hello all, I've been trying for days now to get my new SQL Server x64 Enterprise cluster online in an... SQL Server 7,2000 : T-SQLSUBSTRING And CHARINDEX to find start/end of string - Hi all, I have a big long text string that can vary in lengh and contrain audit information about data that... Select TOP 1 and TOP 1 (-2) - Hello All, Yes this is SQL server 2000/SP4. One of my legacy system generates data where there is four records per group.... Csv list in column to separate columns - Hi all, Can I ask please if I have a row and one column has a csv list in it. Is... SQL Server 2008 : SQL Server 2008 - GeneralHandy and Simple tool for SSMS 2008 R2??? - Hello, I was wondering why SSMS does not have the standard builtIn plugin Quick Math (for example: sum, avg, max, min).... What SQl Agent Jobs Should I set up? - Hi, What are the most common/best SQL Agent Jobs to set up? The ones I generally set up are as follows:- (Daily... Why is does not sys.objects.type have the database default collation? - Today, I discovered to my big surprise that the type column of sys.objects is neither the default collation of the... Determine filepath from open SQL script - Hi everyone, Can any one tell me how to determine the file path of an open SQL file in the... Is there a way to automate the manually inserted data i ssis pls le me know - I hav inserted some records manually into the target table but we dont want that we want to automate the... AWE and PAE both should be enable on Windows 2008 R2 32 bit? - Hi Operating system - Windows 2008 Enterprise Edition 32 bit. SQL SERVER - MS SQLSERVER 2008 R2 32 bit. Physical Memory - 8GB whenever enabling AWE option... How to create a procedure which takes multi value parameter -Urgent Help Needed - Hi, I need to create a procedure which takes 2 input parameters and using this stored procedure in crystal reports for... Suspended Commands on master db - Hi I'm hoping someone will be able to cast some light on my current server issue. There are a number of commands... SQL Serevr Services - Hi All, Can we start and stop the SQL Server services without being the member of Adminstative group on local server... Why does this take so long! - Hi Everybody This is my first posting in this forum, so please bear with me if I should get some thing... SQL Server has encountered occurrence(s) of I/O requests taking longer than 15 seconds to complete - I have a few SQL Server 2008R2 SP1 and SP2 as well as a couple SQL Server 2008 SP3 getting... Read nodes from XML to table - Hi everyone, I have xml structure as below: <row id="PDPD1134200001" xml:space="preserve"> <c2>100113</c2> <c3>KHR</c3> <c4>1</c4> <c5>TR</c5> <c6>00</c6> <c7>1</c7> <c8>21050</c8> <c9>B</c9> <c10>10049</c10> <c11>72.00</c11> <c13> Robocopy false error code of 8 when executed from SQL - I am trying to call robocopy from a bat fie to move backups to a network server. This is working... Practice exam for 70-451 - Can anyone recommend a practice exam for the 70-451? I usually use measureup but they don't appear to have one. Optimize query needed - Hello Expert. I need to convert data from an existing column from BigInt to VarBinary type. I wrote the function to... Is there a way to tell if a box is really a VM or physical server? - We have infrasctructure folks that setup and configure hardware when we need a new SQL Server for something. Once the... OS permission override by SQL Server - Hi My windows ID has admin rights on the sql server but I am not the administrator of the server. Now... Creating a view from 2 joined tables - inserting multiple lines - Okay - my challenge today, is that I need to create some labels for a "Picking List" ( BOM ) - One lable per... SQL Agent not able to start on SQL Server 2008 Developer Edition - Dear All, I have SQL Server 2008 Developer Edition (64-bit) SP1 installed on a Windows Server 2008 R2 machine. For some... SQL Server 2008 : T-SQL (SS2K8)Dynamic Filter and Order By - Hi, I'm doing some testing with dynamic filtering and order by clause. I'm comparing dynamic SQL vs using the CASE statement... [code="sql"] DECLARE @FilterName... Query to Exclude Duplicate values in different columns. - Can anybody help me with this logic. I want to exclude rows in which value for column nc1 is repeated in... Need assistance to CAST the StartDate - HI Need help to get 2 things out of the below result set. 1) Make the StartDateTime as '2012-xx-xx 12:00:00.000' for... find increase/decrease amount - i want to find the increase / decrease amount group by sector and month. --input table declare @t table ([id] int,[Amount] int,monthno int,Sector... Date Comparison Error - Dear All, Greetings!!!! Today I face a problem in Date Comparison please help me in out thanks in advance... problem mention below:- In... Help with Count over days (but not per day) ... the total up until that day through the date range. - I am having an issue trying to wrap my head around this. I am trying to get the count of something... search levels - Hello all, I'm wondering if it is possible to do the following. I have a table with locations in the world. All... How to usdate records after some time periode of its insretion in table. - hello friends I am working on a application where we are sending OTP as password to users but this OTP... how to add indexes in all tables in a database - hi i want to create foreign key indexes on all the base tables in a database ,i need to check... Best way to effienctly store hugh amounts of data - Would be interested to hear on some of the best ways to approach this situation. I've recently started at a company... I need to compare and split the string and then save it in the detail_tb but dont know how ? - Some one post this code in forum and it work fine except i didnt got any clue,How to map these... Consolidate Overlapping Date Periods - Getting late in the day and I could use some support from those who are feeling fresh! :-) Suppose I have... varchar to time or datetime or ARGGHH Please help me. - I have a datetime (your standard yyyy-mm-dd hh:mm:ss) column and a varchar column that holds an appt time that looks... Multiple Space into one + 65,33,17,9,5,3,2 - All, I have recently read Jeff Moden's Multiple space into one space article and i saw M's reply on this topic. He... SQL Server 2008 : SQL Server NewbiesEncryptByPassPhrase/DecryptByPassfrase, why don't this work? - This query returns a lot of results: select Convert(Char,DecryptByPassPhrase('[PASSPHRASE]',cpr_encrypted)) from dbo.personal I have changed the actual passphrase to [PASSPHRASE] above. one of... How can I count the number of occurances of a string in an ntext column for each record? - Dear SQL Gurus: I have a table [tblHTML] with an ntext column [strPage] that has HTML in it. Each record's [strPage] field... Using Access to put data into SQL Server - Hello everyone. So I have a database in SQL Server 2008 that contains 5 tables. I have connected Microsoft Access... F5 to refresh - Hi, need some help. On one server when I open Management Studio and click on something in the object explorer and... SQL Server 2008 : Security (SS2K8)Disabling an account on unexpected SQL Statement - Hi Folks, Wondering if anyone had any ideas on this dilema? I want to be able to trace code through my database,... View Records That Are Associated With a User Table - I would like to find a way to setup a user table with logins (UserID) in one column and a... SQL Server 2008 : SQL Server 2008 High AvailabilityScript to Backup SQL Server Log Files - Hi All, Need assistance in creating a script for the below requirement. Any assistance to satisfy this requirement will be greatly... Replication - After setting up Replication (Transactional Publication with updatable Subscription) between two instances one instance being both Distributor and Publisher, with... SQL Server 2008 : SQL Server 2008 Administrationsql server Audit problem - Hi, I have created one database level audit to monitor select on table "Bet". This audit works only when principal name... SQL express 2008 R2 - DB size - I am using express edition of SQL 2008 R2. As per the microsoft sites we can have max DB size... Error Msg 3013, Level 16 - ? - Hi, Server OS - win 2008 32 bit, Database - SQL 2008 R2 32 bit. Physical Memory - 8 GB.. Not enable AWE and Resource governer.. Backup plan... Installing SP2 on SQL Server 2008R2 RTM (Standard) - Need to install the latest service pack on SQL Server 2008 R2 RTM (Standard Edition). Please let me know if... Capacity Plan for Server ? - I have a scenario where I need to import data from a server to another one. The data transfer is... DBCC CHECK DB ERROR - When we run DBCC CHECKDB on one of our production Server we get the below error.. Error:--The database could not be... urgent help needed--moving data from 2000sql to 2008 sql - [color=#red][/color]I have a database of size 42gb on production which is 2000sql standard edition and want it to move to... Rebuild/Reorg indexes script - Note: this is more of a "request for opinions" post, not an actual problem that I have (no jokes here... msdb backupset new index - hello, do you know why this index creation take for ever when I run it: CREATE NONCLUSTERED INDEX [IX_backupset_database_name_backup_set_id] ON [dbo].[backupset] ([database_name]) INCLUDE... Is there a time limit for Maintenance Plan execution - Hi, We have a maintenance plan that performs a DBCC against all databases on the server before executing a backup of... DBCC CHECKDB errors - I tried running: DBCC CHECKDB ('DatabaseName') WITH NO_INFOMSGS; And got the errors: [color="red"]Msg 1823, Level 16, State 2, Line 1 A database snapshot... How to Restore model database from backup ? - Hi, I want to restore the model database from the latest backup , I am trying to create a scenario by deleting\moving... Programming : PowershellPower shell - Hi guys, I am new to powershell, can anyone please help me in undertanding powershell. I googled about powershell. But... SQLServerCentral.com : Anything that is NOT about SQL!Ryder Cup 2012 - So, the Ryder Cup's just started over at Medinah... Any golfing DBA's on here & will you be following the competition over... Views on Private Messaging - I've attempted to answer a newbie question recently and because some of their data is private, some of the thread... What motivates those of you who answer questions? - I've found a lot of helpful information on this site. Often when I can't find something in books on line... Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,... 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... Sqlservercentral,titles and points - I want to know the name of all titles given by our sqlservercentrsl.com site. I mean, my title is 'SSC... SQLServerCentral.com : EditorialsChallenge Yourself - Last week Andy Warren wrote a great article called revisiting what you know and it got quite a few interesting... SQLServerCentral.com : SQLServerCentral.com AnnouncementsSQLServerCentral Member Levels - Updated 2010/06/10 - Here are the current levels: [code] Forum Newbie 0 Grasshopper 10 SSC Rookie 25 Valued Member 50 SSC Journeyman 75 SSC-Enthusiastic... Reporting Services : Reporting ServicesMatrix column dymamic width - Hi friends, i am wondering if this is possible. I've set up the matrix to show multiple columns and the number... Compare dates & Using Indicators - Hi geniuses! I need to compare dates (proj.last.publication (field) and current date) of various projects and depending of the result, expose... Cascading Parameter of type DateTime does not work? - I have a normal integer parameter to allow the user to select a preset date range like this value, label 1,... Can we add Z-axis (third vertical axis) to a chart in SSRS 2008 R2 - I have searched the web and SSRS documentation but can't seem to find any information whether SSRS allows adding Z-axis... SQL Merge 2 Databases - Hi geniuses! I need to merge 2 databases to colect information for my reports. Have really no clue about how to... Data Warehousing : Integration ServicesSSIS - Import flat file with trailer - I have a flat file with trailers that the total the amounts in certain columns. I need to import the... |
|||||||||||||||||
|
|
|||||||||||||||||
|
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 |
|||||||||||||||||
What a View
Protecting the SQL Server Backup folder
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions