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

Antipathy for Entity Attribute Value data models

David Poole from SQLServerCentral.com

EAV models have their uses but the costs are often hidden and if not hidden, more than anticipated.  More »

VARCHAR (MAX), NVARCHAR (MAX) and NTEXT Data Types

Additional Articles from SQL Server Performance

Despite NTEXT and TEXT being deprecated in SQL Server for some time they are still both used in production systems. In this article, I will briefly demonstrate the difference between a VARCHAR (MAX), NVARCHAR (MAX) and the NTEXT data types, and the impact on performance from using NTEXT/TEXT. More »

SQL Saturday #185 - Trinidad

Press Release from SQLServerCentral.com

A free day of SQL Server training in a beautiful location on Jan 25.  More »

This editorial was originally published on April 15, 2008. It is being re-run as Steve is on holiday.

The other day I was observing a developer interacting with the end-user, who was asking for a particular enhancement to an application. Actually it was a a couple years ago, but I'm just getting to this.

The user had a request to change the way that a process worked, but adding a feature. The end-user was explaining that this small change (to him) would produce a lot of time savings.

The developer listened to most of it and then proceeded to explain that the system wasn't built this way and that it wasn't possible. As the end-user tried to explain further how this change would greatly speed up the process and make the application much more useful, the developer continued to give technical reasons why things weren't built this way, how it would cause problems and take a long time to get working. Eventually the end-user kept suggesting smaller or related changes and the developer finally agreed to make a few changes.

As I watched, I was struck by how painful it was for the end-user to get the developer to work with him and agree to make a change.

When I built applications, I was always a glass half-full developer. I felt that I could do anything (given enough time and money) with an application and worked hard to make my applications fit with users. My goal and desire was to make the end-user happy and I would make suggestions as we talked, trying to find solutions to their problems, and getting myself excited about building the application.

Now in this case it might have just been this developer or even this change, but I've seen this over and over from many developers. It seems that there are more and more developers who find reasons why things can't be changed, even valid reasons, without having the brainstorming and excitement to help the end-user solve the problem.

Maybe it's a work overload, and maybe it's a sign of the times, but try to remember that whoever your client is, an end-user or the developer you help as a DBA, you should look to help them. Even if you shoot down their idea or suggestion, give them an alternative. Work with them to get things done, find an alternate, or even revisit your assumptions about why you don't want to do things their way. Discount their solution, but consider the idea or results they want and make it happen.

Remember it's the drive and determination that allow humans to achieve almost anything, so try and recapture that the next time someone asks you for help.

Steve Jones from SQLServerCentral.com

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

The Voice of the DBA Podcasts

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

Overall RSS Feed: or now on iTunes!

• Windows Media Podcast - 27.8MB WMV
• iPod Video Podcast - 22.9MB MP4
• MP3 Audio Podcast - 4.8MB

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.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

In SQL Server 2012 Developer Edition, I execute the following T-SQL statements

DECLARE @date VARCHAR(50),@I INT;

SET @date = '2016-01-01 13:16:43.350'

SET @I = 0

WHILE @I < 12
 BEGIN
  SELECT DATENAME(Weekday,(SELECT EOMONTH ( @date,@I ))) + ', ' 
  + DATENAME(MM,(SELECT EOMONTH ( @date,@I )))
  + ' ' + DATENAME(d,(SELECT EOMONTH ( @date,@I ))) +','
  + CAST(DATEPART(YEAR,(SELECT EOMONTH ( @date,@I ))) AS NVARCHAR(4))

  SET @I = @I + 1
 END

The questions are as given. Select the correct answer(s). Choose 4

By bitbucket

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

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

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset

As the most influential thought leaders in the data warehousing and business intelligence industry, the Kimball Group has developed pioneering techniques that serve as industry standards for DW/BI system design, development, and management. With this new edition of their bestseller, veteran experts from the Kimball Group get you up to speed with using the new Business Intelligence release of SQL Server: SQL Server 2008 R2. Covering the complete suite of data warehousing and BI tools that are part of SQL Server 2008 R2, the authors follow the full project lifecycle, including design, development, deployment, and maintenance. Get your copy today from Amazon.

How many row(s) are affected by the following code in the estimated execution plan?

Begin Transaction
   SET QUOTED_IDENTIFIER ON;
   SET ARITHABORT ON;
   SET NUMERIC_ROUNDABORT OFF;
   SET CONCAT_NULL_YIELDS_NULL ON;
   SET ANSI_NULLS ON;
   SET ANSI_PADDING ON;
   SET ANSI_WARNINGS ON;
COMMIT

Note: I only tested this in SQL Server 2008 SP3

By Dave

Answer: 1

Explanation: After displaying the estimated execution plan, the Messages tab reveals the (1 row(s) affected) message. As Steve indicated in his edit to the explanation of the first question in this series, there doesn't appear to be documentation of this behavior. I suspect that either the entire transaction is considered 1 affected row or 1 row in a system table was affected as a result of estimating the execution plan.

Ref: First discussion - http://www.sqlservercentral.com/Forums/Topic1389492-2739-1.aspx

» Discuss this question and answer on the forums

SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.

Pick up your copy of this great book from MVP Glenn Berry at Amazon today.

Finding out whether you are using enterprise level feature

Rahul Singh from SQLServerCentral.com

The query tells whether sql server enterprise edition is using any enterprise level features or not  More »

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

SQL Server 2005 : Administering

cluster index and non-clustered index - [b]can any body answer this question ? In what cases developer use cluster and non cluster index . suppose 40,000 rows are...

cluster index and non-clustered index - [b]can any body answer this question ? In what cases developer use cluster and non cluster index . suppose 40,000 rows are...

MSSQLSYTEMRESOURCE NOT IN DATA DIRECTORY LIST - We are planning to migrate our system DB (SQL 2005) on our cluster environment. We followed the instruction given by...

Hyperthread or not? - We're still on sql 2005 enterprise 64-bit with plans to upgrade to 2012 later this year. We have one main...

sql agent job hangs without any errors or warnings - Problem: we have a SQL Agent job which runs everyday in the morning at 4:00 am. This job hangs sometimes without...

Cannot generate SSPI - Error message shown in the logs is : Approva.ApplicationBlocks.ExceptionManagement.BaseApplicationException: Error occurred in Openconnection in SQLServer. ---> System.Data.SqlClient.SqlException: Cannot generate SSPI context. ...

SQL Server 2005 : Backups

Backup file size is 0 KB - Hi there I've backed up a database and called the backup file SQLDBName-MD-20100729-15:300.bak. SQL Server Management Studio says the backup took...

SQL Server 2005 : CLR Integration and Programming.

SQL CLR Types vs Native .Net Types? - I'm looking for some guidance as to when to use things like String Vs SqlString in CLR functions. From what I...

SQL Server 2005 : SQL Server 2005 General Discussion

Barcode 128 generator (using scalar function) - I'm looking for a barcode generating Function that uses the standard Code128. Apparently no function that fixes this has been...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Tasty Performance scenario to feast on, looking for general feedback and theories only - Looking for some ideas on a performance scenario we are seeing in production. I can't make any changes to production...

What process is consuming my VAS Reservation - I am encountering an error on one of our environments where further logins are not allowed anymore. Only way to...

SQL Server 2005 : T-SQL (SS2K5)

Using PATINDEX to find all numeric values - Hello, I forget. How can PATINDEX be used to find column values where the data is all numeric? I've tried, the...

SQL Server 7,2000 : General

Compare two databases - I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is...

SQL Server 7,2000 : T-SQL

Validating TSQL Conventions - I have a set of conventions for my database. for e.g.: select shall be always SELECT and lower case 'select'...

SQL Server 2008 : SQL Server 2008 - General

SSIS Package Error - Hello Everyone I know this is a SSIS Package issue. But I cannot find a category for "2008 SSIS Packages" I have...

Moving Datab from one datafile to another - Hi All, We have one database with 3TB of size, with 6 Data files and 2 Transactional log files. Due to...

sql security - pls any body give some issues regarding sql server security like some login failed error messages Thank you

SQL Server does not exist or access denied - hello all. I run this: declare @h int,@objDBC int,@src varchar(255), @desc varchar(255); EXEC sp_OACreate 'ADODB.Connection', @objDBC OUT EXEC @h=sp_OASetProperty 16711422, 'ConnectionString', 'Driver={SQL...

sp_OAMethod - hello all. I use this: declare @h int,@objDBC int EXEC sp_OACreate 'ADODB.Connection', @objDBC OUT --------@objDBC =16711422 EXEC @h=sp_OASetProperty 16711422, 'ConnectionString', 'Driver={SQL Server}; Server=WIN-00UO3RQ0BM3; Database=MaliGilan; trusted_Connection=Yes' ...

facts abt sps,views and functions - I just want to know whether or not function can call system or userdefined sps? Similarly can userdefined sps can call udf...

Database size keep increasing size massively.. - HI my database is keep increasing size very massively. On server i have only 1 database and database have around...

Declare error message before CATCH? - In doing code review, I suggested to a developer to move their error variables that were used in the CATCH...

function erroring and reporting wrong name - Hi All, I have a weird one for you that I was hoping to get some help with. I created and deleted...

Unable to install sql server 2008 Management studio - Dear Sir/Madam, I downloaded Visual Studio 2010 from microsoft so its automatically installed sql server 2008 R2. In Sql server 2008...

Insert multiple lines in a single row - Dear, I want to insert multiple lines in a single row in SQL Server 2008 R2. I have tried. But it's...

Problem with using SQL Server linked server to MySQL database - I have a MS SQL Server that does a lot of data manipulation and staging and then I need to...

SQL Server 2008 : T-SQL (SS2K8)

First Stored Procedure - I have written my first stored procedure to try to pass a table name in a variable to SSRS. USE XXXX SET...

I have a question about GROUPING SETS and the () element. - What does the () signify with grouping sets of multiple elements? An example on Microsoft.com, (http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx), read the following T-SQL code: GROUP...

Problem with CAST to VARCHAR with SUBSTRING Function - I'm haveing trouble with a simple CAST to VARCHAR Statement. [code="sql"] SELECT CASE WHEN CAST(LEFT(Customer.STARTDATE, 2 AS VARCHAR(2))) = '98' THEN CAST(substring(Customer.STARTDATE, 3,...

Word analysis with SQL - I did a blog post earlier called [url=http://svangasql.wordpress.com/2012/11/12/pass-summit-2012-twitter-dashboard/]PASS Summit Twitter Dashboard[/url]. It shows all tweets by people at the PASS...

Need a backup script to get backup history status for the latest backup from all linked sql servers - Hi Guys, I am actually in need of a t-sql or a powershell script that will help me get the latest...

SQL Server 2008 : Working with Oracle

where to find dbservername when connecting to oracle from asp.net - hello i have a rather novice question but i cant figure it out myself. when i try to modify web.config...

ORA-12592: TNS:bad packet or REB_REPSER ORA-03113: end-of-file on communication channel - Hi guys, recently i need to start to look after some of the linked server query . i got a instance...

SQL Server 2008 : SQL Server Newbies

User defened Datatypes Default values. - Dear All I would like to know the syntax of adding Default value during UDT creation (Rather than by using...

DBF to SQL on daily basics - Hello, I need to import to my SQL server database information from DBF files every day, to do this I...

SQL Server 2008 : SQL Server 2008 High Availability

Changing compatibility - Is it fine if we change compatibility level of a Database which is in Logshipping from 80 to 90 in...

replication issue - ''replication cannot do anything'' i have Transaction replication with pull subscription. There is no error in replication showing all success. below message...

Monitor Log Shipping - Monitoring Log Shipping My client in Manhattan maintains a disaster recovery server in South Brooklyn. We employ log shipping to sync...

Logshipping Query - Logshipping is out of sync for 2 days and so i have a huge amount of logfiles to copy and...

SQL Server 2008 : SQL Server 2008 Administration

check traces - Is there a way to find out what traces are enabled in a sql server instance? and also what is the...

Trace Flag 1118 - Please share what helped shape your point of view on whether or not to enable TF 1118 in your SQL...

Tempdb and disk - I am reading a tutorial of Randal Paul that says, Checkpoints don't flush tempdb data to disk. Log records are...

How to manage log space issue, when you have more than 1 log file for a particular database? - Hi All, If you encounter log space issue and if you have more than 1 log file for a database, how...

create a policy in policy management - How to create a policy that prevents users to create user objects in master database? Thanks

Data Warehousing : Integration Services

NVARCHAR(MAX) TO NText - Hi, the problem I'm having is when I'm passing NVARCHAR(MAX) output parameter from a stored procedure to a SSIS derived...

Data Warehousing : Analysis Services

MDX trouble in a calculated member - Hi to all. I use SSAS 2008 R2 and i built a cube. In this cube i have a little trouble with...

MDX Query - Little TROUBLE with a calculated member - Hi to all. I use SSAS 2008 R2 and i built a cube. In this cube i have a little trouble with...