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

No Handwaving Away the DBA

There's a great quote I read, at the end of this article. It says: "...if you think that switching to NoSQL will just let you hand-wave away all of the challenges of running a database, you are terribly misguided." The context is that all too often people looking to move away from some of the hassles of working with RDBMS platforms, which includes working with the DBA, haven't completely thought through the issues.

I do think NoSQL has a place in the world. There are domains of problems that I'm sure Riak, MongoDB, and others, solve in a more efficient way than SQL Server, Oracle, MySQL, and other relational systems. I'm not sure what they are, and to some extent, I haven't seen good guidance on where particular platforms excel. Most of the articles and pieces on choosing NoSQL seem to be trying to sell me "why a particular platform can replace my other one", and telling me to add in things like transactions, but not explaining the drawbacks.

However in all platforms, we often forget that there are really two frames of reference that matter. We need quick ways to work with data, insert it, update it, query it, etc. This is the development frame of reference, and it often dominates discussions of platforms. For good reasons, as developers are expensive, but that's only part of the system. We also need to consider the operational portion of managing data and applications. When I have those needs to rebuild indexes in relational platforms, or the requirement to periodically merge/remove old versions of documents, or even manage clustered, horizontally scaled resources, we need operational maturity.

In some sense the DevOps movement is built around merging these two frames of reference into the minds of all those involved. I hope that movement continues to grow and mature, and we learn that developers and operational staff are both necessary, and both need to function in a symbiotic, harmonious fashion.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

The Voice of the DBA 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.

Everyday Jones

Follow Steve Jones on Twitter to find links and database related items and announcements.

ADVERTISEMENT
Azure Management Studio

Boost Windows Azure productivity with Azure Management Studio

Manage your Windows Azure storage, diagnostics and compute in one place with Azure Management Studio from Cerebrata. Begin a free trial.

SQL Developer Bundle

12 must-have SQL Server tools

The award-winning SQL Developer Bundle contains 12 tools for faster, simpler SQL Server development. Download a free trial.

Learn .NET Memory Management

Ricky Leeks on Learning .NET Memory Management

Pick up all six free articles in one free download. Find out what Ricky has to teach you about garbage collection, memory management gotchas, and more. Download the article pack free.

Featured Contents

 

Cumulative sum of previous rows

Ben Kubicek from SQLServerCentral.com

I had an update statement that took 3 minutes to run after this code change it took 6 seconds. More »


 

Improve SSIS data flow buffer performance

Additional Articles from MSSQLTips.com

In this tip we have a very simple data flow using a source query with a predictable duration. The data flow takes longer to process all the rows and even larger buffers didn't make the problem go away. What can be the cause of this and how do we solve this? Koen Verbeeck explains. More »


 

From the SQLServerCentral Blogs - The Cardinality Estimator in SQL Server 2014 – Going Forward and Backward

Steve Jones from SQLServerCentral Blogs

I saw a talk from Joe Sack (b | t) on the cardinality estimator (CE) in SQL Server 2014 and found... More »


 

From the SQLServerCentral Blogs - Find SQL Database Owner with T-SQL

Bradley Schacht from SQLServerCentral Blogs

One thing that on occasion bugs me about PDW or APS or whatever it happens to be called this week... More »

Question of the Day

Today's Question (by Tom Thomson):

Which of the following editions of SQL 2014 suppotys the Buffer Pool Extension feature?

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: SQL Server 2014.

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

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.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

How do you remove a memory-optimized table filegroup from your database in SQL Server 2014?

Answer: drop the database

Explanation:

The only way to remove a memory-optmized filegroup in SQL Server 2014 is to drop the database.

Ref: Requirements for Using Memory-Optimized tables - http://msdn.microsoft.com/en-us/library/dn170449%28v=sql.120%29.aspx


» Discuss this question and answer on the forums

Featured Script

Server_Config_Script_2012

Patrick Akhamie from SQLServerCentral.com

Here's a quick query you can run across all your servers (2012) to find a wealth of information like service pack, edition, number of CPUs and RAM.
Even more information is available if you want to add additional SERVERPROPERTY attributes or fields from one of the DMVs.

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

Rebalancing blob and lob data in multiple files on primary file group - Hi all, A little background on what I am trying to achieve first. We are moving to Azure virtual machines and...

SQL 2014 AG with 2012 Replica - Hi all, Is it possible to have a 2014 Availability Group with a read-only secondary Replica in SQL 2012. The replica...

search the content of files - hi I want a mechanism that search the content of all files in my upload folder,then return the address of the...

AlwaysOn - use an Availability group listner or just point to the Cluster name - I have MasterDataServices installed on a server and the database is in an AlwaysOn Availability Group. The MDM front end currently...

SQL Server 2014 - Service Pack 1? - Hi there I am planning to migrate from SQL2008R2 --> SQL2014 but my team mates have concerns about migration of MS...


SQL Server 2014 : Development - SQL Server 2014

Selecting Records Based on Date - So let's say I have a table Orders with columns: Order# and ReceiptDate. Order#'s may be duplicated (Could have same...


SQL Server 2012 : SQL 2012 - General

Controller Action - Hi, I need to do some analysis. We have a database that track how much time it take to execute wcf service...

SSIS - create a script task programatically (the binary code for the script is not found) - Hi, I wrote a script task that´s build a SSIS Package dynamically with a Script Task. When I open this dynamically created...

Need to upgrade SQL Server 2012 Standard edition to Enterprise edition. - Hi, we need to upgrade from a SQL Server 2012 Standard installation to SQL Server 2012 Enterprise installation. Is it Possibility to...

slow sql server machine - Hello, We have a sql server machine with many databases. These databases are big and a few of the tables have millions...

Create Clustered Index on a Very Large Table (500 GB) - Hello, I have a scenario where I need to create a Clustered Index (CI) on a very large SQL Server 2012...

Unable to drop database user - Hi, We are unable to drop a database user with the following error "Msg 15284, Level 16, State 1, Line 1 The...

Long delay beween job start and actual package execution. - Hello I have problems sometimes with a package. There is and SQL Agent job that starts a package (from a file system...

SSMS (manually) generated jobs run long, then fail. - Hello, I have a strange problem SQL, and I am spoiled by SQLs (usually) great logging and error reporting, but...

Monitoring Clustered Server - Hi All, Could you please let me know what are the events to be monitored for a Active/Passive Cluster? Thanks in Advance.

Identifying what caused Corruption - I had a database in suspect mode this morning. Fixed it by: -placing in emergency mode. -set single_user mode -dbcc checkdb(N'dbname', REPAIR_ALLOW_DAT_LOSS) -Rebuild the log. -bring...

AlwaysOn: Client (Re)Connecting to wrong server after fail-over? - Hi, I hope someone can point me into the right direction. We have a AlwaysOn HADR setup with 2 servers, one...

Cannot connect with read only intent when using listner name - Hi All, I have an issue when connecting with applicationIntent=readonly when using Listener name. I can connect to the read only...

Changing Availability Group - Hi Experts, How can we change the backup replica in Always on Cluster. I am not able to take backup because...

Design a table to hold filters for selection criteria - I have an ordering database with several tables that store data of orders belonging to a wide variety of clients....

How to create a SQL 2012 Failover cluster using VMs? - Hi thanks before everything!, I have the following question that I hope you can oriented me: How to create a SQL...

many to many relationship - Hi, why do we need a junction table in a many to many relationship? Why can't everything be in just...


SQL Server 2012 : SQL Server 2012 - T-SQL

Password History Table - hey, I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details.... I have to keep...

The storage of VARCHAR(MAX) when null - Hi all, If I have a table CREATE TABLE [dbo].[logg]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Details] [varchar](MAX) NULL) insert logg (Details) values('') insert logg (Details) values(null) Will...

Hanging WHILE loop - Hello Everyone, My name is Tom and I'm studying for my Data Platforms MCSE, its still early days so bear with...

Is there any database setting that affects CHECKIDENT(RESEED) - Greetings, all. I am using CHECKIDENT(RESEED) in a Visual Studio 2013 script that clears numerous test data tables & repopulates them afresh,...

Disable only one trigger action - I have a trigger that executes AFTER INSERT, UPDATE, DELETE. Is there a way to disable and then reenable only...

complicated sql syntax - looking for explanation for working query - About six months ago, one of the regular SSC forum experts gave me this query to use to build a...


SQL Server 2008 : SQL Server 2008 - General

Sum function where field = - Hi, I am trying to use sum function where a field falls into specific category, but so far no luck. I have: Wait_Status...

SSMS Fails to connect to Reporting Services 2008 R2 Instance (local) - Hi SQL Server reporting is all working fine (Report Manager & SSRCM etc) with the exception of trying to connect to the...

GetDate - in where clause - Hi, I need to get extract the year and month from the currentdate and concatenate with 01 YYYYMM01 e.g. 20140501 I have tried various...

sp_who2 active report help - I was having 'Slowness' on App server. when I ran 'SP_who2 active' ON DATABASE server found these: spid: 12 status: background, login:...

Unable to perform query message - The user can put in the following parameters and I'm trying to create an sql statement that would work for...

Need to generate scripts of users with assigned roles in SQL 2005 - Hi all, I need to generate scripts of all users I added in my database with all assigned roles and permissions....

Upgrade from SQL Server 2008 SP3 to R2 - Hi, I have an instance that is in version: SQL Server 2008 SP3 ( 10.00.5500 ) I have to restore a database into...

Case statement with <> condition - I have query something like this ..i haven't posted here original quary [code="sql"] IF (@p_flag = 1) BEGIN SELECT .. ... FROM Test1 T1 INNER JOIN Test2 T2...

Error in Loading CSV file - Hi All, I am getting when loading data from csv file.. [Flat File Source [9007]] Error: Cannot open the datafile "20140505_Sales_new.csv". [SSIS.Pipeline] Error:...

Divide column values - Hi, I have these two columns select '$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Total_Amount,2)as MONEY),1), '.00', '') as Total_Amount, '$ '+REPLACE(CONVERT(VARCHAR(32),cast(round(Monthly_Amount,2)as MONEY),1), '.00', '') as Monthly_Amount from Finance Now Monthly_Amount column should...

created a index rebuild job want to understand what happens when it runs - Dear Experts, I've created an index rebuild maintenance job for one table which has 9 indexes, the TSQL generated by the...

can we use the system databases of a enterprise edition 2008 into a standard edition of sql 2008? - Hi All, can we use the system databases of a enterprise edition 2008 into a standard edition of sql 2008, as...

Trace Duration in -ve - Will the SQL server profiler trace duration value will be in -ve? here is the one i got Duration Starttime Endtime -43 2014-05-06 22:26:07.887 2014-05-06...

Why is this fast query slow in Visual Studio - The query below executes in SSMS in 2 ms. When I run the exact same query in Visual Studio it...

mdf database with a wrong instance - I am trying to set up my SQLEXPRESS account. I can not make an mdf database and it has something...

Investigating deletes from a table - Hi! This is my first post here, but I'm a longtime reader. I'm investigating who or what is deleting records from...

SSRS -- Error while subscribing for new role - Hi all, I'm getting below error while subscribing for new role in SSRS. System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: An internal error occurred on the report...

SSIS caching error - I am running a pretty straight forward dataflow that is: Source -> Lookup -> Destination I am in the process of trying...

Duplicate Object_ID and rows ...etc - Hi friends, When I run select * from sys.Partitions, output comes out with object_id (with 3-4 same object id no. some time...

Help Needed in Complex Logic - Hi, My table and data: [code="sql"] Create table Sample(Empid int primary key identity(1,1),EmpName varchar(50),Empsalary money,EmpDesignation varchar(30)); insert into Sample values('Jhon',8000,'Manager'), ('Smith',6000,'Lead'), ('Samuel',4000,'AccountExecutive'), ('Simson',4000,'AccountSpecialist'), ('Eric',22000,'Director'), ('Jonathan',12000,'SeniorManager')[/code] Expected

TSQL dilemma - I have a table with an identifying field that is duplicated. I want to get a single record for this...

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

Instance failover failing in Cluster - I am getting the following error when I am trying to failover an instance to another node: The operation failed because...

Insert sp_spaceused output into temp table - Hi All, I need to insert sp_spaceused output of DATABASE into temp table. How can I do this? Pls suggest. Thanks

login failed for user microsoft sql server error 18456 - hi! when i try to open the sql sever 2008 with sql authentication i have this error: login failed for...


SQL Server 2008 : T-SQL (SS2K8)

DEMO_CODE = 'DE1' - Hi everyone. I have these two records in [prj_demo_data] table: [code="other"]prj_code demo_code decmo_vchar_data --------------------------------------------- 1425 DE1 U 1425 RAP YES 1426 DE1 B[/code] How do I select...

forming a dynamic query - have a sp with 4 parameters the values of this parameters is obtained from application currently i have @transfrmdt date, @transtodt date, @cid integer, @Type...

comparison in the merge statement about null values - I use the merge statement in a sproc to insert, update and delete records from a staging table to a...

Store result of stored procedure into a xml/nvarchar(max) variable - I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this...

OPENROWSET when Excel worksheet name is unknown - I've created a stored procedure that accepts two arguments: the name of an Excel workbook (@workbookBillRun), and the name of...


SQL Server 2008 : Working with Oracle

Error while executing queries using Linked server to Oracle - OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment...


SQL Server 2008 : SQL Server Newbies

Moving Queries for access to SQL - For the most part this is pretty easy once you know where to put things, but what if the query...

Slow self join - I have a stored procedure which updates a table with the code below. It is rather slow (table with 360,000 rows)....

Display Binary Image in Report Builder from Database - Anyone have any suggestions on how to display a Binary Image in Report Builder 3.0? I'm trying to display the...

More Memory - We have a Sql Server running Server 2003 Std X64 SP2, On this we have a Microsoft SQL Server 2008...


SQL Server 2008 : Security (SS2K8)

General Script or Method to Query/Report Permissons for a SQL Server Instance - Hello, Is there a general SQL script or method for putting together a list of all users and permissions for a...


SQL Server 2008 : SQL Server 2008 High Availability

AlwaysOn Snapshot with CDC? - [b]Scenario:[/b] CDC is enabled on the production database. We take an AlwaysOn Snapshot of this production database at 10pm. Does CDC replicate...

Cluster resource 'SQL Server' failed - While troubleshooting why a query failed on our SQL Server 2005 cluster, I note in the SQL Server log that...


SQL Server 2008 : SQL Server 2008 Administration

How to determine Database Recovery Percentage - How do you best to determine Database Recovery Percentage? I did this many times but what is the best method, Windows...

Select Statements OPENQUERY Hung on a Server - I have 27 Select Statements OPENQUERY Hung on Server. I stopped all of the jobs. I killed 3 of the processes and...

Different CPU count reported by SQL properties from server available - One of our servers is in the process of being migrated from a physical server with 24 cores to a...

SQL Server CMS Multiserver query issues. If statement. - Hi, I have recently added all of our SQL Servers to our CMS instance. Our environment is a mix of SQL...

sql db backup t-sql - Hi all. I would like to create the following small piece of code: Backup a sql db Create the .bak file in this...


SQL Server 2008 : Data Corruption (SS2K8 / SS2K8 R2)

Restore database from SQL Server 2012 to SQL Server 2008 R2 - Hi, I initially took backup from SQL Server 2012 (database compatibility mode was set to SQL Server 2008 (100)) but when...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Query running for morethan 2Hours 30 min - Hi This query takes morethan a 2 Hour to complete and also attached the exec plan The DML.ExceptionsTrackingTable is having morethan...

Interpreting the results of sys.dm_io_virtual_file_stats - Hi I am using the following query to collect latency statistics: [code="sql"]SELECT [ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END, [WriteLatency]...


Cloud Computing : SQL Azure - Development

Federation / Sharding - same server? - I'm currently researching this functionality for incorporation into a Cloud App to facilitate scaling out of an Azure SQL Database...


SQL Server 2005 : Administering

Instance installation date - Hi, I would like to know in with date did a SQL Server 2005 instance was installed on a server. Can I...

Create Clustered Index on a Very Large Table (500 GB) - Hello, I have a scenario where I need to create a Clustered Index (CI) on a very large SQL Server 2012...

Evaluation of duplicate aliases for multiple objects in a procedure - I had a submittal this morning for release to production that contains 2 aliases 'o' for a physical table and...

Create Database - Hi, I installed a new sql server standard edition with SP4,Now i right click on databases and create new database but...

Select - Hi, Can any body let me know the query to select all the tables in the database with a particular word...


SQL Server 2005 : Working with Oracle

Unable to perform query using Oracle iSite - The user can put in the following parameters and I'm trying to create an sql statement that would work for...


SQL Server 2005 : SQL Server 2005 Integration Services

(SSIS 2012) Excel Destination Columns Added After Data Flow Task Already Designed - Working on an SSIS 2012 project for a client. The project basically finds and loops through a bunch of incoming Excel...


Reporting Services : Reporting Services

ssrs with rectangle cuases first page to be blank - In an SSRS 2008 r2 report I have 6 matrixes that I placed into 1 rectangle so that all the...

Email Report to list of people that are defined by the report ??? - Hi is there a way to email a report to the people who are on the report For example I run a...

IIF Condition - Hi Team, I need some assistance with the following I have an IIF condition and it is as follows =round((Sum(Fields!VDuration.Value) + sum(iif(not(Fields!STypeName.Value = "SS")...

Table two Column - Hello I have a table that gives me information, it is for example in column 3 appears OK the values...


Reporting Services : Reporting Services 2008 Development

Percentage Calculation in Metrics Report. - Hi All, 12/3/2013 12/4/2013 Measure value percentage value percentage Failed 100 percent 1 200 Delivered 100 percent 2 200 Unknown 100 percent 3 200 Total 300 600 i have a metric...

ssrs 2008 missing parameter value - In an SSRS 2008 R2 existing report, I am passing a new parameter called 'SchoolYear' to an existing report. I...


Data Warehousing : Integration Services

Get ALL files from folder based on lastDateModifed - I need to get ALL the files from a folder based on the DateModifed and load into a SQL table. I...

SSIS - REST SPI - Parameters - Hi Gurus, I'm trying to implement data passed from SQL database to REST API using SSIS. Background: ------------ The REST API takes 3 parameters...

Best way for SSIS log...there are so many - Hi, Everywhere somebody talks about logging a different way in SSIS packages. What is the best way to log the...


Data Warehousing : Analysis Services

group by clasue in mdx - Hi Friends i have small doubt in mdx query. table having data like below table name : patieninformation pn prcode dos Expected Payment MS0000003 PT001 1/2/2013 29.72 0 MS0000003 PT001 1/21/2013 57.1 0 MS0000003 PT002 6/7/2013 26.69...

Login failed for user 'NT Service\MSSQLServerOLAPService' 28000 - I am using SQL Server 2012 developer edition on Windows 7. I have the following error attempting to process a...


SQLServerCentral.com : Anything that is NOT about SQL!

First SQl job were you nervous? - I currently work in a health clinic as a Clinical Applictions Specialist. I do the training, configuring workflows and as...

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.com : SQLServerCentral.com Website Issues

New format of subject for notification emails - This is only my opinion but most of the time I read these emails on a phone and the preamble...


SQL Server 7,2000 : General

unable to see entire SQL Statement in profiler - I ran profiler but the column textdata does not give me the entire sql statement. What is the reason? how to...


SQL Server 7,2000 : T-SQL

Transactions problem - Hi, I have a requirement like,every month we will have a release, we collect the code from the TFS and give...


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

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