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

Project Hekaton

If you haven't heard about Hekaton by now, you should think about reading up on it. There's some information from TechNet, a keynote video, and more. Despite the ranting from Oracle's Bob Evans in 2012 that this was "vaporware", it's coming in SQL Server 2014. The latest demos don't show 100x increase in query speed, but they 30x or so numbers I've seen are very impressive. 

Hekaton is an in-memory database technology, actually called In-Memory OLTP in Books Online. This isn't a re-invention of the pintable concept from SQL Server 2000. Instead it's a very well though out architecture and rather interesting in the way it's implemented. The keynote video gives you a deep overview, though to use the tables, most of us don't need to know the internals of how they work.

However we do need to look at the restrictions, err requirements. Since these tables will exist in memory, obviously you need more memory, but more than you thought. The recommendation is twice the expected table size, to account for versioning. In addition, this memory is in addition to the buffer pool and other memory your instance already needs. That means much more complex memory calculations and monitoring for DBAs.

The complexity of SQL Server continues to grow over time as more and more features are added to the product. Many of us don't need much more than the core relational database functionality for many applications, but the in-memory OLTP tables might be one feature that can help dramatically increase the speed of your application.

Steve Jones from SQLServerCentral.com

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


Video and Audio versions

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

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

Steve Jones

Windows Media Video ( 15.3MB) feed

MP4 iPod Video ( 18.1MB) feed

MP3 Audio ( 3.7MB) feed

Feeds are available at iTunes and Mevio

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

ADVERTISEMENT
SQL Monitor

Optimize SQL Server performance

“With SQL Monitor, we can be proactive in our optimization process, instead of waiting until a customer reports a problem,” John Trumbul, Sr. Software Engineer. Optimize your servers with a free trial.

SQL Data Generator

Generate realistic test data, fast

“In less than the time it took me to get my coffee, I had a database with 2 million rows of data for each of 10 tables.” Stephanie Beach, QA Manager. Try SQL Data Generator now.

SQL DBA Bundle

Save 45% on our top SQL Server database administration tools.

Together they make up the SQL DBA Bundle, which supports your core tasks and helps your day run smoothly. Download a free trial now.

Featured Contents

 

Stairway to PowerPivot and DAX - Level 4: The DAX BLANK() Function

Bill Pearson from SQLServerCentral.com

Business Intelligence architect and author Bill Pearson exposes the DAX BLANK() function, and then provides some hands-on exposure to its use in managing empty values underlying our PowerPivot model designs. More »


 

Free eBook: SQL Server Execution Plans, Second Edition

Press Release from Red-Gate

Every day, out in the various online forums devoted to SQL Server, and on Twitter, the same types of questions come up repeatedly: Why is this query running slowly? Why is SQL Server ignoring my index? Why does this query run quickly sometimes and slowly at others? My response is the same in each case: have you looked at the execution plan? More »


 

Getting Started with Columnstored Index in SQL Server 2014 – Part 2

Additional Articles from Database Journal

Column Store Index, which improves performance of data warehouse queries several folds, was first introduced in SQL Server 2012. Though it had several limitations, now SQL Server 2014 enhances the columnstore index and overcomes several of the earlier limitations. In this article, Arshad Ali discusses how you can get started using the enhanced columnstore index feature in SQL Server 2014 and do some performance tests. More »

Question of the Day

Today's Question (by Steve Jones):

Can you store filestream containers on compressed volumes?

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

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

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.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I am working in Management Studio on a SQL Server 2012 instance. I want to execute SQLCMD using the IDE. How do I do this?

Answer: Select "SQLCMD Mode" from the Query menu

Explanation:

There is a SQLCMD mode built into Management studio.

Ref: Edit SQLCMD Scripts with Query Editor: http://technet.microsoft.com/en-us/library/ms174187.aspx


» Discuss this question and answer on the forums

Featured Script

Capture_Login_Auths

Donald Patterson from SQLServerCentral.com

This is an enhancement to the sp_helprevlogin stored procedure.  As a DBA I'm always asked to grant access to an account based on another and it can be difficult at times.   

This procedure takes in a login name, windows or SQL, and will generate the access and authorities for the login across the SQL instance.  The script generates the user's login, any server roles the login may have, database access and database roles that have been granted, and any specific permissions granted on the database and authorities granted on objects.  Everything gets outputed onto the message tab.  The output can be copied into a query window and with a replace of the login id, access can be quickly granted to another user.  
 
 If the login name is not provided, an error will be generated
 
 EX - sp_capture_login_auths 'domain name\snooze' 
 
 EX - sp_capture_login_auths 'snooze'

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

Chaning the drive letter for system database and user databases - Hi Team , I have system database and user database file are present in G,H and W drive.The process is going...


SQL Server 2014 : Development - SQL Server 2014

Cursor to Delete data - Hey Guys, i have to delete data from a table which is older than 2 weeks, how can i use a...


SQL Server 2012 : SQL 2012 - General

NUMA - Node - Hi, I need to verify memory size of each NUMA node in the system. Please share me the method how to...

Permissions and encryption - Is there any way to restrict permissions so that a user can read from a database (and even decrypt data)...

Wrestling with DateTime data from SQL Sever 2000/2005 DB - Hey guys. I tried to work out the solution myself before asking for help but couldn't get this to work....

Stored procedure performance update after index rebuild - I have a stored procedure that runs every hour from 5 AM to 10 PM to check the pending orders...

How to update a DB user password in SQL Server 2012 - I am needing to update the password of one of the users in the DB\security folder in SQL Server 2012....

Upgrade 32 bit to 64 bit? - I recently inherited a SQL 2012 32 bit installation running on a 64 bit OS. Has anyone ever performed such...

SQL server 2012 HA and DR solution design for sharepoint - Hi Experts, We need to setup HA and DR solution in sql 2012 for Sharepoint. what is the best practice for implementing...

Need suggestion in Prod migration - Hi All, We need to migrate some of our old prod and dev to new prod server.i have few queries to...

Minimally Logged trouble: SELECT ... INTO vs. INSERT INTO WITH (TABLOCK) - Hi everybody. I've an unexpected behaviour filling a table with the [i]tablock[/i] hint. I need to use minimally logged insert...

Express edition Performance tunning - Hi Can anyone guide regarding how to do performance tuinning using SQl 2012 Express edition Regards

Query against snapshot databases create more physical io requests - Hi all, I had an issue where a query executed against a snapshot database was taking an age, the same query...

Suggest a port other then default - Hi at one of the client i have to suggest him a port other then default.Can anybody tell me how...

SSDT Problems for VS 2012 - Hello guys I need urgent help here. I've installed ssdt sp 1 and it worked for about few days. Yesterday when...


SQL Server 2012 : SQL Server 2012 - T-SQL

BCP Command for XML Export - Hi All, I'm trying to run a query to export one of the tables currently sitting in our SQL Server using...

create a view to show the backup status in every 10 mins - I have a question, my table have following data: userID, startTime, EndTime ————————————— 101, 04/11/2013 11:00:00, 04/11/2013 11:55:00 102, 04/11/2013 11:00:00, 04/11/2013 11:24:00 103, 04/11/2013 11:20:00,...

sp_OAMethod to retrieve the file attributes from a folder - Hi, I found the below code that will bring back "some" of the attributes of any file in the selected folder......

display order by like 1,2,3,4,5...............plz write quarie - Examples of values I want to put in order houseno 3-13-1 3-13-3/a 3-13-3/b 3-13-2/a 3-13-4 3-13-6 3-13-5/a 3-13-4/c i want output like this below houseno 3-13-1 3-13-2/a 3-13-3/a 3-13-3/b 3-13-4 3-13-4/c 3-13-5/a 3-13-6


SQL Server 2008 : SQL Server 2008 - General

question about querying Active Directory - I've linked to AD and can execute the following query. It returns a result set (GREAT) but also an error...

sql jobs issue - Hi All, SQL nightly jobs were hung because one of the users forgot to close the application or log off from...

?? on usiong a replace on an update - Hi I'm looking to update a field It currently has username@domain I want to update to domain\username for example jsmith@company should be company\jsmith Thanks...

calculating a date in the future - hello, i was trying to find dates in the future when a few doubts arise with this sql i can calculate, lets...

SSIS jobs activities - Hi All, I want to write small query, which can show me alll the SSIS jobs activities Shuan

Reporting stored procedure issue - I am in a bit of a pickle on what to do here. Currently we have a View that is...

Must declare the scalar variable - Which is already declared - Hi there - I rarely use Cursors (mainly due to performance reasons) but when I do I get an error stating...

what is the best way instead of using cursor? - hello all. I need to use nested cursor for 5000 record,but it doesn't work correctly and stoped.what is the best way...

Database mailer is not sending mail - Hello, We have a database mail configured in SQL Server. When we are trying to send a mail using any procedure it...

hierarchical linking of locations to each other - I need help here, I have this table, I may need to create another column I'm not sure yet. CREATE TABLE...

Downgrading Compatibility Level - I recently had to take a snapshot for replication of a client database, this failed because the database contained an...

recovery model simple - Howdy Everybody, My database is Simple (Recovery Model) and when i generated backup, the log file was to become big 30gb)......

SQL Query - same table - same row - Hi Guys, I'm hoping this is "possible" but my skills are failing me... I have this query that works fine... SELECT shipmentitems.orderid AS Ref, shipments.id, shipments.traderid...

SSRS default value Time Expression - Hi All, I got two dropdowns in SSRS report StartTime and EndTime StartTime always should take default values as 7...

Fullscan statistic refresh produces badly performing histogram - Hello there, I have found an issue in our environment that I cannot explain. There is a statistic based off of two...


SQL Server 2008 : T-SQL (SS2K8)

Selecting n Closest (Geographic) Rows - Hello there! This is a high-level explanation. I have two sets of data. One set , let's say, is employer locations. The other...

Help with Text search - I have a customer that is upgrading his legacy to updated version of his software and I have to develop...

MSDB.dbo.sp_send_dbmail HTML formatting fails, but unformatted works - This sample code should work if folks want to test this - just add your email in the 2 sections [insert...

Export particualr column of Stored Procedure to a Table - Hi I know that It's possible to export the entire result set of a Stored Procedure to file, table etc.. but...

How can I select values where first 2 characters are non numeric and last character is 1 - I need to make a condition where I exclued results from a column where the first 2 characters are not...

Sp runs from 7 sec to 10 min (UnitPricingTrans) - I compiled a stored procedure (UnitPricingTrans) 3 days ago. The first execution took 10 min. When I ran it second time it...


SQL Server 2008 : SQL Server Newbies

API? - Hi all, I have many procedures written for a projects where we had to connect to MySQL database (there is an...

CTAS – Create local SQL Table As SELECT from a view to a Oracle Linked Server - Please look over and comment. This is new territory for me on SQL Server Linked Servers The Problem: SQL Server's Linked...

Consolidating an entire year into a single query - I Have a query that counts individual monthly orders just a simple count as below, however i want to consolidate...

Database activity - Hello, We have a SQL Server 2005 Enterprise Edition - 64 bit instance with 12 user databases. The activity monitor database drop...

Need all activity by product code even if it's empty. - Trying to show all activity (typically dollars) and eventually group by product code. there are some months when some (if...

Maililng Labels - Hello , I am working on a Mailing Labels Report using SSRS 2008. As per advise, first I placed three list boxes...

SQL Server 2008 Data Compression - Can a table be compressed (row or page) as an online operation? What is the syntax to do it as...


SQL Server 2008 : Security (SS2K8)

Enabling "Enforce password expiration" on an account with an old password - It's required at my employer to have password policy and expiration enforced for all logins. Well, turns out there's a...


SQL Server 2008 : SQL Server 2008 High Availability

When Database Mirroring fails, testing recovery scenarios - I have a mirror setup, and I want to test failover recovery when things go bad. Like in clustering, I can...

Issue with Logshipping - Can someone help me on this.. For testing,I've configured logshpping on same server (primary and secondary are in same instance).. LSCOPY job...

Logshipping to another server with different drive names - Hello, We have primary with drive F,L and secondary server with drives D,L. Data files from F goes to D in secondary...


SQL Server 2008 : SQL Server 2008 Administration

Concurrent fullbackups in sql server - We've a server (SQL 2008R2) with two sets of full backups running at the sametime..One is local backup with sql...

Tempdb and performance issue - HI Experts I have noticed that the performance of our application is slower than expected, I decided to add file to...

user connections sql server - How many user connections can sql server handle? In the sp_configure the default values for min 0 and max 32767....

jobs taking too long to finish - On one of our prod server, every sunday we perform full backups and few other archival processes. We use third...

Dynamic TCP/IP Port - Hi I'm a little confused, one of our Production Instances is configured to listen on a Dynamic port, which I thought...

Copying database error - HI Experts I am trying to copy a database on the same server but the error says the job is failed...

Permissions issue with temp tables - I was 'cleaning' up some security issues on database. I added users to a Group login ID used by the...

Can someone translate this restore error? - I have a 100GB backup to test restore. After 4+ hours and 90% done, I received the below error: [quote] Restore failed...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Stuck tracking down CXPACKET issue - We have a server that often has the following happen: 1) It breaks a SPID down and assignes hundreds of threads...


SQL Server 2005 : CLR Integration and Programming.

Return image from file system as if it were in a blob column - I have been tasked with writing a CLR proc (my first) to retrieve an image from the file system. Background: Currently,...


SQL Server 2005 : SQL Server 2005 General Discussion

Update SQL job step - I need to add my email address on the failure steps on SQL agent....There are hundred of jobs setup and...

Query - hello select vm.VoucherTypeCode,vm.VoucherNo,vm.VoucherDate,vm.Narration, vt.VoucherType from VoucherMaster vm left join VoucherDetail vd on vm.VoucherTypeCode = vd.VoucherTypeCode left join VoucherType vt on vt.VoucherTypeCode = vt.VoucherTypeCode where vm.VoucherDate BETWEEN...


SQL Server 2005 : SQL Server Express

Cannot find SSMS on server to connect to SQLExpress2005 - Hi everyone, I have a SQL Express 2005 instance on our Blackberry server. I can see the location of the database...


SQL Server 2005 : SQL Server 2005 Integration Services

OLE DB Error Redirect - Generic Error Descriptions - I have a very simple package I'm experimenting with error handling on, which takes a flat file (CSV) and then...

Combining everything to present the whole picture - Really need some advice and guidance. I have been working a ETL/SSIS solution that was 75% complete when I took...


SQL Server 2005 : T-SQL (SS2K5)

Manually increment smallint column during an INSERT command. - Here is how the table is created... SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[SPEDDTL]( [spdd_f1] [char](2) NOT NULL, [spdd_f2] [char](1) NOT...

Customer Purchase Incentive Validation in T-SQL - Hello all - I would like your input on how to address this problem using T-SQL. I don't expect you to...

IF table exists, DROP TABLE then CREATE TABLE - script not working - Hi all, This code to change a column name is not working and I'm not able to figure out why: IF OBJECT_ID(N'dbo.mtzcus_2014_CoreMenuReportCriteria',...


SQL Server 2005 : SQL Server Newbies

Three tables SQL query - I have a table (Vehicles) which contains a list of vehicles. VehicleID PlateNo CurrentDriver I also have a table (History) which contains a the...


Reporting Services : Reporting Services

SSRS installtion failed - Hi guys, may be a foolish question... does internet connection does effect the ssrs installation. I was asked to install ssrs on 4...

Sharepoint integrated reporting deployment problem - SQL 2012 Sharepoint 2010 VS / BIDS 2010 using Integrated mode for SSRS with SharePoint report server url is http://XXXdev002/sites/Reports/_vti_bin/ReportServer/ reports folder is at...


Reporting Services : Reporting Services 2005 Development

Different last page - How do I get my report to print a completely different formatted page on the last page? One way of seeing...


Reporting Services : Reporting Services 2008 Administration

Report Grouping - Grouping Question - Best advice Here's my dataset DocNum ItemNumber ItemType QtyOnHand MO001 Item A LotTracked 5 MO001 Item B NotTracked 100 MO001 Item C LotTracked...

SQL 2005 to 2008 Upgradation (System Databases) - I want to upgrade SQL 2005 to 2008, i don't want to do in-place migration directly. we are planning Side by...

Report Builder says .Net Framework 3.5 required - Hi, I ran into a weird issue today in Sql Server Reporting Service 2008 R2 I installed SSRS 2008 R2 on...


Reporting Services : Reporting Services 2008 Development

Extend a column based on Selection in Parameter Prompts - I have a parameter prompt with hierarchy (Category, sub category, product) If I select for instance "Locks" in the prompt, the...

Conditional Formatting of Table Row - I am trying to find a way to format a border style for a row if the value in a...

visual studio 2008 create project Business Intelligence Projects --> Analysis Services Project - While click rebuild first and right click process follow picture P1 rebuild project picture P2 alert error "The server content appears to...

ssrs 2008 r2 display customer name and number - In an existing SSRS 2008 r2 report, I have a database column that originally contained two values. The two values...

Last Three Row - Goal: Select the last tree row in a detailed list. Problem: How should I do it? Information: *The data source is SSAS.

Report rendering in Chrome - My report runs fine when in IE. But in Chrome it shows blank report. Any ideas...

Resetting Parameter Default Value - I have two parameters, ParameterA and ParameterB. I want to set the default value of ParameterB based upon the selected value...

Cognos to SSRS 2008 - All, we are going to migrate 41 Cognos report to SSRS. This project is in initial stage now. As part of this...


Data Warehousing : Integration Services

Looping a DataTable twice - I am using c# and .NET 3.5 in visual studio 2008. I wanted to loop over a result set stored...

Import Excel to Sql table - Can anyone help me on this. what is the easiest way to do this? I used a excel source and...

Tips for avoiding stored passwords with DontSaveSensitive? - Hi all, We have an in development data warehouse project in SSIS 2012 and I am trying to set up our...

warning on send mail task about sender email malformed - I am using the following value for from mail in my send mail task. "ServerNameAutomatedMailer <Monitor@myorg.org>" It runs fine and also...


Data Warehousing : Strategies and Ideas

Updating Historical Information - I have to create a slowly changing dimension with the caveat that any of the data attributes can change for...


Data Warehousing : Analysis Services

Apply Sourcecode in SSAS - Goal: Use the KPI [Measures].[Profit Margin] inside of SSAS:s tab named Calculation and then use it in my SSRS report and...

Age groups based on difference - Date slicer - Hello, I have a cube I'm working with that has a dimension called Employees. In the dimension table contains a Birthdate. What I...

Calculating MTD, YTD and ITD - I have developed a Cube for client and want to implement the time intelligence on Date, Month and Year hierarchy...

MDX with linked server - Hi, I have an MDX query which executes within 7 seconds (Profiler 'Query End' - duration = 7288). The resultset contains +70000 rows,...

The connection timed out - I am using BIDS 2008, developing a cube in SSAS. While trying to browse a dimension (by clicking the Browse...


SQLServerCentral.com : Anything that is NOT about SQL!

Talking baseball - Okay, a topic that has NOTHING to do with SQL . . . Came across [url=http://www.sqlservercentral.com/Forums/Topic447796-4-1.aspx]this link[/url] describing the SSC point scoring descriptions,...

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 : General

DTS package and a single global parameter - I've got a SQL2000 DTS that will restore data from a backup (think TodayDatabase and RestoreDatabase). In that package are 15+...


SQL Server 7,2000 : Performance Tuning

Anyone any experience of Quest ActiveRoles QuickConnect? - For some time now, we've been running Quest's ActiveRoles product for administering our Active Directory, and it's been generally fine....


Career : Certification

70-461 - Hi, Id like to take this exam and looking for some good prep.. My T-SQL skills are basic at present...

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