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

An in-depth look at change detection in SQL Server - Part 02

Nakul Vachhrajani from SQLServerCentral.com

Auditing, conflict resolution, tamper & concurrency protection are some of the most common requirements for any enterprise system – this 2-part series presents an in-depth look at the various change detection mechanisms available within SQL Server. More »

SQL Bits XI

Press Release from SQLServerCentral.com

The popular UK conference heads to Nottingham from May 2-4, 2013. Both Grant Fritchey and Steve Jones will be speaking, along with lots of talented SQL Server professionals. Register today. More »

SSIS for Azure and Hybrid Data Movement

Additional Articles from TechNet

SQL Server Integration Services (SSIS) can be used effectively as a tool for moving data to and from Windows Azure SQL Database, as part of the total extract, transform, and load (ETL) solution and as part of the data movement solution. SSIS can be used effectively to move data between sources and destinations in the cloud, and in a hybrid scenario between the cloud and on-premise. This paper outlines best practices for using SSIS for cloud sources and destinations and for project planning for SSIS projects to be used with Azure or hybrid data moves, and gives an example of maximizing performance on a hybrid move by scaling out the data movement. More »

From the SQLServerCentral Blogs - A Rickety Stairway to SQL Server Data Mining, Algorithm 7: Clustering

SQLServerSteve from SQLServerCentral.com

by Steve Bolton                 In last week’s installment of this amateur series of self-tutorials on SQL Server Data Mining (SSDM), we... More »

This editorial was originally published on Nov 10, 2008. It is being re-published as Steve is out of town.

It is late evening. Something is wrong with a database. You narrow down the possibilities, getting more frustrated and puzzled. Stay calm. Check the inputs systematically. No! The data going into that table is right, but when you then read it in the table, it's wrong. Why did I stop believing in the supernatural?

Then it hits you. Every time it comes as a surprise. They're using triggers.

I've always been big fan of stored procedures, and of implementing data-centric business logic in the database, where it need only be implemented once, and cannot be circumvented. I am much less convinced, however, of the value of triggers.

Show me a constraint, or a rule, and I'm all smiles. The only time you know they're working for you is when you need to know. Triggers, on the other hand, may have outlasted their usefulness. They were devised to ensure referential integrity in the bad old days before constraints. There is little need for them now unless you are unfortunate enough to allow direct access to the base tables.

Triggers are not only difficult to test and debug, but they are also masters of the art of concealment. They are not called explicitly, they just "happen" as a result of other code executing. It is very easy to fail to spot them in the SSMS Object Browser, and the old Enterprise Manager seemed to deliberately conceal them. They are easily hidden away in the DDL and forgotten about. This leads to "unexpected behaviour" and a maintenance headache. Triggers also have a reputation for bad performance with some DBAs, possibly due to their "encouragement" of line-by-line rather than set-based processing.

I don't like to see triggers being used to compensate for a lack of basic integrity control in the database, via appropriate use of keys, check constraints and so on. I think Joe Celko was spot on in his recent Constraint Yourself article, when he suggested that not using all the CHECK constraints you can is a big mistake. They are there not just for data integrity but are also used by the optimizer and can result in better query plans and performance.

Last year at PASS I heard one person proudly proclaim that we "like our databases dumb!" He, like many others, wanted to avoid putting any logic in the database in favour of placing it all in a middle-tier layer. This, they argue, leads to a more scalable and portable architecture.

Would you agree with the wild men of the 'Dumb Database' persuasion? Would you still use triggers to implement business logic when building new applications? Or is Conor Cunningham right that most designs that implement triggers are "legacy" ones.

Finally, speaking of PASS, SQLServerCentral will be at the event again this year. I look forward to meeting some of you at the SSC party! If anyone is interested in saying hello, I'll see you there, or you can drop me a mail at "tdavis at sqlservercentral.com" to arrange a different time.

Cheers,

Tony Davis.

Tony Davis from SQLServerCentral.com

» 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

Today's Question:

Objective: Update all the M's to F and all F's to M.

-- #1 Table
CREATE TABLE GenderUpdate
(
ID SMALLINT IDENTITY,
Gender CHAR(1)
);

-- #2 Data
INSERT INTO GenderUpdate (Gender) 
VALUES ('M'), ('M'),('M'),('M'),('M'),('M'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F'),('F')

-- #3 To check the data count gender wise
SELECT Gender, COUNT(gender) [COUNT] FROM GenderUpdate GROUP BY gender

-- #4 Final update with case statement
UPDATE GenderUpdate
 SET Gender = CASE WHEN GENDER = 'M' then 'F' ELSE 'M' END

By Raghavendra Mudugal

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

This question is worth 1 point 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.

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

Given the following code:

USE tempdb;
go
CREATE LOGIN SSC_Question 
  WITH PASSWORD = 'SSC', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

CREATE USER SSC WITHOUT LOGIN;

ALTER USER SSC WITH LOGIN=SSC_Question

DROP USER SSC

DROP LOGIN SSC_Question

Will the alter user statement succeed?

By Ole Kristian Velstadbråten Bangås

Answer: No, login cannot be assigned to a user without login

Explanation: The WITH LOGIN clause enables the remapping of a user to a different login. Users without a login, users mapped to a certificate, or users mapped to an asymmetric key cannot be re-mapped with this clause.

Ref: http://msdn.microsoft.com/en-us/library/ms176060.aspx

» Discuss this question and answer on the forums

SQL Server 2012 T-SQL Recipes: A Problem-Solution Approach

SQL Server 2012 T-SQL Recipes is an example-based guide to the Transact-SQL language that is at the core of SQL Server 2012. It provides ready-to-implement solutions to common programming and database administration tasks. Learn to create databases, insert and update data, generate reports, secure your data, and more. Tasks and their solutions are broken down into a problem/solution format that is quick and easy to read so that you can get the job done fast when the pressure is on. Get your copy from Amazon today.

Script to Rebuild/Reorganise Indexes

Andrew Pruski from SQLServerCentral.com

A script to get the fragmentation of indexes in a database and then rebuild or reorganise accordingly More »

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

SQL Server 2005 : Administering

Management Studio - Copy Database - How to set Default Folder - Using the Management Studio Copy Database Wizard - is there a way to change the default destination folder for database and...

Problem in avg_page_space_used_in_percent value in fragmentation - Hi, I have some confusion so i required some help. I am using below statement in sql server to reduce fragmentation [b]alter...

Any thoughts on this process? - This is more of generic programming question. We have a stored procedure which is used on many of our applications...

Copying Databases - Error - TempDB - Error: 823 Severity: 24 State: 7 - I'm trying to copy a few databases within the same server. Four of the database copies worked while two will...

How to configure mirrored DB on ODBC for failover - Hello, I am having a question on configuring mirror DB on ODBC. When a Principal DB goes down the mirror DB...

Different between Application DBA and Production Support DBA - anyone knows the different between Application DBA and Production support DBA? In my understanding, production support is the person whom...

SQL Server 2005 : Business Intelligence

How to Compare Data between to servers based in Id's - Hi All, I have a requirement like, Identify the out of sync records from two servers. I have Id's to pass...

expression output - Hi, can any body tell the output for expression by taking any 2 dates as example. =cdec(DateDiff(DateInterval.Day,Fields!Date.Value,Fields!Date2.Value))*(-1) Thanks, Niha

SQL Server 2005 : Development

Android Development - Hi, i am newbie to Andorid development .. suggest me good material to this journey thanks

SQL Server 2005 : SQL Server 2005 General Discussion

Issue with OSQL - Hi Friends, Requirement : i have a table which contains around 40 columns of which name is a column which i use...

SQL Server 2005 : SQL Server 2005 Performance Tuning

Performence on the basis of Column Names. - Is there will any impact on the performence if the name of the temp table/table variable column's like [30-40], [Weightage...

SQL Server 2005 : SQL Server 2005 Integration Services

Multiple Ole db sources to a single flat file destination - Hello friends, i have requirement where i have take data from different tables of sql server db, but here the important...

When does a variable get evaluated - Guys, I have a package scoped variable which gets its value from an expression: replace((DT_WSTR, 30)(DT_DBDATE)GETDATE() ,"-","") I use this variable to...

SQL Server 2005 : T-SQL (SS2K5)

Weird Question About Querying with Dates - Okay so this is a very odd situation, one that I have not seen before. I'm trying to query a...

In a Jam - Need Some Help - Hello, I'm urgently working away at this but not able to solve my problem. I'm at the point of needing...

calculating ratios - Hello, I am trying to calculate a ratio, i am pretty sure the problem is in the line * SUM([SUMTOTALVOLUME])) FROM...

Database Design question regarding Normalized Tables - Hey all, Got a theoretical question for you guys. Let's say I have a table that contains information about a store....

SQL Server 2005 : SQL Server Newbies

SQL Suppression code FOXPR0 2.6 DOS - Hello, We need to write a few lines of sql that will compare one file (as dbf) to another. Then output...

SQL Server 7,2000 : Administration

Admin Office Asst/ Secretary/ Clerical ! - :-) Automotive Internet company looking for an Office Asst/ Secretary Asst. Must have good computer skills, word, excel, Outlook, ETC. Data...

SQL Server 7,2000 : Data Corruption

601: Could not continue scan with NOLOCK due to Data movement - [b]SCENARIO 1[/b] Hi, i'm getting the error msg 601: Could not continue scan with NOLOCK due to Data movement? - from...

SQL Server 7,2000 : T-SQL

using locks in sql server 2005 - i have one table, concurrently multiple users are hiting the table through insert action. i have placed trigger on the table...

How to calculate total working hours of all the employees in specified dates. - Hi, I'm a learner, please help me by solving the following issue. I have data of all employees whose login/logout times are...

SQL Server 2008 : SQL Server 2008 - General

Merge - Hi, If two users are performing same operation then the below stored procedure provides the output based on single context(combination of...

setup an alert for DB data file full - Hello I need to set an alert when Database data file will exceed 90% or less then 5GB in SQL Server...

SQL SERVICE - what are the possible reasons for the sql services are not up ?

SSIS rounding decimals - Hi, I am importing several csv files into a database. The CSV files contain data in a decimal format, accurate to...

SQL Server 2008 cluster upgrade - Hi Team, We have SQL Server 2008 active-passive cluster. We got requirement to upgrade it SQL Server 2008 R2. Can you...

Need the details of the following concepts use in reporting services - Hai to all Iam deploying a code inthe reporting services which gives the grap of the following I want to Know...

Can any one please Share the 70-450(SQL Server MCITP-2008) exam dumps - dfg

alerts - i need to configure alerts on the servers as part of DBA ,can any one send me the scripts to...

SQL Data warehouse poor performance - Hi All I require help with a performance issue we currently having. We have a data warehouse which processes and downloads...

How to Refresh Database from Production to DR - Hi I am very new in SQL SERVER...... I have two database in two difference instances. Database A and Database B Database...

Restore database + replication - Hello! I have a following situation: There are 2 SQL Servers with same databases (one in production env and another for developers). Sometimes...

Removing Duplicate Values using Merge statement - i have one issue i have one merge statement in my procedure :but after merging i am getting duplicate values...

SQL compression - Hi I am looking for software that will do compression on MS SQL data files. I had a look at Red-Gate...

sp_rename - Hi All, I need to use sp_rename - [url]http://msdn.microsoft.com/en-us/library/ms188351.aspx[/url] I need to use it for tables in the following way: For an existing "table_name"...

Multiple SQL servers Consolidation to one Single SQL 2012 Cluster - Hi, Experts. I have a project which includes different 180 databases on around 18 different SQL servers with different versions (2000,2005,2008,2008...

Newb with SQL in school learning - class project help - Okay here is my situation, I have a database that I had on my website and I want to use...

Export all tables of database to Excel - Hi to everybody How can I export [u]all tables of a database [/u]to one excel file (each table should be one...

What is the best way to access a sql server in another country using the public ip address? - I can access my sql server when both of my computers are connected to a same router. However, when one...

Memory Question - Quick one I probably should know this but.... Scenario 1 SSRS server with 16b GB mem. SQL set to MAX 10GB SSAS set to...

SQL Jobs running after disabled? - Hi, SQL Jobs running after disabled, what could be issue? where should be troubleshooting? I have done as below steps. 1. Disabled the...

Waring alters when the database (files MDF, NDF & LDF) reaching the MAX size. - HI Frnds, I need help on setting up alters when the database (files MDF, NDF & LDF) reaching the MAX size....

Display on Month and Year from date formula ?? - Hi, I want to display only MM/YY from the formula of @enddate -21?? Thanks

Clustered Key. (Making it unique). - Using: (sp_SQLskills_SQL2008_helpindex: provided by Kimberly L. Tripp). (sp_help) drawing_eid (identity field) [code="plain"]index_keys: [client_eid], [drawing_eid] columns_in_tree: [client_eid], [drawing_eid], UNIQUIFIER[/code] Why is de UNIQUIFIER there? (With drawing_eid as identity...

Average memory per query - Hi All, I want to know the average amount of memory spent per query, or the memory spent for each query...

DWH : Fact table : index creation. - Below is the schema of my FACT table table : Fact.POLICY Size : 165 GB [quote] Policy_transactional_ID (surrogate key ) Policy_ID Audit_Trail_ID (FK) Journalization_ID (FK) Program_ID (FK) Product_ID (FK) Producer_ID...

All Databases Data & log file size, space used & free space loading into a Table. - I am working on Capture database growth trends on a server. I got little nice script from below Link and...

Split VLDB into Multiple data files - We have an 600gb database as a single mdf and ldf. Due to various reasons, we are planning to split...

How to match a UNICODE NULL character within an nVarchar(128) string? - Our content has been contaminated by UNICODE NULL characters in our SQL Server 2008 database that don't play nicely with...

Use an AD group for SQL Authentication - Hi Guys, Our Syspro system uses SQL authentication to connect the users to the database. We're migrating that server and I...

SQL Server 2008 : T-SQL (SS2K8)

UPDATE - row by row vs batch - Guys, I'm wondering of the implications of two different update methods, say you're changing a bunch of titles from Mr to...

Get distinct values into single column - Hi - I am having trouble writing a query to get the distinct values of a column grouping into a single...

Query Advise... - Hi guys, I am not sure it is possible or not but would like to ask here. I want know...

Get number of unique values in group of columns - Hi - I have the following scenario and not sure of the best way to write the T-SQL query without a...

combining the results of multiple select statements in to a single row - All, I have a requirement to combine the results of multiple select statements in to a single row. EX : query 1 : select...

All Possible Combinations Loop - Hi, I am trying to build something that combines all values of all variables using SQL Server. For Example: A...

Issue with crypt_gen_random - This is my scalar function, which returns numbers between @min and @max (both included): [code] create function GetRandom(@min int, @max int) returns int as begin declare...

SQL Server 2008 : SQL Server Newbies

Query Help - Okay here is my situation, I have a database that I had on my website and I want to use...

Backing up & Restoring a DB - Hello, I have a db which I would like to backup and restore. Currently I go to db, right click tasks and...

SQL Server 2008 : Security (SS2K8)

Revoking public Role Permissions not working - Per my company's security policy, I need to revoke permissions to the public and guest roles to all objects. In...

SQL Server 2008 : SQL Server 2008 High Availability

XL to SQL - [center][/center]

Log Shipping Issue - I was trying to set Log Shipping on the Test server, but some how it didnt work... so now when...

Configuration sql 2008 cluster on san storage - Hi All, Iam unable to proceed with MSDTC storage configration where it fails with the folowing error: No disk exists in available...

Transactionla Replication - Very first step while configuring the Ditribution.. error comes on last step :w00t: Property LoginSecure cannot be changed or read after...

Log shipping between SQL Server 2000 and SQL 2008R2 - I am going to implement log shipping between 2000 (primary ) and 2008 R2 (secondary). I found there are regular backups are...

Cluster Installation Time Out Issue - Hi. I want to create a failover cluster and always get the following error while creating the windows server cluster : Security...

Replicate a new table without taking a snapshot - I have a 350 GB table that is replicated, and I need to rename. This will require replication to be...

Mirroring restore rate very low (< 5 MB/sec) - database mirror stuck in "synchronizing" state for several hrs - A production mirroring session started synchronizing this morning and at first the restore rate on the mirror server was at...

SQL Server 2008 : SQL Server 2008 Administration

SQL 2008 Express Syncronization - Hi, Can SQL 2008 Express be syncronized with another SQL 2008 Express instance on another server. I would like to setup...

Transaction Log Issues - Hello, The company I work for is currently using SQL server 2008 R2 SP2 Express Edition. Express Edition doesn't have the...

Configuration query for SQL 2008 - We are trying to use SQL Server Management Studio 2008 R2 to connect to a SQL Server 2012 deployment. When...

Fragmention even after rebuilding the indexes - I Checked for fragmentation on my DEV Server and it was in the late 90's. So i decided i would...

Agent Jobs Query - Hi, I would like to check each morning if my agent jobs have completed successfully. What would be the best method...

SSIS ForEach Loop Container - loop through all DB's - I am trying to get the FELC to loop through all the databases for my sql server. I want to...

SQLServerCentral.com : Anything that is NOT about SQL!

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 Services

LIKE statement with Parameter Problem - Hi, I am trying to add a LIKE statement to a query in report builder 3.0 with a parameter like...

Reporting Services : Reporting Services 2005 Administration

Problems with transactional replication to a reporting server - We are using transactional replication to our report server. There are a couple of problems that have developed over the...

Data Warehousing : Integration Services

How to automate validation process - Hi, We have the following scenario: We receive CSV files every month for which SSIS packages were built to process the...

Integration Services. Exporting to multiple CSV Files based on a Column in the table as a variable - Hi I seem to have completely forgotten how to do this and cant make it work I have a SQL Table...

Reading Data From XML through Script component - Goal : Need to pull data from XML file (800MB). Note: I tried to do with XML task and XSLT but couldn't...

configure list of email addresses in XML file - Guys, When deploying my package using XML configuration, I have a variable called [User::strEmailList] This is used to evaluate the expression for...

SSIS Config Wizard failing on SQL Server config - We have a SSIS package that has been developed in VS2008. Our company standard is to have package configuration enabled...

Data Warehousing : Strategies and Ideas

SSIS package for DWH Load - Hi All, Please suggest the best option for designing SSIS package for DWH load. Here is the scenario. I have two star schema...

Connecting to CRM 2011 Web Service using SSIS 2008 - I would like to backup MS Dynamics CRM 2011 Online using SSIS. My current code allows me to download known...

Data Warehousing : Analysis Services

Named sets in SSAS - Hi, I'm creating named sets in SSAS. I happen to be using 2012 but don't think that this has any effect...

Aggregation of an MDX calculated measure when multiple time periods are selected - In my SSAS cube, I have several measures defined in MDX which work fine except in one type of aggregation...

Calculations tab SCOPE - Hi, I'm wondering if anyone can help with some scope syntax in the calculations tab. I'm trying test the following: /*...