| A community of more than 1,600,000 database professionals and growing |
| | | Graphing Performance We have a lot of different database platforms to choose from when building software. Most of us reading this are SQL Server users, and likely relationally biased. However, key-value stores, document databases, graph databases, and more are out there. If you work with developers that embrace change and new options, likely you've been asked about implementing some sort of NoSQL database instead of SQL Server for some project. Maybe you've even been asked to migrate away from SQL Server to an Open Source (OSS) NoSQL platform, with the lack of software cost being a factor. I do think that there are some domains of problems that relational systems don't handle well. Certainly at scales (data volume or rate), there are better ways to deal with some data sets in a less structured and tightly coupled way. We see that in the large scale web companies like Google, Twitter, Facebook, etc. If these companies had tried to build their entire system on a RDBMS platform, they would have struggled to grow, and maybe not even reached the size they are. I've been reading and playing with the new graph capabilities of SQL Server 2017, trying to determine what I think of the concepts. Certainly large scale many-many relationships don't seem to be a strength of relational databases and I've thought there are certain types of queries or data models that might be better handled by a graph database. Then I ran across this report from a few researchers that examine how graph database compare to relational ones. After all, we've grown accustomed to using RDBMSs in many environments and situations. What better way to evaluate the performance of a specialized database than compare its performance in the problem domain its designed to solve to that of a general database platform. The results are a little surprising. Even with a sub-optimal query language, I would have expected the graph database to perform better. Instead, relational seems to handle the reference graph workload better. Raw performance isn't everything. Ease of development and ability to scale are important. There may be other considerations in your system as well, but I did find this to be an interesting paper. We will see how the world of specialized databases handles real world workloads over time as more companies use them, but for now, I'd be skeptical of replacing an existing, working RDBMS with something unproven. I'd need to see a good POC that shows quite a bit of improvement across a variety of metrics, not just scalability. Steve Jones from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums |
| | The Voice of the DBA Podcast Listen to the MP3 Audio ( 4.0MB) podcast or subscribe to the feed at iTunes and Libsyn. The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. |
|
|
|
| | ADVERTISEMENT | | | | Free whitepaper: Solving the database deployment problem with Database DevOps Don’t let the database be a blocker to DevOps success. Learn how Database DevOps helps your team deliver value quicker while keeping your data safe. Download the free whitepaper |
| | | | New SQL Monitor Reporting Module SQL Monitor now includes a new Reporting module. It allows you to create customized reports, combining the individual server performance metrics available in SQL Monitor with summary information on your entire SQL Server estate. These reports can then be exported to pdf and emailed to you on a scheduled basis. Download SQL Monitor now and get a 14 day free trial |
|
|
|
| | | | | Jerod Johnson from SQLServerCentral.com TDS remoting services provide a native SQL experience with local and remote ODBC data sources (contrast with limitations of using OLE DB). More » |
| | Additional Articles from Database Journal Azure SQL Data Sync, which has been lingering in the Preview mode since its introduction seven years ago, no longer requires the use of the Azure classic portal; you can finally access it by using the current Azure portal. Marcin Policht steps you through the process of implementing Azure SQL Data Sync using this interface. More » |
| | SQL Census is a prototype from Redgate Foundry that helps you trace SQL Server user access permissions. Find out what's new and what's next for the tool in this blog post from Santiago Arias. More » |
| | Andrew Pruski from SQLServerCentral Blogs Last week I presented my session on SQL Server & Containers for the PASS Virtualization Group and during my prep I... More » |
| | Daniel Janik from SQLServerCentral Blogs It’s Wednesday and that means another SQL/Oracle post. Today we’ll be discussing NULL Values, which can sometimes be a real... More » |
|
|
| | | Today's Question (by Avinash): What will be the output of this query? select '1/0' = 'Hello' |
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 Script. 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 | | More than ever, the effective management of technology is critical for business competitiveness. For decades, technology leaders have struggled to balance agility, reliability, and security. The consequences of failure have never been greater?whether it's the healthcare.gov debacle, cardholder data breaches, or missing the boat with Big Data in the cloud. Get your copy from Amazon today. | |
|
|
|
|
|
|
| Yesterday's Question of the Day |
| Yesterday's Question (by Thomas Franz): How many and which line IDs (dummy column) does the following procedure call return? IF OBJECT_ID('dbo.p_test_temptables', 'P') IS NOT NULL DROP PROCEDURE dbo.p_test_temptables GO CREATE PROCEDURE dbo.p_test_temptables AS BEGIN DECLARE @sql NVARCHAR(100); IF OBJECT_ID('tempdb.dbo.##tmp') IS NOT NULL DROP TABLE ##tmp IF OBJECT_ID('tempdb.dbo.##tmp1') IS NOT NULL DROP TABLE ##tmp1 IF OBJECT_ID('tempdb.dbo.##tmp2') IS NOT NULL DROP TABLE ##tmp2 CREATE TABLE #tmp (dummy INT, txt VARCHAR(150)); CREATE TABLE ##tmp (dummy INT, txt VARCHAR(150)); SET @sql = 'INSERT INTO #tmp SELECT 1 dummy, ''Insert into existing local #TempTable using EXEC'' txt;' EXEC (@sql) SET @sql = 'INSERT INTO #tmp SELECT 2 dummy, ''Insert into existing local #TempTable using sp_executesql'' txt;' EXEC sys.sp_executesql @sql SET @sql = 'INSERT INTO ##tmp SELECT 3 dummy, ''Insert into existing global ##TempTable using EXEC'' txt;' EXEC (@sql) SET @sql = 'INSERT INTO ##tmp SELECT 4 dummy, ''Insert into existing global ##TempTable using sp_executesql'' txt;' EXEC sys.sp_executesql @sql SET @sql = 'SELECT 5 dummy, ''Insert into new local #TempTable using EXEC'' txt INTO #tmp1;' EXEC (@sql) SET @sql = 'SELECT 6 dummy, ''Insert into new local #TempTable using sp_executesql'' txt INTO #tmp2;' EXEC sys.sp_executesql @sql SET @sql = 'SELECT 7 dummy, ''Insert into new global ##TempTable using EXEC'' txt INTO ##tmp1;' EXEC (@sql) SET @sql = 'SELECT 8 dummy, ''Insert into new global ##TempTable using sp_executesql'' txt INTO ##tmp2;' EXEC sys.sp_executesql @sql IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL SELECT * FROM #tmp AS t IF OBJECT_ID('tempdb.dbo.##tmp') IS NOT NULL SELECT * FROM ##tmp AS t IF OBJECT_ID('tempdb.dbo.#tmp1') IS NOT NULL SELECT * FROM #tmp1 AS t IF OBJECT_ID('tempdb.dbo.#tmp2') IS NOT NULL SELECT * FROM #tmp2 AS t IF OBJECT_ID('tempdb.dbo.##tmp1') IS NOT NULL SELECT * FROM ##tmp1 AS t IF OBJECT_ID('tempdb.dbo.##tmp2') IS NOT NULL SELECT * FROM ##tmp2 AS t IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL DROP TABLE #tmp IF OBJECT_ID('tempdb.dbo.##tmp') IS NOT NULL DROP TABLE ##tmp IF OBJECT_ID('tempdb.dbo.#tmp1') IS NOT NULL DROP TABLE ##tmp1 IF OBJECT_ID('tempdb.dbo.##tmp1') IS NOT NULL DROP TABLE ##tmp1 IF OBJECT_ID('tempdb.dbo.##tmp2') IS NOT NULL DROP TABLE ##tmp2 END GO EXEC dbo.p_test_temptables GO DROP PROCEDURE dbo.p_test_temptables
Answer: 6 lines (all except 5 and 6) because the new created local #TempTables are out of scope Explanation: The procedure has no syntax errors and the availability of the tables is explicit checked, so answer 1 (no lines because of syntax error) is false. It would limit the usage of TempTables, when there would be no way to use them dynamical, so answer 2 is wrong too. Answer 3 (all lines) would be too easy, so it has to be wrong :-) Global ##TempTables are global, so they could never be out of scope and works always (as long there are no errors e.g. because you try to create them and they exists already). -> Answer 5 is true, but as you see later it is not complete. It does not matter, if you are using EXEC or sp_executesql to execute a dynamic SQL statement regarding the availability / usage of temporary tables, which invalidates the answers 6 and 7. Local temporary tables behave a little bit special in the context of dynamic SQL. If they already exists before the dynamic statement is executed, they will be used and can be queried outside of the dynamic statement again (-> lines 1 and 2 will return). But when you create a local temporary table in the dynamic SQL statement ('SELECT * INTO #tmp FROM ...;' or 'CREATE TABLE #tmp (); INSERT INTO #tmp ...' ) they could not be accessed outside of the dynamic statement. So answer 4 (all lines using a local) is wrong and answer 8 (all except lines 5 and 6 (SELECT ... INTO #tmp1)) is true. Links: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql » Discuss this question and answer on the forums |
|
|
| Database Pros Who Need Your Help |
| | Here's a few of the new posts today on the forums. To see more, visit the forums. Installed SS 2016, unable to find default instance or create one - I have just installed SQL Server 2016 and SSMS 2016 on a virtual machine/server. I'm not sure how to find... How to do this in one sql statement without CTE ? - I am able to query Total number of Devices from below CTE. with Alarmreport (ID,SiteName,DEVICENAME) as ( SELECT inp. as ID ,su Find out Who stopped SQL server service ? - Hi All, Is there any way to find out who stopped SQL service ? I went through SQL log and windows event... SQL Server 2014 Developer Edition - Installation Best Practices - I've installed SQL Server 2014 Developer Edition several times on my Win7 desktop. The installation is just for POC, development,... Does HIPAA require that test data not have any real data? - This is a question that I'd ask in a free form forum, but I couldn't find one. If I've missed... When you migrate a database to the same SQL build, but a new environment, post migration tasks? - Hello everyone, I was curious as to peoples take on the following... Lets say you have to move a database to a... Left outer join make duplicate column name - I used code below to select 10 temp tables as one row table. All 10 tables has the same column . How... Columns to rows and rows to column conversion without pivot/unpivot - All I need is convert Column(A1/A2) into rows and rows(1 into Jan) into columns.????Input: Here A1/A2 belongs to say A and... Increasing the PLE - Dear Experts, Please advise what one can do to increase the Page Life Expectancy. The PLE of one of the stand alone databases... Toolbox not available in Business Intelligence Development studio - Hi Good Morning, I am using windows 7 operating system and recently installed SQL server 2008 R2 with Visual studio 2008... PLE - Hi All, Appreciate if anybody can help understand why the physical memory is divided by 4 to determine ideal Page Life... Lookup values similar to VLookUp in Excel - Hi, I am struggling to write a procedure which looks up a value in a lookup table. See desired results below. Any suggestions? Cheers, Julian [code... Trying to create function to create folders (and sub folders) - I'm using sp_OACreate in a scalar function to create a folder if it doesn't exist, and it works fine if... Using dtexec to execute a file system package with project level connection managers - I'm new to SSIS. I'm trying not to be a help vampire ;-) http://www.skidmore.edu/~pdwyer/e/eoc/help_vampire.htm I've read Modeling relational databases - Hi, At the company we are being Audited by some entity... This entity is asking for the ERD Model of each... Full text search with Contains function - Hello All, I have one field in the database table having the value for eg as "Wilson,Gregory T". In my SP there... |
|
| This email has been sent to [email protected]. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the [email protected]. | 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 ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: [email protected] |
|
|
