SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Finding Topics

I put out a call for editorials awhile back and got a few that I have scheduled for my sabbatical time this summer. I'm still looking for 10 more to cover some time, so you have the chance to submit one if you'd like to feature your own thoughts in the newsletter. Submit them here (include "Editorial" in the title).

A few people have asked for tips and tricks for writing pieces, and I thought I'd take a few minutes and publish some of mine. I get ideas from all sorts of areas: headlines, other blogs, conversations, and more. These come about both at work and in the rest of my life. Here are a few pieces I've written and the inspirations.

Due Diligence - This came out of a question asked in one of my talks. I thought it was worth sharing some thoughts on what has gotten people to interview me, or gotten me interested in candidates.

Advice for Newcomers  - I was talking at a SQL Saturday with someone that was new to SQL Server as a profession and they asked me if I had advice that would help them learn quickly and focus on areas that would help them become better at their job. That spawned this.

How Long Before You Upgrade? - I wrote a long time ago about people looking ofr 10 years out of their database servers. Every time a new version of SQL Server comes out, I'm curious about this topic.

Charge backs - This was common early in my career. I saw this post and it made me start writing about whether this was a good idea.

The More Things Change ...  - A chance encounter with an old friend inspired me to think about how our industry has changed. Or not. Much of what I've learned over the years has me thinking about data, SQL Server, careers, and more, and taking a few minutes when I read/hear/notice something and considering what that means to me. I'll often jot a few sentences down or save a link and then come back to it later.

The best advice I could give you is to try something. Write down a sentence, and then try to write a few paragraphs that support some viewpoint you have. Let a friend look at it and give you an opinion. If they like it, send it in.

Steve Jones from SQLServerCentral.com

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

ADVERTISEMENT
sqldbabundle

NEW! The DBA Team in The Girl with the Backup Tattoo

Pina colada in the disk drives! How could any DBA do such a thing? And can the DBA Team undo the damage? Find out in Part 2 of their new series, 5 Worst Days in a DBA’s Life. Read the new article now.

SQL Backup

Want faster, smaller backups you can rely on?

Use SQL Backup Pro for up to 95% compression, faster file transfer and integrated DBCC CHECKDB. Download a free trial now.

SQL Search

Have you tried SQL Search yet?

SQL Search has one job, and it does it well. Search database schemas for fragments of SQL text in sprocs, functions, views and more. Download Red Gate SQL Search - it's free!

Featured Contents

 

DAX: Topn is not a Filter

Gary Strange from SQLServerCentral.com

In this document I will demonstrate how using the TOPN function in a DAX query doesn’t necessarily do what you may expect. More »


 

SQL Saturday #292: Detroit

Press Release from SQL Saturday

SQL Saturday is coming to Detroit MI on May 17, 2014. Join us for a free day of SQL Server training and networking. Register while space is available. More »


 

Searching for Strings in SQL Server Databases

Additional Articles from SimpleTalk

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google. Besides the obvious Full-Text search, Phil Factor describes some of the techniques for finding that pesky data that resists the normal SELECT blandishments. More »


 

From the SQLServerCentral Blogs - Understanding the Three A’s of Security for SQL Server

Brian Kelley from SQLServerCentral Blogs

Do you know what the “Three A’s of Security” are and how they apply to Microsoft SQL Server? Let’s look... More »


 

From the SQLServerCentral Blogs - Real heavyweights: Float vs Decimal, the Thrilla in Precision

William Assaf from SQLServerCentral Blogs

This is a followup to a previous post where I likened SQL Server float datatype to Muhammad Ali. Specifically, I... More »

Question of the Day

Today's Question (by Steve Jones):

In a hash match join, which input is the one used to build the hash table?

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 1 point in this category: Performance.

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

Professional SQL Server 2012 Internals and Troubleshooting

The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Shanjan Sapra):

How do you ALTER computed columns in SQL Server ?

Answer: There is NO way to alter computed column. You will have to drop and recreate it

Explanation:

If we try to alter the computed column it will throw following error.

-- Create table
CREATE TABLE Table1 
( Col1 INT, 
  Col2 AS Col1 * 10
);
-- Failed attempt to alter column
ALTER TABLE Table1
 ALTER COLUMN Col2 AS Col1 / 10;

The above script will give following error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘AS’.

The best way to fix this is to drop the column and recreate it.

-- Drop Column
ALTER TABLE Table1
DROP COLUMN Col2;
-- Create Column
ALTER TABLE Table1
ADD Col2 AS Col1/10;

» Discuss this question and answer on the forums

Featured Script

Reorganize indexes

Andrés Michaca from SQLServerCentral.com

Generate script to reorganize indexes 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 2014 : Administration - SQL Server 2014

Performance issues in FULL recovery model? - Hi Experts, Just wanted to know whether there would any kind of Performance issues if I change the recovery model from...

Am using Windows Server 2008R2,In this C:/ Drive Space getting increase - Dear All, I know this is some what get irritate to you,Very sorry for that but i hope that you can...


SQL Server 2014 : Development - SQL Server 2014

Hekaton - Origin - Hi all, Someone knows the origin of the name "hekaton" because in previous editions the name was based mountains. Thanks.

Changed behavior or am I missing something? - Hi all, when porting and testing code from 2008/2012 to 2014 I noticed that some queries where significantly slower. Digging into...

Adjacency Matrix in MDS with a recursive derived hierarchy? Anyone tried this? - I'm working through a problem in MDS and I'm curious to see if anyone else has run into this. If you've...

Anyone Using or Planning to Use Memory-Optimized Tables? - I was just wondering if this feature will be used in the real world. Not being able to have identity columns...

Database and its Objects Naming Standards - I am trying to establish the standards for naming convention in my new project. Can you please provide me the...

Null instead of 0 - Friends, I have a excel sheet with some data and blank columns. I have a ssis package using to import...


SQL Server 2012 : SQL 2012 - General

Unable to connect to analysis services - Hi All, Currently we are unable to connect to analysis services and getting the below error Element 'return' was not found.Line 5,position...

Symmetrically encrypt a single node in XML field - How do I reliably symmetrically encrypt a single node in an XML field in my SQL Server 2012 database? I...

Database Design - Need help in Choosing a primary key - Hi All, I am kind of confused. I need some help in choosing a primary key for the database which i...

Execution plan display change from 2005 to 2012 - I was wondering if anyone could help me with something that I've noticed now that we've upgraded our SQL version...

Upgrading from SQL 2005 to SQL 2012 - Hello, We are planning to upgrade our SQL server from 2005 to 2012, and we also have to move our SQL...

XML Query - Objective: To insert into Mod_Primary,Mod_Secondary columns I should get all values of ModuleList element from XML and join it with Module...

SQL Query Performance: CXPACKET - Hello, I am running a query for which Waittype is CXPACKET, query does join two tables and insert the data into...

chache queries - Hi , Do you happen to know of a way to invalidate cached query plans? I would rather target a specific...

SQL in a DMZ - What is everyones view on accessing SQL data which is stored on SQL instances in the DMZ via management tools...

DBCC Shrinkfile empty file not distributing data evenly in primary file group with multiple files - Could somebody help me tell me why shrinkfile empty file does not redistribute data evenly in the primary file group...

Backup "fails" - A few months ago, I added a simple, one-line job to backup one of my SQL Server 2012 databases -- a...

SQL Design Question - Hi, I am designing a database and would appreciate some thoughts. I am thinking about creating a Telephone table to handle...

Edit script on Script Task hangs when VB is selected but opens when C# is selected - I googled and searched on this and can't find anything about this specific issue. Has anyone ever experienced this? It...


SQL Server 2012 : SQL Server 2012 - T-SQL

Choose of join algorithm - Query Optimizer - Hi all, Is there a sort of pattern for the query optimizer take a decision about the best algorithm for...

chache queries - Hi , Do you happen to know of a way to invalidate cached query plans? I would rather target a specific query...

updated Aggregate column - I am trying to create an aggregate table where the value is a rolling sum. Type a on date 1...


SQL Server 2008 : SQL Server 2008 - General

Backup questions for 612GB database! - We have a database that is over 612Gb and growing. Our backup is being done on tape using Backup Exec...

Starting over after 4 year break - This is not a technical question but I need a career advice. I worked as a SQL server DBA for...

Moving Data Across Domains - I have a scenario I'm faced with that I wanted to share and see what elegant solutions you guys could...

XML is 17GB on disk... 4MB when cast as varchar(max) - Hi folks, I have a table that always contains only 1 row which has a column defined as XML data type....

how to combine 3 different select statements into one - Hello, I am creating an ssrs report for this it should have single dataset because its grouping with system name...

Group by date query - I need to write a query which involves 3 tables. Each table has a date field. I want to run...

Min and Max Number from string - Hello everyone I need help to develop logic. In my query i have Alpha Numeric string with commas and hyphens.so from...

Should I create a new index ? why or why not - Hi All, I got confused with indexes so i really appreciate if someone clear me about it. Let's say I have...

? on Parsing more than one section of data - Hi I have some code I used to parse out a section of data in a field below. Now I need parse...

Error in SSRS 2008R2 Report : Indicator does not appear - I have a report , in Sqlserver 2008r2 containing a table which contains various items including a chart in one column...

Need Assistance for Groups and Islands Problem - [code="sql"]create table #sample (rowguid int identity ,id_frm int ,id_to int) insert into #sample values( 1,5) insert into #sample values( 7,13) insert into #sample...

chache queries - Hi , Do you happen to know of a way to invalidate cached query plans? I would rather target a specific query...

Urgent :Index Rebuild Job not working as Expected - Dear All, Problem Statement 1 :I have been using ola hallengren's script on the below link for index maintenance. HOwever I...

Sort operation in execution plan - I have two set of tables each table has unique Column and indexes. while doing a Order by operation when i issue...

SSIS data conversion error - Iam using flat file source,data conversion and oledb destination for data transfer But Iam getting the error [BEM(ETA) [161]] Error: SSIS Error...

CPU usage in percent - Hi to all. How I can get the CPU usage in percent by T-SQL query? Thank you for help.

spliting data rowwise - create table #temp ( range2 varchar(15), descrip varchar(10) ) insert into #temp select '10-13','test one' union select 'T100-T1105','test two' union select '20G-22G','test three' select * from #temp output required create table #temp1 ( range2 varchar(15), descrip...

SQL Query - CREATE TABLE OFFICES( OFFICEID INT NOT NULL, OFFICENAME VARCHAR(100), HEADOFFICEID INT ) INSERT INTO OFFICES VALUES(1,'Germany',0); INSERT INTO OFFICES VALUES(2,'France',0); INSERT...

Creating alias for a database - I have 3 databases that my OLTP application uses SalesDB MarketingDB AccountingDB I have about 50 ETL stored procedures that extracts data by joining...

Any tricks for simulating a "Connection Timeout Expired" message? - wondering if anyone, during their travels, has ever found a way to simulate a "Connection Timeout Expired" message. It's to...

SSIS - How to pass data set from one Data Flow to another? (or other options?) - I am developing a package that needs to be able to export any of 5 different queries (all with different...

Server Side Trace - I am wondering if someone a help a brother out? I will try to give as much information as possible. Problem:...

Covert all characters in field into their ASCII code - Hi i have a column with the following data in ..... The data varies from numeric to capitals to lower case...

Reporting Services - large Report Model SMDL files - Hello! I am generating and modifying the report model programmatically and it comes out as big as 260MB - for over...

Am using Windows Server 2008R2,In this C:/ Drive Space getting increase - Dear All, I know this is some what get irritate to you,Very sorry for that but i hope that you can...

166 days to create index - I have a table containing 2,163,568,622 rows hosted on SQL server Standard 2008. Attempted to create a new index: -------------------------------------------------------------------------------------------------------------------------------------------------- Using TEMPDB = ON...

Select statement - Effective running - Hi All, I have an select statement which needs to be run 15 times with a slightly different where clause and...

Access with ADOX too slow - Hi My questrion is about connection to DB. I had migrate my DB Access to DB SQL Server 2008 and I'...

Error installing MS SQL Server R2 Enterprise (x64) - Hi Everyone, Recently I attempted to upgrade from MS SQL Server to MS SQL Server R2. However I have encountered an error,...

BCP Command, Error - Login failed for user - I am using this BCP command to output the data from a table into the file, and getting this error...


SQL Server 2008 : T-SQL (SS2K8)

Max with distinct two columns and corresponding third column - Hi, i need to write a query and can't get it to work no matter how it try. Here's what...

How many users are working till specify date? - Helllo, Can somebody help me? I have a table with 5000 rows history about working and retired time. It is the serveral...

How to modify these procedure for my input is null or zero - Hi Friends, i m creating web application for state and dist wise map i ve the tables like create table ind_state ( ind_stat_id...

Rounding off a percentage result - Hi, I've a small issue working out how to remove the trailing chars from the end of a percentage calculation. For instance: SELECT...

How to fetch count ? - Hi All, I have one table say A and in which 4 columns are there. Out of 4 , one columns stores...

trigger to call a program to write a text file onto a folder in the server - Hi to all I have created a trigger to call a program that is written by our program. The program is...

Outer Join performance - Can someone please give me advise on a better way to write the following? SELECT a.BoxId, b.field1 as value1, c.field1 as...


SQL Server 2008 : SQL Server Newbies

Get 1st Word in the string - I am trying to to extract the 1st word from the string but end up with this error : Invalid length...

Help a complete noob with a project - Hello everyone, i have a project for a datawarehousing class and could really use some help in this. Heres what i...

SQL Credentials - Is there a way to give a user rights to change the password on a credential without giving the Alter...


SQL Server 2008 : SQL Server 2008 High Availability

Replicate databases for querying only - Hi, Would appreciate any help in implementing a solution for the following: Source server: sql server 2008 r2 enterprise Target server: sql...

one db versus multipl dbs - Hi everyone We're looking into setting up a SQL cluster for our app. Our current setup is based on a very high...

HA options - greetings - we have SQL Server 2008 R2, and am in the process of setting up mirrored databases with a witness...


SQL Server 2008 : SQL Server 2008 Administration

Periodically Reviewing SQL Server Permissions - Hello, I am very interested in knowing how experienced DBAs go about periodically reviewing who has access their SQL Servers and...

Latching and Locking... - Hi Can you please tell about Latching and Locking and are these same with each of them. Thanks in Advance!!!

Workaround for filestream with group - Hi, I have faced a difficult situation here, This is my scenario, In our company, we need to implement the [b]filestream...

Database collation Change - Hi my Production DB's Server level Collation is SQL_Latin1_General_CP1_CI_AS where as my development DB's Collation is Latin1_General_CI_AI ,often our development team...

Backup .bak to My PC Local drive? - Backup .bak to My PCs Local drive? Instead of to the SQL Server's Local drive. I know this has been...


SQL Server 2008 : SQL Server 2008 Performance Tuning

multiple inserts multiple connections -blocking - In our environment we have table which gets populated through multiple connections. Just a single insert statement from multiple connections....


SQL Server 2005 : Backups

Log Shipping - Please help me out on below : I have configured the Log shipping and its working fine.So i want to change...


SQL Server 2005 : Business Intelligence

Command LIne DTS 2000 package import to SQL 2005? - I have a SQL 2000 instance with 400 DTS packages; the databases for this instance are to be moved to...


SQL Server 2005 : Development

"Cursor operation conflict" - HI INSERT INTO [srsvr\dev].db_lines.dbo.SYNC_OBJECT_RECEIVE_IN_M(object_name,eventtype,Created_by) SELECT TOP 2 object_name,eventtype,Created_by FROM db_lines.dbo.SYNC_OBJECT_SEND_HQ_M Order By Created_by I m executing above remote query and its giving error...


SQL Server 2005 : Working with Oracle

SSMA to migrate data from oracle to sqlserver - We migrated data from oracle to sqlserver using SSMA but when we see data of sqlserver on .NET GUI, formatting...


SQL Server 2005 : SQL Server 2005 General Discussion

Restoring a sql backup from another server - Hello: We have two sql servers one is HR uat and HR production. We want to refresh the HR uat...


SQL Server 2005 : SQL Server 2005 Performance Tuning

automate the process of database uploading from local system to dbsever - I have a local system and I have a DB server.From local system to DBserver only port 1433 is open.From...


SQL Server 2005 : SQL Server 2005 Integration Services

Stopping an SSIS Package on Data Flow Fail - I have an SSIS package with 5 separate data flow tasks. They are not linked and run in parallel. I;m...

Remtoe SQL Server backup on local server using SSIS - I am trying to create a package that would take remote sql server backup on my local server. But all...


SQL Server 2005 : T-SQL (SS2K5)

BCP to Text Files. Odd Character at File End - I'm trying to streamline a manual process here by using BCP to create some text files. I want the test...


SQL Server 2005 : SQL Server Newbies

SEARCHABLE PDF'S - Hi Guys, I have a sql server 2005 table that stores a list of small pdf articles, there are over 1900...


Reporting Services : Reporting Services

Toggle and Visibility depending on Parent Group value - Hi, I have a report where there are collapsible groups IT, Non-IT and Other. I want to remove the collapsible button...

Report - SubReport - I have two reports that Point to the same Sub Report. On The Sub Report I have a text that says...


Reporting Services : Reporting Services 2005 Administration

Logging into SSRS with NT AUTHORITY\NETWORK SERVICE instead of Windows Authenticated User - We recently had to recreate our development server, however, we were able to recover the ReportServer and ReportServerTempDB databases. We...


Reporting Services : Reporting Services 2008 Development

ssrs 2008 textbox expression value - In an SSRS 2008 existing report, I wouuld like a particular textbox to look like the following when there is...

need expression for startdate and end date - Hello, I have an ssrs report in the header I am trying to display report start date and report end...

Center a report in a Web Page - Hello all, I have noticed when I create a report and deploy it or preview it, everything is alligned to the...

Showing the same report layout but multiple times for different invoices - Hi, I am after some help and please excuse the subject description, I was struggling to think of the best way...

Indicator for to remain in one cell for a group - In SSRS, I have a drill down report with three columns. One column says 'yes' or 'no.' The other column...


Programming : General

.NET SSIS Script Task issue - I am still a rookie when it comes to Visual Basic. I've been giving VB code to read a file...


Programming : Powershell

Add Network Places using PoSh - I'm getting a new Windows 7 desktop. From a powershell perspective, probably a good thing. But, I have a goodly number...


Data Warehousing : Analysis Services

Cannot create an instance of OLE DB provider for linked server - Hi all, When i log in to my SQL Server Management Studio with windows authentication and try to run a stored procedure...


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


SQL Server 7,2000 : T-SQL

Alternative to manual pivots - Hey all, I have a rather complicated report using a stored procedure that uses the; SELECT @vals = @vals + ' ' + REPLACE( REPLACE( then lots of...


Career : Certification

MCSA SQL Server 2012 vs. OCA DBA 11g (Oracle) in time effort and complexity - Hi I passed OCA DBA 11g exams. I have some experience with Oracle, but zero experience with SQL Server. For me was...

Upgrade Exams - AKA Certification Tribulations I've previous 2008 certification but, before Christmas, I failed upgrade exam 70-457 (by ONE question. gah.) Next time...


Career : Employers and Employees

Switch from prod DBA to BI Admin or Cloud? - I would like to understand the pros and cons of switching the job role from a core production DBA to...


Career : Job Postings

Knowledgeable Mid-Level DBA - Westchester, IL - We are looking for a very knowledgeable mid-level DBA for a company in Westchester, IL. Knowledgeable means strong t-sql, knowledge...

This email has been sent to {user_email}. 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 ©2014 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com