Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

The Developer Arguments for Stored Procedures

In all of the decades that I've been working with SQL Server, many people have been preaching the benefits of using stored procedures, In all of that time, the vast majority of developers that I've worked with have not wanted to actually write, or even call them. In one job, I offered to write all procedures within a day for a development team, and they still didn't really want to use them. I made them do this by revoking permissions on many tables, but they still grumbled about the overhead of procedures.

I was having a discussion with a friend recently about using procedure, and he was having a similar battle. There was a group of developers that wanted to embed SQL  or use LINQ->SQL in their applications instead of calling stored procedures. It was frustrating, but he managed to secure a meeting to discuss the concerns with the developers.

As we talked, we tried to frame the problem from the perspective of a developer. Why would or wouldn't you want to use stored procedures? We had a few ideas, but really, I'm curious what you think today. Do you have any debate points that make sense from a developer point of view?

I certainly do understand the ease of just writing SQL in whatever IDE you use for C#, ASP.NET, Java, etc. Not wanting to open up SSMS or some other tool makes sense. I also get that it's nice to see all the code in one place, and not have some split between the application and the database.

On the other hand, the idea of encapsulating code in the database feels a lot like the reason developers refactor code into new classes and methods to provide a clean interface between different types of functionality. Not to mention you push a lot of the performance work down on the database and to the DBAs.

If you think stored procedures do or don't make sense from a developer point of view, let me know today. Leave a comment in the discussion and explain your position.

Steve Jones - SSC Editor

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

 
 Featured Contents

Displaying Hierarchical Data

Adam Aspin from SQLServerCentral.com

Producing hierarchies from SQL tables can necessitate joining a table to itself. This article will explain how you can do this.

Meeting your CCPA needs with Data Classification and Masking

Additional Articles from Redgate

This article will explain how to import the data classification metadata for a SQL Server database into Data Masker, providing a masking plan that you can use to ensure the protection of all this data. By applying the data masking operation as part of an automated database provisioning process, you make it fast, repeatable and auditable.

Creating Time-Intelligence Functions in DAX

Additional Articles from SimpleTalk

DAX contains a host of time-intelligence functions with exotic names such as SAMEPERIODLASTYEAR and PARALLELPERIOD. In the last article in this series, Andy Brown explains how to write measures using the range of date functions in DAX, and also explains how time-intelligence functions work behind the scenes.

From the SQL Server Central Blogs - Setting 2020 Learning Goals

Steve Jones - SSC Editor from The Voice of the DBA

I deliberately didn’t set any goals at the start of 2020 since I was taking six weeks off for a sabbatical. That’s done, and it’s time to look forward....

From the SQL Server Central Blogs - ASF 029: Tomaz Kastrun interview

KamilN78 from SQL Player Blog

Introduction Tomaž Kastrun is BI & DEV developer, data analyst & data science. With more than 15 years of experiences in the field of databases, business warehouses and development,...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Identity Values

I have this query that I run in my database: 
SELECT
               SchemaName = OBJECT_SCHEMA_NAME(t.object_id, DB_ID())
             , TableName = t.name
             , ColumnName = c.name
             , ic.last_value
FROM
               sys.tables AS t
    INNER JOIN sys.columns AS c
        ON t.object_id      = c.object_id
    INNER JOIN sys.identity_columns AS ic
        ON ic.column_id     = c.column_id
           AND ic.object_id = c.object_id
WHERE          c.is_identity = 1;
What does the ic.last_value represent?

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Jones - SSC Editor)

The gMSA

What is a gMSA?

Answer: A group Managed Service Account

Explanation: A gMSA is a Group Managed Service Account, an account managed by the domain that can be used across many services and many machines. These are a good choice for modern SQL Server service accounts. Ref: Group Managed Service Accounts Overview - https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/hh831782(v=ws.11)?redirectedfrom=MSDN

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.

SQL Server 2016 - Administration
Disk latency Writes and the Recovery Interval - I have a number of clients in Azure who are experiencing Disk Write Latency of 200ms and above. The issue in Azure is IOPS are capped based on the size of the disk, so on a P30, 1TB disk you are capped at 5,000 IOPS per disk. This creates a number of issues: If you […]
SQL 2016 Merge replication issues - Last week, I ran into two merge replication related issues: For non-production, it's SQL 2016 to SQL 2016 merge replication, one schema change(a new column) was unable to replicate from publisher to subscriber. I had to reinitialize the subscriber with a new snapshot to get the replication article in sync. On production, it's SQL 2016 […]
SQL Server 2016 - Development and T-SQL
Datatype differences when restoring a database from one server to the next - Hello, I'm performing a backup/restore operation from a SQL Server 2012 database server to a SQL Server 2016 database server. The database is currently set to SQL Server 2008 compatibility within both database servers using the same 'SQL_Latin1_General_CP1_CI_AS' collation from the server level to column level. Now when the database finished restoring on the 2016 […]
Roll multiple records into 1 - I'm trying to roll multiple records into one replacing null value. IF (SELECT OBJECT_ID('tempdb..#Table50'))is not null DROP TABLE #Table50 CREATE TABLE #Table50 ( MyID int, A int, B int, C int) INSERT INTO #Table50 ( MYID, A, B, C) SELECT 1, NULL,NULL, 53 UNION ALL SELECT 1, NULL,NULL, 54 UNION ALL SELECT 1, NULL,924, NULL […]
SQL UniqueIdentifier question - Hello, Our reporting database truncates and loads latest data during each import and generates Unique Identifiers for Tasks and Project UIDs .   Example: ProjectTable TaskTable We have a SharePoint list where incremental updates are done using TaskUID from Task Table and ProjectUID from Project Table. If with every refresh, Project and Task tables regenerates […]
Extract string from string that contains specific first two characters - Hi I would like to extract from a full string the 'XX....' string. This specific string inst in the same position in my full string. - this string always starts with XX - this string always contains 6 characters   thanks in advance,
Receiving error: The xml data type cannot be selected as DISTINCT because it... - In the below query, the data returned in the query is in xml format.  I'm receiving an issue with duplicate records being returned however, so I wanted to set it to "Select DISTINCT...", however in doing so, I'm now receiving this error, but I'm not sure how to correct it to ensure I still receive […]
Administration - SQL Server 2014
Upgrading SQL 2014 to SQL 2017 cluster - Hi All, Not sure whether to post it here or in SQL 2017, but here goes.... We have a Win 2012 R2 / SQL 2014 cluster for a mission critical database. It's a 3 node cluster with nodes in different subnets. As DR we logship to a 4th off site server. We want to upgrade […]
SQL Server 2019 - Administration
MIGRATE SQL 2014 to 2019 (not UPGRADE) - Anyone had experience migrating a SQL Server to the latest version? We have 2 servers, SQL 2014 SP3-CU4 + Windows Server 2012 R2 and would want to migrate both server to SQL 2019 + Windows Server 2016/2019. Have to point out the following: The servers has a transactional replication running. The subscriber server is also […]
SQL Server installation with British English - What little hair I have left is being torn out in chunks trying to get to the right part of Microsoft to get a reasonable response, so I'm hoping you helpful lot here can answer my problem. When I try to install SQL Server 2019 and look at the instance properties, I see it has […]
Availability groups and sql agent - I have A proof of concept 2019 sql server with 2 active nodes and a read only reporting node. I'm looking at the SQL agent jobs and don't want them to fire on both nodes - i know the jobs need to be on both nodes for a failover scenario so my plan was to […]
SQL Server 2019 - Development
Indexed view using remote database - Dear Friends, i have an requirement where i need to create a indexed view with a table which is available in different server.  but i get an error as Remote access is not allowed from within a schema-bound object.  I understand this problem. but i want to create as it is.  i can create the same table […]
SQL Server 2008 - General
Continuously current list of attached files in SQL Server - This isn't strictly a SQL Server question, but there are some pretty sharp people on here, and I'm probably not the only one with a problem like this.   I have a database that has associated files in a folder/subfolder structure on the server – Windows Server Standard, 2007, SP2. The associated files are mostly […]
Integration Services
SSIS Class not registered - Hello, I installed SSIS on a server by itself, it is 2016 version 13.0 version I followed permission directions from the link below: https://docs.microsoft.com/en-us/sql/integration-services/grant-permissions-to-integration-services-service?view=sql-server-2014 I tried to connect to SQL management studio version 16.3 version, to SSMS 18.4, and I get the following error message, I attached it. not sure why or what to do […]
General
Trying to Sum by different levels - Hello everyone, If any one could please help me with this? I'm trying to add additional sum by Family and Sum by Store level Trying to get this result It seem that i only know how to use group by at the end of my statement.
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -