A community of more than 1,500,000 database professionals and growing

Back to Basics: Capturing Baselines on Production SQL Servers

Erin Stellato from SQLServerCentral.com

If you have not been capturing baselines on your production servers, then today is the day you can start. This article provides scripts, valid for SQL Server 2005 and higher, which anyone can use to capture basic information about a SQL Server instance. More »

SQL Saturday #182 - Istanbul

Press Release from SQLServerCentral.com

SQL Saturday at the gateway between Europe and Asia. If you are nearby, come join in a free day of SQL Server training and presentations. More »

Understanding the SQL Server 2012 BI Semantic Model (BISM)

Additional Articles from MSSQLTips.com

SQL Server 2012 introduced an unified BI Semantic Model (BISM) which is based on some of the existing as well as some new technologies. This model is intended to serve as one model for all end user experiences for reporting, analytics, scorecards, dashboards, etc. In this tip, I will talk in detail about the new BISM, how it differs from earlier the earlier Unified Dimensional Model (UDM) and how BISM lays down a foundation for future.  More »

From the SQLServerCentral Blogs - Explore file physical structure - Delete

jamesxu98918 from SQLServerCentral.com

1. Create sample db use master go CREATE DATABASE [test] ON  PRIMARY ( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master\test.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB... More »

I received an email just after I arrived in Seattle for the last SQL in the City 2012 stop. It was from Simon Doubt, who was also coming to Seattle for the PASS Summit, but who hadn't planned on just attending the conference. Simon had actually come to the Pacific Northwest with a plan: The OLAP Sprint.

After a few years of attending the Summit and being inspired, taught, and excited by the immersion, it was time to up the ante. The idea was simple, even if the execution would be a challenge. Simon wanted to do more than attend a variety of talks and have interesting conversations. In seven days in Seattle he planned on learning to build an OLAP solution.

You can read about his journey and it's an interesting one. It was a success, and while it hasn't created an OLAP expert, I'm sure that Simon has gained a lot of things from his focused effort. It's a starting point to learn more. It's an accomplishment of a fairly difficult task, and it's a great story that should help a career move forward. This is certainly something I'd put on my resume.

Throughout the week as I read each entry, I noticed this was a way to tackle the week long conference not as a break from work, but as an exciting new project. The posts showed someone that was taking advantage of the resources at a conference to grow their knowledge and skills. This is a great example of what a plan can do for you, and how you can show your boss that the money spent on this type of training is worthwhile.

Not everyone will have a plan, and I'd guess most don't, but the next time you ask for funding think about including a plan of what you are looking to accomplish in a week. Whether you're attending a conference or an Immersion event, having a plan, with a few specific goals can show that you are using the money you get wisely.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast - 17.9MB WMV
• Watch the iPod Video Podcast - 19.3MB MP4
• Listen to the MP3 Audio Podcast - 3.8MB MP3

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: or now on iTunes!

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

Today's Question:

What is the output of this query in SQL Server 2008. Assume that getdate() is today's date.

create table #test(id int,EmpName varchar(50),dateofjoin datetime default getdate())

insert into #test
select 1,'malli',null
union
select 2,'reddy',' '
union
select 3,'test',getdate()

select * from #test

drop table  #test

A)

B)

C) Error

By malls reddy

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: Date Manipulation. 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.

Pro SQL Server 2012 Practices

Expert SQL Server Practices 2012 Edition is an anthology of high-end wisdom from a group of accomplished database administrators who are quietly but relentlessly pushing the performance and feature envelope of Microsoft SQL Server 2012. With an emphasis upon performance—but also branching into release management, auditing, and other issues—the book helps you deliver the most value for your company’s investment in Microsoft’s flagship database system.

Get your copy from Amazon today.

CREATE TABLE T1 ( ID INT, Name VARCHAR(20)
)
CREATE TABLE T2 ( ID INT, Name VARCHAR(20)
)
INSERT T1
VALUES ( 1, 'A' ),
( 3, 'AA' ),
( 4, 'AAA' ),
( 6, 'AAAA' ),
( 7, 'AAAAA' )

INSERT T2
VALUES ( 1, 'B' ),
( 2, 'BB' ),
( 3, 'BBB' ),
( 6, 'BBBB' ),
( 8, 'BBBBB' )

Which of the following queries give the right answer (2,4,7,8) ? (Choose 2)

-- 1
SELECT a.id FROM T1 a LEFT JOIN T2 b ON a.Id = b.Id WHERE b.Id IS NULL 
UNION
SELECT b.id FROM T2 b LEFT JOIN T1 a ON b.Id=a.Id WHERE a.Id IS NULL

-- 2
SELECT b.id FROM T2 b LEFT JOIN T1 a ON a.Id = b.Id WHERE b.Id IS NULL
UNION
SELECT a.id FROM T1 a LEFT JOIN T2 b ON a.Id = b.Id WHERE a.Id IS NULL

-- 3
SELECT b.id FROM T1 a RIGHT JOIN T2 b ON a.Id = b.Id WHERE a.Id IS NULL 
UNION
SELECT a.id FROM T2 b RIGHT JOIN T1 a ON b.Id = a.Id WHERE b.Id IS NULL 

-- 4
SELECT a.id FROM T2 b RIGHT JOIN T1 a ON a.Id = b.Id WHERE a.Id IS NULL
UNION
SELECT b.id FROM T1 a RIGHT JOIN T2 b ON a.Id = b.Id WHERE b.Id IS NULL

-- 5
SELECT b.id FROM T2 b JOIN T1 a ON a.Id = b.Id WHERE a.Id IS NULL
UNION
SELECT a.id FROM T1 a JOIN T2 b ON a.Id = b.Id WHERE b.Id IS NULL

-- 6
SELECT b.id FROM T2 b JOIN T1 a ON a.Id = b.Id WHERE b.Id IS NULL
UNION
SELECT a.id FROM T1 a JOIN T2 b ON a.Id = b.Id WHERE a.Id IS NULL
 

By Gary Chinn

Answer:

• 1)
• 3)

Explanation: We can use EXCEPT returns any distinct values from the left query that are not also found on the right query. The same thing can be done with an outer join.

Ref: http://msdn.microsoft.com/en-us/library/ms188055.aspx
http://msdn.microsoft.com/en-us/library/ms187518(v=sql.105).aspx

» Discuss this question and answer on the forums

Microsoft SQL Server 2012 Master Data Services

Harness your master data and grow revenue while reducing administrative costs. Thoroughly revised to cover the latest MDS features, Microsoft SQL Server 2012 Master Data Services, Second Edition shows how to implement and manage a centralized, customer-focused MDS framework. See how to accurately model business processes, load and cleanse data, enforce business rules, eliminate redundancies, and publish data to external systems. Security, SOA and Web services, and legacy data integration are also covered in this practical guide.

Get your copy from Amazon today.

Scripting CREATE INDEX Statements Automatically From Your Tables

Derek Colley from SQLServerCentral.com

Stored proc for scripting out full CREATE INDEX statements from your table indexes with all filegroup, filter and WITH parameters. More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

Transaction log files had grown to five times the database size. - Hi All, The log file is grown to five times the database size. Transaction Log file size 3.1GB Database file size is 700...

Roll up of a column in dynamic query - HI This is data in the table ClientId Ad TagId Name Total 1 D1 47 American 1000 1 D2 47 American 500 1 D3 47 American 300 1 D4 48 Hispanic 1000 1 D5 48 Hispanic 200 This is the result when I pivot the table ClientId TagId Name D1 D2 D3 D4 D5 1 47 American 1000 500 300 0 0 1 48 Hispanic 0 0 0 1000 200 you...

Unable to start SQL agent with domain account - Hi, I've recently changed our SQL services to run under domain account instead of the local account they used to use....

Error after changing the Domain - We have SQl Server 2005 Standard edition which was Intially on another domain at that time it was working fine. Now...

backup file growth and disk size-SQL 2005 database - Hi All, We use Litespeed to backup our databases. One particular database grows 330MB/day there is enough space on the data...

Isolation level using linked server - Our situation: - our EPR system uses Progress database (blackbox for me, don't even know where it is installed) - on a...

SNAPSHOT Isolation with (NOLOCK) being used all over - Recently, my applications architect decided that we needed to change the level of the database Isolation from the the default...

Can we shrink database file - Hi All, We have the SQL server 2005 where it has three data files on three different LUN's. Each data file is...

DeadLocking issue - I want to confirm that I am reading the deadlock information properly. It looks like to me that there are...

convert NULL to 0 in Pivot Result Set - I have a process where in the end I show my result in a "Pivot way". My last result is something...

rebuild clustered index in transactional replication - Hi, I am planning to run a script to rebuild highly fragmented clustered indexes on tables in transactional replication. Is...

SQL Server 2005 : Business Intelligence

MDX query parameter passing issue - Hi everyone, i have a requirement i.e if i select any value in areport filter for week example 45, then...

SQL Server 2005 : Data Corruption

corrupted mdf file - hello: i was given a corrupted mdf file, the server the database (sql 2000) crashed and the only file recovered...

SQL Server 2005 : Development

Copy 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...

SQL Server 2005 : SQL Server 2005 General Discussion

HI can any one say what is the use of these in detail - HI can any one say what is the use of these 1 SET SHOWPLAN_ALL ON 2 SET SHOWPLAN_ALL OFF 3...

A simple query is executing slow and taking long time to execute - HI all, A simple query is executing slow and taking long time to execute but it has to execute in 2to...

SQL Query Notification SELECT Query - Hi folks, I'm currently writing stored procedure which contains SELECT query supported by SQL Query Notification. What's strange is that when...

Help MS SQL Join Problems - I am by no means a SQL master. I am trying to access data from two tables that are relational...

Automated e-mail from sql server 2005 when the CPU utilization is high - Hi Friend, Is is possilbe to have an automated e-mail from sql server 2005 when the CPU utilization is high to add...

Will SQL Server 2000 run on Win Server 2008 R2? - Don't beat up on me, but we still run SQL2K on Win Server 2003. It pretty well meets our needs...

SQL Server 2005 : SS2K5 Replication

sql server replicatoin requires actual server name to make a connection to the server - I have two servers at different location In both servers sql2005 is installed and have the same database instances I wanted to...

SQL Server 2005 : SQL Server 2005 Performance Tuning

high memory usage - hello experts, please go through the below configuration:- sql server 2005 sp2 windows server 2003 R2 (64 bit) CPU 2.13 GHz RAM 128 GB max...

need one doubt clarification application team trying to execute a simple query - hi all need one doubt clarification application team trying to execute a simple query but its taking long time i tried evry thing suppose if...

SQL Server 2005 : SQL Server 2005 Integration Services

high memory usage - hello experts, please go through the below configuration:- sql server 2005 sp2 windows server 2003 R2 (64 bit) CPU 2.13 GHz RAM 128 GB max...

Data Import from CSV file - I have a CSV file which is having record like mentioned below. [code="other"] UserName User Id Type RamKumar ram MSAD RamaC rama MSAD RamesK rames MSAD Rameshv ramesh MSAD RamKumar ramp PCG Ramig rami ...

Destination data checking - Hi, Destination (OLE DB) has two table with existing data. (table1 : Person with Bonus and Table2: Person without bonus) The requirement is...

SQL Server 2005 : T-SQL (SS2K5)

Update Table with Join NOT RBAR - Hello, My problem today is that when this company started they had no standards for data entry. So one person could...

SQL Server 2005 : SQL Server Newbies

Failed Simple Maintenance Plan only when Full Backups do not run - Hello all. Let me start off that I'm new to SQL administration and have inherited an older server that serves the...

SQL Server 7,2000 : Security

db_datareader permission for a login - we have 15 Database in our server how can i give at a time db_datareader permission for all DB 's...

SQL Server 2008 : SQL Server 2008 - General

sql server express 2008 express wont start - Hi, this morning sql server 2008 express installed on my dev machine won't work, worked fine yesterday the service wont start...

Network ip - is any query to find the network IP , OS version etc by using management studio

Blocked Process Alert issues - This is really beginning to annoy me. I have Blocked Process Alerts on every single server in our environment. The alerts...

Calculated for the two columns of the table - I have two columns. Name column 1 is F1 Name column 2 is F2 I would calculate two columns And the third field to...

Database master key and symmetric keys protection - Hi guys, A quick question about encryption: What is the reason why the database master key can not protect symmetric keys directly...

Amount of data - I was wondering if there is a way to "trap" or track the amount of data (in bytes) that a...

Backup Overwrite - I have a requirement where i need to take daily backup with datetime stamp , the issue is we have limited...

how to write a stored procedure to check if the members membership has expired - i have a table named users where i have two three column userid, email, membershipexpiry i want to write a stored...

The new role of the DBA - Good day to you all . I am observing a very unhealthy state of affairs in the database environment . Let me...

Monitorear las inserciones y transacciones en base de datos - Hola que tal. Quisiera que me ayudaran por favor. Necesito monitorear las inserciones que se realizan en las tablas de...

LIKE with and without wildcards in WHERE clause - I have a set of pretty well tuned queries with indexes and all the appropriate trinkets. This is a 'last...

Unable to kill a session - Hi, When we kill a session, we get the below error. What is the fix for this? In the backend(sp_who2) it...

MAX behavior difference with nvarchar and varchar column types - Hi All, We are getting different results for MAX function with nvarchar and varchar columns. create table my_table(id1 nvarchar(10), id2 varchar(10); insert...

How to check mirrored database - Dear, I have implemented database mirroring with automatic failover option. My principal database is a busy one. Now I am not...

View image stored in VARBINARY(MAX) column in SQL SERVER 2008 - Hello, How to select and view the .jpg image stored in VARBINARY(MAX) column in SQL SERVER 2008? Thanks!

rebuild index worsens query performance - we have a query which runs several times a day reads from several tables in one database ("W"), and two tables...

Dependent Delete statements - i need to delete the records from a table with multiple conditions , like below first i am clearing non numeric...

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,...

SQL Server 2008 : T-SQL (SS2K8)

Delete records - I have table with 20k records. I want to delete 20k-1000 records from the table. i.e keep the top 1000...

findingdifference between full or partial index scan in graphical execution plan? - hi all, many times when looking to graphical excution plans I see the operator "clustered index scan". The description says "scanning...

Update null to unknown in a table for all the columns - Hi, i need query to update all the columns which ever carrying null to 'unknown' using a single query or procedure...

self joining a table to get Parent Child relationship - Hi, I need some assistance with this regard. I am trying to figure out how i can Join a table to...

string with int - pretty basic so appreciate the help here,getting conversion error , what is the way to have int variable in the string? declare...

Merge Statement over a linked server - Does anyone know a way of running a MERGE statement across 2 tables over a linked server? Getting the message "The...

read UNCOMMITTED run slower than read COMMITTED - Hello I have the following query which runs considerably slower when we change the isolation level to read uncomitted, I just...

multiple columns to single column (normalization) - Hi, I'm working on normalizing a small part of our DB, but having trouble building a particular query that would...

xml in subselect and performance => alternatives? - hi there, many times there is the requirement of selecting multiple records into one xml-string. Usually you do this using a...

Update statement question - I'm doing a DB review and have always been taught to write updates with a Join like using this syntax. [code="sql"] Update...

switching address with Subtring & len funtion - I have something out of order what is it I'm not sure, I want to reverse the field(s) Doe.John@CompanyABC.com to ...

Converting a string to a date - Hello everyone, I have 3 fields, all text strings but all supposed to represent dates. Field 1: varchar in the...

SQL Express - Create maintenance plan to run truncate table - Hi - I am new to SQL express, can someone please guide me to how I can schedule a task to...

SQL Server 2008 : SQL Server Newbies

Memory Clerks - MEMORYCLERK_SQLBUFFERPOOL - Hi Guys Please help me understand why the following Query returns 0 for the Single Page allocator on my system for...

SQL find maximum of table generated with select - Hi guys. Newb in SQL with what i hope is an easy one: SELECT ACCUM.c1 FROM (SELECT t1.c1, SUM(t1.c2) as adding FROM Table1 t1 GROUP...

Alternative to views - Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2...

html from sql server 2K8R2 - Hi all, I am trying to generate an HTML-formated email from sql server. I need to be able to control the...

Date Format in SSRS - I have the date time field. I want extract only Month and the year. How can I do the same. Current...

SQL Server 2008 : Security (SS2K8)

Limit Concurrent Logins by database and/or user ID - In my environment I have a sql server 2008 database on 64bit architecture. The production database accessed by users is...

SQL Server 2008 : SQL Server 2008 High Availability

Regarding Cluster Failover - Hi All, I was troubleshooting a connectivity issue for a cluster server. SQL 2k5 on Windows 2k3. Found an instance is down, manually...

SQL Server 2008 : SQL Server 2008 Administration

need script to set Port number - need script to set Port number .

permisssion - Hi, How do I make the users group i.e. domainname\users to be able to only select from tableA but user1 and...

witness server - with out witness server can we set up mirroring?

Why does my log show two backup events for the same database? - So looking over logs today, I saw the following: [code="plain"]11/20/2012 6:02:52pm - Backup - Database backed up. Database... Pages dumped: 9218 11/20/2012 7:00:13pm - Backup...

SQL Server Backups - This is more of a poll but I'd like to get others opinions. What is the recommend and preferred methods...

Programming : XML

XML DOM - The XML DOM defines a standard way for accessing and manipulating XML documents. The XML DOM views an XML document as...

SQLServerCentral.com : Anything that is NOT about SQL!

Career Advice Needed - I have an interesting dilemma, and could use some advice. I have spent the last two years as a Network...

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...

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...

Reporting Services : Reporting Services

Chart not shown in report manager - Hi all, after reinstalling windows 8 and sql server 2012 I have the problem, that charts are not showing in report...

How to Change rowcolor on toggle SSRS? - Hi All, I am new to SSRS, I want to change the rowcolor in the SSRS report based on the toggle...

Generate one PDF per record - Hi all, I've built a report which displays data from a db. This works fine, rendering the report correctly. My query returns...

Auto Generate Columns? - Guys, Some of the Devs I work with have coded certain pages such that columns are automatically generated on an aspx...

Cannot connect to Reporting Services with Managment Studio - I've just set up a new instance of SSRS 2012 and I'm unable to connect to it through Management Studio....

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...

Reporting Services : Reporting Services 2005 Administration

http://localhost/reports gives error: Object reference not set to an instance of an object. - hay m using windiows vista home edition and IIS7.5 when i open http:// localhost : - it displays the home page when i open http://...

Database Design : Disaster Recovery

Backup strategy for replicated DBs - I'm going on an extended holiday soon. (NZL :-D) It's so happens that I had to restore this week. Combining the 2...

Current best practice wrt very large databases (100GB) + wrt Backup / Restore / Archive ? - Envrionment: 1. 1 Production server (VMWare, Server 2008, SQL Server 2008 R2 Enterprise) 2. 1 SAN ( SQL server storage allocated 3.5TB (600GB...

Data Warehousing : Integration Services

Heeeelp !!!!!!!!!!!! SSIS Incremental loading. - Hi All, Excuse me for my bad english. I am newbie in SSIS. I need your help. Configuration: sql server 2008R2. I have ssis package...

SSIS process fails as a job but not when manually executed - Hi I have a SSIS process that is failing on a data pump step when it is executed as a job....

What is the best approach for defining server name in OLE DB connection for SSIS package running in different environments? - Hello, I am using SSIS in SQL Server 2008 R2 and have been creating packages for a few years, but have...

SSIS Package not importing particular rows to sql server - I have a package that imports CSV files into SQL server, however certain rows are not importing. As soon as...

Execute Process Task - Not returning exit process code from SQLCMD SQL Script - Hi I have a SSIS Package developed in 2008 R2 BIDS environment that includes a process task to execute a SQL...

Data Warehousing : Analysis Services

MDX for last year values - Hi, I am using SSAS 2005 and trying to create a MDX for the hierarchy Fiscal Period (Year->Quarter->Month->Day). The user will...