Tagged Questions

0
votes
0answers
94 views

How to create table set from several XSD in SQL Server 2012 or with VS 2012

Is there any solution in T-SQL or in C# how to create XML schema collection in T-SQL or C#? Problem is I cannot use: USE Database_test GO CREATE XML SCHEMA COLLECTION Collection_from_XSD AS N' ...
3
votes
1answer
66 views

Many:Many with Shared Relation

I'm modelling data with multiplicity like this: Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I ...
0
votes
0answers
67 views

How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
0
votes
1answer
127 views

Granting permissions only on a set list of objects

I have a SQL Server 2005 database with a large number of tables in the dbo schema. I now created a new schema (call it myschema) that only has three table-valued functions and two stored procedures in ...
1
vote
1answer
186 views

Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Are there differences between Owned Schemas and Role Members? From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked. ...
1
vote
2answers
235 views

SQL Server - backup / restore schema only (not a duplicate if that's what you're thinking!)

We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - ...
5
votes
2answers
82 views

Relationship that are optionally more specific

Forgive my title, I couldn't think of anything that accurately describes what I'm talking about. I currently have the following relationship. CREATE TABLE Events ( ID INT IDENTITY(1,1) NOT ...
3
votes
2answers
212 views

Backup Only Database Schema Everynight

I have an instance with 15 databases on SQL Server 2012. I will set up to backup system which will work every night. I want to backup only the schema because several databases are very big. I haven't ...
2
votes
2answers
477 views

One database with two schemas: cross-schema View permissions issue?

I have a database on SQL Server 2008 R2 that has two schemas, the default dbo and a new one called Test. I have a view in the Test schema called View1 that selects from dbo.Table1. Both schemas are ...
2
votes
1answer
576 views

Grant select to dbo view without select on non dbo tables

SQL Server 2008 R2 We have a login & user called "JoeBlogs", this user has it's own default schema of JoeBlogs - it has Public access only to the database. We then granted it SELECT on a dbo ...
2
votes
3answers
211 views

How to find out what caused a schema change?

I am investigating some performance issues on my SQL Server 2008 R2 server. Some queries are being recompiled due to a "schema change". I am pretty sure no one is changing the schema. All databases on ...
0
votes
2answers
584 views

Workarounds for setting default schema for domain group?

I am currently working on a project for a client, they have asked me to change the database from Oracle to SQL Server. So we migrated the database across to SQL Server 2008, when this was migrated ...
3
votes
1answer
86 views

What mechanisms do you use to notify third parties of future schema changes?

How do you notify third parties of future SQL Server schema changes other than through external documentation? I've started implementing a deprecation warning extended property on objects that will ...
2
votes
1answer
115 views

Schema for 1 <-> 0..1(+), many related but only one primary

Using tools available in SQL Server 2008 and Entity Framework, what is a solid way to represent a one-to-many relationship, where there is an underlying unique relation that I need to enforce? My ...
5
votes
1answer
453 views

Do schema changes “break” SQL Server 2012 AlwaysOn or are they handled transparently?

My organization is planning to adopt SQL Server 2012 AlwaysOn and I am trying to understand what impact (if any) it will have on our application upgrade process. We release application updates on an ...
1
vote
1answer
97 views

Default Schema Problems on server

When i connect to SSMS on my server through remote desktop and query my database using: SELECT * FROM [sys].[database_principals] WHERE [principal_id] = USER_ID() I am getting the correct results ...
3
votes
1answer
142 views

Always being logged in as SA and not Windows account

First off i would like to appologise if i sound stupid as i do not have much knowladge of SQL Server. I have recently started a project working with SQL Server 2008 R2. I have access to the database ...
2
votes
1answer
250 views

.NET SQL Server Authentication Schema issue

I am currently working on a project for a client. The application is written in ASP.NET C#. The Database used is SQL Server 2008. The project has been to migrate the old Oracle database to a new SQL ...
1
vote
1answer
47 views

What steps are required to move data from a custom schema in one database to the default schema in new database

Lets assume for the sake of argument that I have the following tables in a single SQL Server database. mySchema.Users mySchema.Products -- secondSchema.Users secondSchema.Contracts ...
0
votes
1answer
211 views

Tools for synchronizing database schema with Oracle, SQL-Server and Netezza databases

I'm looking for software tools to move database schema for Oracle, SQL Server and Netezza. We use Oracle for our ODS, Netezza for our Data Warehouse (for it's bulk loading capabilities) and SQL ...
3
votes
1answer
1k views

Copy complete structure of a table

Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say: select * into dbo.table2 from dbo.table1; This is just a simple copy of the ...
4
votes
2answers
244 views

How to store many smallish time-series in a relational DB?

Input Data Multiple Tests Beds generate measurement data of various complexity. In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
4
votes
2answers
397 views

What's a good relational structure for units and complex unit conversions?

My company is in the Energy industry and I need to come up with a good way to represent the conversion of units of measurement. I've done some searching and have yet to find a good article covering ...
2
votes
2answers
73 views

Is it safe to use “Safety” as a schema name?

I was considering using "Safety" as a new schema name in SQL Server. In SSMS, the term Safety is colored as a keyword, but this term is not listed as a reserved keyword in the BOL. The database does ...
1
vote
1answer
213 views

Replicate/Mirror SQL Server schema to slave databases

I have a empty database called MASTERDB and around 10, or more, slave databases that are copies of the MASTERDB database. MASTERDB will be always empty, as I only need the schema. No data is going to ...
10
votes
4answers
4k views

Should dbo schema be avoided?

When it comes to the dbo schema: Is it a best practice to avoid using the dbo schema when creating database objects? Why should the dbo schema be avoided or should it? Which database user should own ...
5
votes
2answers
284 views

How recording of every change of a row in a database is generaly stored?

In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
4
votes
1answer
2k views

SQL Server: hierarchy of permissions for schema?

We have several databases under development and I would like to start using SQL Server schemas for permission management and logical grouping of database objects. Under Schema Properties there is ...
2
votes
2answers
11k views

SQL Server 2008: How do I change the default schema of the dbo?

When I attempt to change the default schema of the dbo in SQL Server Management Studio, the ALTER fails and states that the dbo account's schema cannot be changed. Are there any tricks that can make ...
5
votes
3answers
533 views

Indexing - Uniqueidentifier Foreign Key or Intermediary mapping table?

Looking for some expert views on this guys - I'm not a DBA by trade so would appreciate any advice. Comments on the rest of the schema aren't required as it is purely fabricated to illustrate my ...
7
votes
4answers
703 views

Guidance for using composite keys to identify rows

Is it good practice (or would it have any adverse effects) to use a set of 4 columns to identify a row as being unique (one being a foriegn key, the other three being float data types)? I'm attempting ...
11
votes
3answers
2k views

Schema design - best practices?

I understand the capabilities of SQL Server Schemas, but what are the best practices? Sure they give another layer of security, and provide logical grouping of database objects within the database, ...
1
vote
1answer
310 views

Is it possible to make schema changes on a replicated subscriber database?

I have a table that is published via transactional replication. A developer has requested modifying the subscribed table to add a column that is to be used for reporting purposes. The column will be ...