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

Custom Schemas

This editorial was originally published on 13 Nov 2015. It is being republished as Steve is at the PASS Summit.

One of the things I seem to see more and more is an expanded use of schemas in their development efforts. This isn't something I had done much in the past as almost half of my career was with SQL Server 2000 or earlier, where we didn't really have schemas and we were loathe to tie too many objects to owners. However the last decade has had schemas as a first class citizen in all SQL Server versions since 2005.

I've seen a number of software packages use schemas, but it seemed more as a way of separating out development efforts than any well thought out architecture. However there have been a few interesting uses of schemas as a way of duplicating tables for various uses. ETL and auditing are two of the more common uses, though there have been others.

I ran into an interesting approach recently that I found interesting. A group of developers had added a new schema to separate out their custom objects from all other objects in the database. They did this as the database was created by, and maintained by a third party application. They wanted to create new objects, but wanted them separate from the vendor's objects, and used a new schema.

I thought this was a good idea and wondered who else be doing something similar. This Friday, I wanted to ask the question of the rest of you.

Do you have a custom or reporting schema as a way to separate objects from those that aren't under your development control?

Let us know if you've created some interesting use of schemas. Or are there reasons you don't use schemas? Have you never considered the advantages of using schemas to group  objects in their own section of the database?

I think schemas can be a powerful way of grouping objects, applying separate security, and  organizing your database in a way that makes it easier for developers, and report writers, to manage the complex objects that may clutter up your database.

Steve Jones - SSC Editor

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

 
 Featured Contents

How to filter non-existing records in SQL

aveek22 from SQLServerCentral

Introduction Developing predefined reports in addition to all the ad-hoc queries have been a part of my daily activities. Often I find my stakeholders asking me to prepare reports in which they want to know something that has never happened. It might sound strange, but yeah, that's what their point of concern is all about. […]

Make SQL Server monitoring part of your deployment process for 2020

Additional Articles from Redgate

As we approach 2020, demand for more frequent deployments continues to rise. With this, management of SQL Server availability needs to evolve.

Now’s the time to consider how SQL Server monitoring could improve your deployment performance. In this whitepaper, learn how SQL Server monitoring can help your development and DBA teams work together to remove bottlenecks and enable faster, more reliable deployments.

From the SQL Server Central Blogs - SQL Server Stored Procedures vs Functions vs Views

Bert Wagner from Bert Wagner

Watch this week’s episode on YouTube. SQL Server has several ways to store queries for later executions. This makes developers happy because it allows them to follow DRY principles: Don’t Repeat Yourself....

From the SQL Server Central Blogs - SSIS Scaleout: Cannot open certificate store on the machine

Will Assaf from SQL Tact

When attempting to connect a worker node to the current Master node of a SQL Server Integration Services (SSIS) Scaleout, for on-prem SQL Servers, if you receive the error:...

 

 Question of the Day

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

 

The Top Ten Percent

What does this query do in SQL Server 2017 on a table with 500 rows? 
SELECT
          TOP (10) PERCENT
          cs.SalesAmount,
          cs.CalendarYear,
          cs.Employee
 FROM     dbo.CalendarSales AS cs
 ORDER BY NEWID();

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)

Query the Services

Which services for a SQL Server 2017 install can I query in sys.dm_server_services?

Answer: The SQL Server database engine, Full-Text, and SQL Agent services only

Explanation: The following services can be queried in sys.dm_server_services:

  • SQL Server database engine
  • Full-Test Search service
  • SQL Agent.

Ref: sys.dm_server_services - https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-server-services-transact-sql?view=sql-server-2017

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 2017 - Administration
Maintenance Plans, Agent Jobs, and logging - Open an agent job that's been created by your maintenance plan Open the step Click Advanced Checkmark "Include step output in history" Click OK until you've closed out of the job Open the maintenance plan. Do anything or nothing at all and click save Go back to the job and look at the checkbox Does […]
SSAS remote connectivity issue - Hi , I installed SQL 2017 Deloper edition in my server. Installed with ABCDomian\Administrator. I am able to connect SSAS with windows credentials. Requirement is one user wants to connect SSAS remotely from his SQL management studio..He signin his PC with ABCDomain\xyz username. using his windows authentication not able to connect ssis. I noticed there […]
SSIS Connectivity issue - Hi, I installed SQL 2017 Developer edition. trying to connect all installed SSAS,SSRS,SSIS & DB engine. Iam able to connect all services except SSIS. When i checked the services in sql configuration manager all services are running. in services.msc i see SQL Server Integration services 14.0 also and it is running.. I installed SQL Server […]
SQL Server 2017 - Development
Try/Catch Not Working - I have a log table that gets an insert right at the beginning in order to show the process started. Then it should do the merge/insert and either mark it as failed or succeeded. The initial 'in progress' insert is not happening and the failure is not being logged either. I have purposely tried inserting […]
Convert European format values to US Format - Hi: Is there a function or some code to convert a number that's European (4.100,00 - dots in place of comma for thousands and comma for decimal point) into US (4,100.00)? -278.999 should be -278,999 621.562,61 should be 621,562.61   Any help is appreciated.   Thanks !
SQL Server 2016 - Administration
Cannot connect to DB after Reboot but can after SQL Server engine restart - After a reboot of the computer, we see in the SQL Error Log problems to connect to one of the database STORESQL.  Our software using this database cannot connect obviously.  But after a simple restart of the SQL engine, the connection to the database is possible.    Do you have an idea why?   The logs […]
Add multiple databases to availability group - Hello together, We have about 50 databases that are not in full recovery mode and not in always on included. Do you know what the fastest way is to add these databases into an Availability group and also Change to full recovery mode without SQL Mgmt Studio Wizard. Maybe someone has a script suggestion for […]
Enable "force encryption" on SQL server - My production DB server is running without SSL since day one (force encryption is off at configuration manager) Someone is asking to use SSL connection from application server to DB server. If i issue a certificate and enable force encryption, should all existing clients be changed to encrypted connections too? in this case, we have […]
SQL Server 2016 - Development and T-SQL
How to get previous value(last matched records results) in SQL query result - Hi Every one, I need your help to achieve results. Can you please help me any one? My requirement is get last matched records results for unmatched records. I mean when matching the Name column with "ABC" then display corresponding records results value(988777) in expected result. else display previous result value.  llly, when matching name […]
Administration - SQL Server 2014
Why Index column is NULL for DMV - sys.dm_db_index_physical_stats - I am running the DMV (sys.dm_db_index_physical_stats) to get the fragmentation for a database, however when I get the results the Index column is NULL for 3 tables even though the table in question has 2 x indexes on it and the avg_frag percentage is >80%.  Why is this?  I've tried DBCC INDEXDEFRAG for these 3 […]
Development - SQL Server 2014
Report runs forever.` - Not sure why this report runs continously... Select distinct H.Customs_Entry_Num as [Entry Num] ,H.Entry_Summary_Date ,L.Part_Num ,L.HTS_Num ,l.HTS_Value ,L.Line_Item_Duty AS Duty ,z.HTS_NUMBER ,z. PTNR_ID ,Z.ELIGIBILITY ,z.Created_Date ,z.COMPOSITE_PART FROM ADHOC.ATS_ESH H INNER JOIN adhoc.ATS_ESL L ON h.TRANS_SK = l.TRANS_SK LEFT JOIN [TSI].[ZATS_BROKER_FEED] Z ON L.Part_Num = Z.COMPOSITE_PART AND Z.CREATED_DATE = ( SELECT max(Z.CREATED_DATE) FROM [TSI].[ZATS_BROKER_FEED] Z WHERE […]
SQL Server 15 - Administration
Polybase on windows auth - Hello, im trying to config polybase to take data from my other sql and struggling a bit here. Does anyone finished such task successfully? The thing is to use windows authentication here. So i already have database master key. With that im going to create database scoped credentials: CREATE CREDENTIAL [AppCredWind] WITH IDENTITY = N'domain\user', […]
SQL Server 2008 - General
passing parameter to stored procedure in bcp command - Hi, I have to take output of stp using bcp command. the stp contains one parameter. I want to know how to pass parameter to stp and get the output. the batch contains below bcp command set DBName=DBLive set DBServerName=ServerLive set FileName=ClosePrice_N.csv bcp "EXEC "%DBName%..stp " " queryout %FileName% -Uadmin -Padmin -S%DBServerName% -t, -w How […]
SQL Azure - Administration
OPENJSON support for SQL Azure Encryption - Does OPENJSON supported for a Azure SQL DB encrypted with AE. especially for insert, update and delete.
Reporting Services
Maps - What is the best way to add location maps to SSRS - Hello, If I wanted to add a map of our employees and their work sites to an SSRS report. What is the best way to go about this. What is the best way to integrate maps? Thank you,
 

 

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

 

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