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

Hybrid Databases

SQL Server already handles hybrid data.

We are dealing with more and more data all the time, and in more and more formats. As noted in this piece, companies are receiving data in new formats, from a variety of sources, and the traditional relational database isn't always the best fit for the processing, storage, and management of the information.

However SQL Server isn't just a relational database. It handles unstructured data well, with the Filestream and Filetable enhancements to varbinary columns. We can even read into this unstructured data with iFilters that we install on our instances. In fact, I'll be talking about these filters at DevConnections on Wednesday.

SQL Server has StreamInsight for complex event processing, allowing you to handle very quick streams of data that could overwhelm your OLTP architecture.  If that's not enough, there are connectors that allow you to integrate with other data processing architectures, like Hadoop.

With all the various subsystems like SSIS (ETL), Reporting Services (exports to other formats), Service Broker (messaging), spatial data, and more, I think SQL Server is an amazing hybrid database platform in and of itself, without any extra charges for the various features (like some other platforms). That's not to say you shouldn't use any other platfoms. If they work, use them and integrate with SQL Server, but if you don't have other databases in place, I'm not sure you need to look past SQL Server for many of your needs.

Steve Jones from SQLServerCentral.com

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

ADVERTISEMENT
sqlserverlive

SQL Server Live! Nov 18 – 22 in Orlando, FL

SQL Server Live! provides comprehensive education on SQL Server database management, data warehouse/BI model design, Big Data analytics, performance tuning, troubleshooting and coding against SQL Server. Find out more

SQL Backup Pro

"A real time saver" Andy Doyle, Head of IT Services

Andy and his team saved time by automating backup and restores with SQL Backup Pro. Find out how much time you could save. Download a free trial now.

SQL Prompt

5 ways to code effortlessly

Discover the different ways you can make writing, exploring, and refactoring SQL code refreshingly effortless with SQL Prompt 6. Download a free trial.

Featured Contents

 

The Fundamentals of SQL Server Replication by Sebastian Meine

Sebastian Meine from SQLServerCentral.com

Many of my clients need to make data that lives on one server available on another server. There are many reasons for such a requirement. You might want to speed up cross-server queries by providing a local copy of the data. Or you might want to make the data available to resource intensive reporting queries without impacting the OLTP load, maybe even with an intentional delay so you're always reporting against complete days only. Finally, you might be looking to implement high availability. In all these situations, SQL Server Replication is a viable option to look at when planning for the implemen­tation of such a requirement. More »


 

SQL in the City Pasadena 2013

Press Release from Red-Gate

SQL in the City is coming to Pasadena on October 9. Register for a full day of free SQL Server training the Red Gate way. Top tips and best practices for SQL Server database development and administration will be presented by SQL Server MVP experts, including Steve Jones and Grant Fritchey. You’ll also see Red Gate tools in action and have the chance to network with other data professionals. More »


 

Developing for Delivery, a Practical Example

Additional Articles from SimpleTalk

Richard Morris interviewed Michael Stoop, a database developer at Calvi, Europe's leading provider of Telecom Invoice Management software. The discussion focused on how Calvi transformed their database delivery process to accommodate massive database growth, statutory regulations, and developments in their application. Here's their story. More »

Question of the Day

Today's Question (by sqlnaive):

We have created following table:

CREATE TABLE dbo.T_EMPLOYEE
( EmpNo VARCHAR(20) NOT NULL PRIMARY KEY, 
  EmpName VARCHAR(50), 
  EmpContact VARCHAR(20), 
  EmpManager VARCHAR(20)
 FOREIGN KEY REFERENCES dbo.T_EMPLOYEE(EmpNo));

Here is the data populated:

INSERT INTO dbo.T_EMPLOYEE SELECT 'E1001', 'Steve Smith', '+1-723-2311', NULL
INSERT INTO dbo.T_EMPLOYEE SELECT 'E1002', 'Raj Shekhar', '+1-723-2145', 'E1001'
INSERT INTO dbo.T_EMPLOYEE SELECT 'E1003', 'Kelly Brook', '+1-723-1648', 'E1001'
INSERT INTO dbo.T_EMPLOYEE SELECT 'E1004', 'Sam Samson', '+1-724-2386', 'E1002'
INSERT INTO dbo.T_EMPLOYEE SELECT 'E1005', 'Keith Hunt', '+1-724-5689', 'E1003'

Now I try the following command:

TRUNCATE TABLE dbo.T_EMPLOYEE

What will be the result?

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: T-SQL.

We'd love to give you credit for your own question and answer.
To submit a QOD, simply log in to the Contribution Center.

ADVERTISEMENT

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Ace your preparation for Microsoft® Certification Exam 70-461 with this 2-in-1 Training Kit from Microsoft Press®. Work at your own pace through a series of lessons and practical exercises, and then assess your skills with practice tests on CD—featuring multiple, customizable testing options.

Maximize your performance on the exam by learning how to:

  • Create database objects
  • Work with data
  • Modify data
  • Troubleshoot and optimize queries

You also get an exam discount voucher—making this book an exceptional value and a great career investment.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

What is the data type used for a column that stores FILESTREAM data?

Answer: varbinary

Explanation:

The data type used for FILESTREAM data is the varbinary type. Specifically, you would declare the column as varbinary(max) and include the FILESTREAM attribute.

Ref: FILESTREAM - http://technet.microsoft.com/en-us/library/bb933993(v=SQL.105).aspx


» Discuss this question and answer on the forums

Featured Script

Restore Database with any number of Ndf files

Sanjeev Kumar, DBA from SQLServerCentral.com

Execute the code after passing databse name on which you want to restore and the path where your backup file being located.
e.g exec  sp_restoredb N'mydatabase',N'D:\mydatabase.bak'

Here mydatabase is the name of database on which you want to restore.
D:\mydatabase.bak is the location of database backup  file to which you want to restore.

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

Pro Active DBA - Hi Guys, I've recently been appointed to maintain my companies internal DB's. Must also stress the point that the DB's are...


SQL Server 2014 : Development - SQL Server 2014

The special character changed to question mark in DB - Hi.. When i am trying to insert delta symbol into db, its converted to question mark(?) symbol. I am passing...

how to convert date in varchar format into datetime - Hello Everyone, I have table in which date is in varchar format i.e. '29/09/2013' now I want to insert the records in...

How check constaint works? - Hi All! I have added check constraint on table. I am calling one function in that check constraint. Actually I have to...


SQL Server 2012 : SQL 2012 - General

how to solve this - two tables table1 table2 StudentId studentid TestId teacherid QuestionId classid PointsAchieved PointsPossible Write a query that would return teachers who’s students had highest overall percent correct on...

Can I have SQL 2012 AND SQL Express 2008 R2 installed on the same machine? - I'm looking at removing the SQL Server 2008 R2 Developer Edition that I have installed on my machine (a Windows...

Output based on Mapping Table and Orders Table - I have two tables, 1st Table is the Order Table which has order no,old items, price and their quantity. 2nd Table is...

ssis vs jobs - I just started as dba at new co. I'm looking over what the previous guy did. He has some ETL...

Failure Restoring Differential Backup - I have an issue with my SQL 2012 database server and restoring a full backup (norecovery) then applying a differential...

Error in attaching Adventureworks 2012 database - hi i have problem in attaching Adventure works 2012 database Attach database failed for Server '..........\MSSQLSERVER2012'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Serve

Sql Server 2012 can't see 2nd drive - I installed Sql Server 2012 Express. Everything went without a hitch until I attempted to create a new database and...


SQL Server 2012 : SQL Server 2012 - T-SQL

Searching for n-categories - Hi there I have a challenge where I need some help. May someone can help me? I have a table where a...

Trigger with RAISERROR - Hi, Why doesn't a RAISERROR inside an AFTER trigger "break" the transaction? Is there any flag or SET option to do...

how to update column city value from 'A' to 'B' and 'B' to 'A' in single query - hi, I have a table like id city 1 A so i want to update city column from A to B and again...


SQL Server 2008 : SQL Server 2008 - General

Error Message: BACKUP failed to complete the command BACKUP LOG msdb - Hi, I am getting following error message daily into my error log. I am running FULL Backup, Diff Backup an T-Log Backup. This...

Passing external sproc params to internal variables - Explanation? - Sometimes I find the reason behind a poorly performing stored procedure lies in passing the external parameters to internal variables...

How to run multiple stored procedures in parallel? - I have a stored procedure which runs in database DB1 This stored procedure will have other stored procedures inside it which...

Using CASE Condition for Prefixing a field from a table - Hi All, I want to retain the same Employee Code when an employee is rehired and prefix with A,B,C based on...

SQL Server Database Backup Monitoring - Hi SQL Masters, Greetings! Do you have a script that will determine if the backup failed/succeeded? Sample output: Database Name Status Log Date --------------- ---------- -------------- AdventureWorks2008...

TSQL Recomile Problems when using subqueries - Good Day All I have found a problem with a Simple TSQL script that is returning incorrect results due to a...

Convert DD MMM YYYY to yyyymmdd in string - Hi, I am struggling with changing the date format of [b]dd MMM YYYY[u][/u][/b] in the sample strings below [u]into [/u][b]yyymmdd[u][/u][/b] Sample Data...

Returning related records from the same table - I have a table named batch. Which contains a PK field(ID) and FK field(OriginatingID). The issue I have is that...

Query to create log file for sql select query... - can you please tell me how to create a log file in sql server 2008

how to add a column to a table ? - How to edit table data in SQL Server Management Studio ? I right click on table > Edit top 200 data...

synch two tables - Guys, I have two tables one is users and the other is frap_users ,Both contains some common attributes which are required...

Need Help on Fastest Search Logic - Hi, I have two tables named "Table1" and "Table2". Table1 Details: id bigint, product_name nvarchar(1000),quantity int records count on Table1 : 25000( may increase in...

query issue - Hi I have written a query and the output for the query is Run Date Fiscal Year Posting Period Port Fuel Oil Price Gas Oil Price 20061031 2006 10 FOS NULL 578.5 20061031 2006 10 FOS 275 NULL 20061031 2006 10 FUJ NULL 592.5 20061031 2006 10 FUJ 276 NULL 20061031 2006 10 GEN 273 NULL 20061031 2006 10 GEN NULL 568.5 20061031 2006 10 HOU 262 NULL 20061031 2006 10 HOU NULL 537.5 20061031

Prevent backups on C:\ drive? - Can anyone - off the top of their heads - think of a way of preventing backups being taken to the C:\...

data copy - I have a DEV database i.e DEVDB I also have a TEST database i.e TESTDB both are SQL SERVER 2008 I want to...

Need to combine multiple sql queries into one to produce a single outcome - Greetings, I have several queries that all produce a "yes" or "no" outcome. I need to consolidate these queries in...

listing of months for a particular year - Dear All, Hope you are doing fine. What am I trying to do is get a stock count for each item...

Rolling 3 month average cost help - Hi guys, I need some help calculating a 3 month avergae rolling cost. I have been trying to figure this out...

NUMA - In preparation for my MCITP exam tomorrow I have been reading through the books and just crossing the t's and...


SQL Server 2008 : T-SQL (SS2K8)

Query Help - Hello. I have one table that looks something like this.. 3022224 G 4980 65 3 3022224 U 4980 596 2 3022224 G 4980 67...

Compare delimited data - same data but in a different order - Hello All, I am currently working on comparing two versions of data for a comparison report. I have a delimited...

FILEPROPERTY 'SpaceUsed' what it returns - Can anyone explain the behavior of the query below. I was trying to write a query that can be run...

Usage of OR in WHERE clause - Apologies for a silly question, but I was taught in the past to avoid usage of OR in WHERE clause...

Error arthematic operations inside varchar @variable - Hi I'm getting conversion error in the messages while executing below query. Please help me understand this. [code="sql"]DECLARE @Linkedserver sysname = 'DataServerSeven'; DECLARE...

Update script - Hi, I a mtrying to create update staments including data for the table in this way but I am getting error...

Error converting data type nvarchar to numeric - Maybe I am a bit dense today since it is a Friday and I am looking towards the weekend.. But...

ORDER BY clause based on values IN () - Hi all, So my client want to run a SQL query to list data along the lines of SELECT field1, field2,...

how to replace this cursor with set based solution? - I've got a cursor script that I want to replace with a set based solution because the cursor script takes...

Query Needed - Dear All Currently I am getting following result sets Registerno SubjectCode SubjectName Qno Marks 12402223 171906 Quality And Reliability Engineering 1 4 12402223 171906 Quality And Reliability...

diffrence between nolock with braces and without braces - Can someone explain me about the difference between nolock with braces and without braces nolock (nolock)


SQL Server 2008 : SQL Server Newbies

Is this Correct Use of Dynamic SQL??? - [code="sql"] set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[SearchBiography] @firstname nvarchar(50), @middlename nvarchar(50), @lastname nvarcha

calculate database size - Friends, how can i calculate database size with this information: Processed 8192240 pages for database 'DBOLTP', file 'rm' on file 1. ?? (8192240...


SQL Server 2008 : SQL Server 2008 Administration

Management Data Warehouse Purge - Does anyone know where the table core.source_info_internal gets the days_until_expiration value from. I have an MDW database which has grown to...

SWITCH statement: required indexes in target table? - hello, is there some information about which indexes have to be created on a table to which I want to switch...

drop auto stats - Dear Experts How to drop all auto sys statistics in the whole database Thanks lot

Choosing replication type and preparing for it - Hi all, We have 15 servers (in different regions) and each need to work with subset of data and exchange modifications...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Need to set Max Memory? - One item you often see in "best practices" articles about SQL is the need to configure Maximum Memory at the...


SQL Server 2005 : Administering

How can i find which tables are read/write intensive within sql server 2005 - How can i find which tables are read/write intensive within sql server 2005 Really would appreciate some help on this guys...

BLOCKING CAUSED BY REORGANIZE OF INDEX JOB IN SQL SERVER 2005 - hi all, need your help on this. i have a job which is reorganizing the index and with that i am having...

backup job failed - Hi , my full,dataintegrity,index optimization jobs failed with the below error Ö Executed as user: NT AUTHORITY\SYSTEM. Database '****' is already open and...

SQL SERVER 2005 STARTING PROBLEM - Hai Every One. I am using SQL Server-2005. When i am trying to start my SQL Server(MSSQLSERVER) it was not starting and...

Creating a Loop to Iterate Through a SQL Table and Change Default Database - Hello! I was wondering whether someone could help me with creating a loop to iterate through a table (sys.syslogins) and change...

What roles do I add to a new custom role, which will allow CRUD and execute on SP? - I want to create a new, custom role to my databases, to which I'll add users. This role will allow...

NUMA Node - Hi all, We have 16CPU servers. When i took a look at Errorlog log i have all this informational Message. Is...


SQL Server 2005 : Backups

Logshipping Restore job is failing - Hi, We are using lite speed for backups. Yesterday, at 6:15pm, the log backup job is failed. The SQL Server error...


SQL Server 2005 : Business Intelligence

branding a map report with my company logo - Using SQL server 2008 R2. I created an SSRS report that uses ESRI map to show customer distribution on the map...

Default Selection (Report) - Hello SQL Gurus I am currently working on a report that a default selection used to select customers. I use Visual...

SSRS 2008 R2. Print Reports In sequential order - Hi All, I have 2 reports with following layout [b][u]Sales Report[/u][/b] Agent1 State..........................sales amount-----------------Number of items.......................Average Sales VA................................$25000.....................................10.........................................$250....... CA................................$35000....................


SQL Server 2005 : Development

Invalid object name 'DDLTriggerTest..EventTableData'. - hi, when u tried to modify the procedure then it gives me error like this... [b]Procedure tr_ProcedureEvents, Line 6 Invalid object name...


SQL Server 2005 : SQL Server 2005 General Discussion

Need to shut down sql instances with out disturbing other instances... - As we have four instances in one physical server which is all are 2005 instances. In this one instance not...


SQL Server 2005 : SQL Server 2005 Integration Services

Removing noisi commas in CSV file (SSIS) - I am quite new on using SSIS dtsx on SQL server 2005. I have CSV files that have been cleansed...

same issue - i have the Same issue. Please provide solution thanks in advance.


SQL Server 2005 : T-SQL (SS2K5)

Need help with Charindex function.. - I am trying to capture an email address from the below message..whats the best possible way to do it .. DECLARE...

using case when condition in WHERE in SP - i have a SP with where condition as where id=@id and month=@month and Eid=@Eid wht i need is if @Eid=0 i...


Reporting Services : Reporting Services

SSRS report previewing error - Can you help figure out for the following error messages while previewing my SSRS report. What is to be done.? [rsParameterReference]...

SQL Book Questions - Couple of SQL realted Book Questions I am seeking a book on SSRS 2012 and am considering the recently published title...


Reporting Services : Reporting Services 2008 Development

Using LIKE in drop down parameter - I have a report generated from a stored procedure. There is a code called "cip" with different lengths of code....


Programming : XML

Render XML report using XSL transform - Hi, I am upgrading reports from 2005 to 2008R2 and I noticed that two reports are failing on the new...


Data Warehousing : Integration Services

[Add Date from variable [2]] Error: An error occurred while attempting to perform a type cast. Getting error.. - Hello , I am getting below error when I am adding row into destination table using derived column. [Add PaymentDate_FirstOfMonth from variable...

SSIS should not be showing this error ? - I have an Execute SQL task which gives returns only one row with one column - a number. I set my...

How to test an ETL process ? - Is there a set of guidelines and tools which can be used for testing if your ETL process does its...

SSIS needed to run SSIS jobs? - Does SSIS need to be installed on a server to run SSIS packages? Or is SSIS only needed on computers...

SSIS check if file was already loaded ? - I want to make a package to load some text files into a database table. Any suggestions on how I...

Question about dynamic OLE DB connections - Ok I have the following issue & scenario I want a ssis package that connects to a single configuration database server,on this...


Data Warehousing : Strategies and Ideas

Multiple Fact Table / Calculated Measure - Hi I have the following structure currently a fact With Order details in and multiple dimensions (customer, date, product etc) in...


Data Warehousing : Analysis Services

MDX to Get Customer Status in given date range - I have a fact table that stores customer statuses based on when they last chaged. e.g. Customer Status DateAttained Cust 1 Live...

How To Find OUt How Long It Took For A Cube To Process - Is there any way to find out how long a cube took to complete processing the last time the cube...

Find Date from SQL Table in Analysis Services - Hi, How can I find date from SQL Table in Analysis Services. Like I have Thousand of date in a table...

Update Dimension Member - I've been tasked with populating the description of all dimensions and measures in our analysis services cubes to then be...


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


SQL Server 7,2000 : T-SQL

I need help Please :( - I have a table name Employee Logs table consist of EmpID, Empname,Logdatetime. I can generate the first in and last out by...

TempDB best practices - Doubt - Hello guys, I have two doubt: -- 1 script: SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count],...

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 ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com