All Questions
Tagged with sql-server-2008-r2 database-design
55 questions
0
votes
1
answer
38
views
Transactional replication Snapshot agent not starting
I am trying to consider all the options before setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL ...
- 13
1
vote
0
answers
35
views
Remove Transactional Replication Manually or Via SSMS?
I am setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL recovery mode. Log backup is taken every 15 ...
- 37
0
votes
1
answer
96
views
Table structure where name changes to be retained
Example Situation - order table with client list and products. In below example table , when client name/company changes (company name may change) when updating records need to retain old company name ...
0
votes
1
answer
366
views
Survey Database Structure
I am building a database to feed into a PowerBI dashboard.
The data is extracted as CSV (SurveyResults) from Survey Monkey which will be loading into the MSSQL database.
The outcome i am after is the ...
- 137
3
votes
1
answer
68
views
Ensuring Uniqueness Across Columns
I'll lead with this: Perhaps the database design is wrong, so I will accept a re-design as a solution!
Consider the following table:
ID | TypeA | RefA | TypeB | RefB
-- + ----- + ---- + ----- + ----
...
- 457
2
votes
2
answers
103
views
Basic RDBMS table relations
If you have a design of a "master table" and several "subtables", is it better* that the master table "link" to each of the sub tables, the subtables link to the master, or for bi-directional links be ...
- 968
1
vote
2
answers
238
views
Schema Generation
I am trying to generate Schema with 90000 tables from Generate Script option but after sometime it returns error. DB size 600 GB. What's the other way around?
Microsoft.SqlServer.Management....
- 125
0
votes
1
answer
36
views
Any reason for declaring a view with cross-database SELECTs for the same database?
I currently have to take over an old project and found a single view that's kind of a special snowflake because it's declared this way:
USE [DbName]
GO
create view [abc].[vXyz] as
select id, name, ...
- 407
0
votes
2
answers
228
views
Does using lower Isolation Level improve OLTP performance?
We have a SQL Server 2008 R2 instance which gets millions of rows of data per day from automatic devices such as CTI and AVR. On the same instance there are many heavy reports which analyze the data.
...
- 301
1
vote
1
answer
66
views
How should I build a DB to hold data about another database
I know that this is a strange question. I need ideas and/or advice, not necessarily "how-to"s.
I work with data from a 3rd party tool/database. I do not have permissions to modify this production ...
- 121
0
votes
1
answer
37
views
Developing Access DB to SQL? [closed]
I built a database several years ago in Access to track over 4K pieces of equipment in and out of a shop, where it was going, current status, etc. It's dying. I would like to upgrade it, but unsure if ...
- 1
2
votes
1
answer
2k
views
Efficient way to store multi choice in a column with few options
I am looking for advices on how to handle multiple choices in a column?
By way of example lets say selecting a fruit for lunch. Options are Apple or Pear. And you are 100% sure those two options ...
- 159
3
votes
0
answers
105
views
why some one will use approximate data types for sales
I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
- 131
1
vote
1
answer
239
views
Really big LOG backups on a DB with Image field
Normally, our log backups has < 10MB size In a big database with a lot of transactions per hour ).
But now we have a database, that is storing Images on a Image field.
the problem is, the ...
- 7,484
1
vote
1
answer
596
views
What is the best SQL Server field type to cast to when storing a UNC path stored in a sql_variant field?
What is the best choice of field type to cast to when storing a UNC path in a sql_variant in Microsoft SQL Server 2008?
It appears that I could store up to 4000 characters according to Microsoft ...
- 113
4
votes
1
answer
191
views
What is the best design for dynamic assignment of locations for variable sized grid boxes to occupy?
I want to build a database that will have limited single locations to put boxes. It's divided by lanes and bays.
Imagine a 4x4 grid, with a total of 16 slots, and every time I query the tables, I ...
- 141
2
votes
1
answer
3k
views
Soft delete to improve performance: all wrong?
A few years back I read soft delete would improve performance of deletion, because instead of actually removing the item and impact the clustered (and all other) indices, it would just change a value.
...
2
votes
1
answer
209
views
What should we do when same column fields occurring multiple time in many tables?
I have more than 20 tables in my database. There are some columns which occur in almost all of the tables. In that case what should I do? Do I put all those columns in every table or is there a better ...
- 123
2
votes
0
answers
68
views
Filegroup design for Document Table for BLOB Storage
I have a table for storing documents as VARBINARY types in my database (I'm not using filestream). My database is setup with only one data file, but I am wondering if there is a benefit to creating a ...
- 19
-1
votes
2
answers
5k
views
How to Execute multiple select statements in SSIS 2008 using Data flow task [closed]
I am migrating data from one database to another database using SSIS package and i need to write multiple select statements for same table depending on conditions as mentioned below.
Actual Queries i ...
0
votes
1
answer
2k
views
Copy of a database with an existing database with schema and data in sql server
I have two databases one is having master kind of tables and other will have transaction data.
I should create a new database with a structure of an existing database to my clients. Here is the ...
- 125
3
votes
1
answer
577
views
How to create a Relation between a table and a many-to-many relation table
I am buildng a new database inside an Sql server 2008 r2.I have the following tables & business rules:-
Skill that have predefined levels assigned to it, such as junior, senior, manager.
Staff ...
- 1
1
vote
1
answer
214
views
optimize design for a table with 50 columns [closed]
I have a table with 50 column and one of my friend has told me to split the table in 4 different table to save 12 column in each table. He proposed a design to have Master table and refer the PK of ...
- 80
2
votes
2
answers
50
views
Report individual changes to data
I need to produce a daily extract on the individual changes to a dataset. So, there are 14 tables containing product and customer information and the extract must only show records from each table ...
- 123
1
vote
1
answer
707
views
Enforce referential integrity between two groups of data
I have two sets of records:
A collection of rules.
A collection of actions to perform based on those rules.
What I'm trying to model is something like this:
Multiple rules can be grouped together ...
- 111
0
votes
2
answers
473
views
Are #tables and #procedures 'not best practice' or 'bad practice'?
I am working on a project and part of it is an interface tool that executes SQL code stored in a xml file to import/export files.
In the file, temp procedures and temp tables are used quite heavily (...
- 1,523
1
vote
1
answer
680
views
Should I use a surrogate key in a mapping table?
I am deploying a mapping table which connects Items to ItemGroups
Though an Item will only ever be in a single ItemGroup please accept a priori it is not appropriate in my use case to append a column ...
- 1,693
13
votes
3
answers
44k
views
How to create new table with same constraints and indexes?
I am Creating a new table with Primary Key Constraints and a Non Clustered Index in that table.
I know, I would like to create another table with same structure and values as well keys and indexes ...
- 857
7
votes
6
answers
68k
views
How To change the column order?
How To change the column order in sql server 2008 R2?
Eg: My table order is Eid------Ename-----Esalary
Know i want this format like Eid---Salary----Ename
Thanks.
user36326
0
votes
2
answers
21k
views
how to store multiple value in column in sql server [closed]
I am creating job post application. In my first table the columns are This is my master table
ID
Position
jobDescription
minExp
maxExp
LastDate
InterviewDate
Project
HiringManager
interviewer
...
- 1
3
votes
1
answer
2k
views
Most efficient queries on flags in TSQL: bit field or table join?
Looking for the most efficient database structure for selecting rows from a large table based on one or more true/false values. For example, whether or not a user has opted in to marketing ...
- 141
3
votes
2
answers
67
views
Calulating savings between two schemes
I've normalized a single table to reduce the number of nullable columns from 3 down to 0. The idea was to save a little drive space but I'm seeing a 45% decrease in space needed to store the same ...
- 221
2
votes
1
answer
84
views
Database Design guidance required
I have scenario where there is Table T with columns PK,A, B, C,D
TABLE t
(
PK,
A,
B,
C,
D
)
i have to insert data from different sources ie columns A,B,C,D are calculated separately..
and they same ...
- 109
1
vote
1
answer
2k
views
Autoclose property resets to true after attaching db even if model is set to false
I am using SQL Server Express 2008 R2.
The auto close property of a newly created database will be set to value same as model db. In my case I have set autoclose to false for model so that when I ...
- 3,131
1
vote
1
answer
3k
views
SQL Server performance for tables having large number of columns [closed]
I have a requirement where I need to create a table with about 600 columns. Out of 600 columns about 200 columns will be XML columns. Other column will be of other datatypes such as int, varchar, ...
- 3,131
3
votes
1
answer
275
views
Mathematical method for spotting irregularity in numbers flow
I am trying to build an engine which is going to predict orders for items inside shop.
Three main variables are:
Time-dates (x-axe etc 1.1.2013,1.2.2013 etc I do calculation one per day)
Available ...
- 2,433
2
votes
2
answers
237
views
Record versioning and promotion
Let's say we have this hierarchy:
-World
--USA
---WA
----Seattle
-----Downtown
------1st Ave
-------945 1st ave
------3rd Ave
---...
- 161
0
votes
1
answer
1k
views
How to store a sequence of events related to a transaction
I am using Sql Server 2008 R2.
I have two tables that describe import processes and export processes.
Import table has these columns with others that just have details about each type of import
...
- 417
3
votes
1
answer
702
views
SSIS Design consideration with object variables
I have an SSIS package for SQL Server 2008 R2. In the package I am required to work with an Access database (one to many) and iterate through it running validation checks with the data. The validation ...
user507
6
votes
1
answer
3k
views
Best way to index a table with two datetime columns that are equally queried
Sql Server 2008 R2.
I have a table with ~70m records, about 10 inserts per second.
It's currently clustered on a CreatedAt datetime column, which always increases. 50% of queries involve this ...
- 409
6
votes
1
answer
2k
views
SSIS package with BULK data load
I analyzed a C# program over the weekend for a customer that basically does the following:
Truncate Table
Fetch Data (source on these is either remote database or local database where job runs)
...
user507
3
votes
1
answer
807
views
Applying user-defined fields to arbitrary entities
Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and ...
- 186
1
vote
1
answer
1k
views
Issues on defining a PK on a nvarchar column and indexing the FK referring this PK
Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255).
The value exists in this columns are ...
- 3,694
8
votes
2
answers
33k
views
Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?
I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when ...
- 191
7
votes
2
answers
4k
views
Design best practices for last tracking with indexed view
My tables structure is below :
TbDoc (ID int , ...)
TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...)
I want to have indexed view to get last TbDocActions columns for each ...
- 1,195
11
votes
3
answers
34k
views
Tools for Identifying Needed Indexes
I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?
- 1,195
1
vote
1
answer
332
views
Choosing the right field data type in SQL Server 2008
I got a request to create a new table that will store certain information for customers. The definition of the table is somewhat irrelevant except for one field. This field will store ANSI characters ...
- 133
14
votes
2
answers
9k
views
Do I need a separate Id column for this "mapping" table?
I have a table of Producers and a table of Products, both of which are of the form:
Id - int, Primary key
Name - nvarchar
A Producer can carry multiple Products, so I was going to create a table ...
- 29.8k
1
vote
3
answers
7k
views
Adding Row Version to SQL Server table
I have a table in a database with three columns: ID, Key and Value. Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's ...
- 597
2
votes
1
answer
2k
views
DATETIME2 and IDENTITY as composite key
I am currently developing a new database schema with an opportunity to do things right. The purpose of the development effort is to collect data (events) from various sensors that will be recorded ...
- 23