Tagged Questions
2
votes
2answers
374 views
Is there any way to do a SELECT query without any optimization in SQL Server?
I am trying to test the speed of SQL server queries.
However, once I do the query once, it becomes a lot faster.
The database is live so I can not make any changes to it, only to the SELECT query ...
2
votes
1answer
73 views
Database design for a table with both bulk insert and single row insert
I have to design a table where records will be inserted in two ways.
Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records ...
0
votes
1answer
49 views
Text Storage and Database Design Optimization in a SQL Server Database
Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to ...
0
votes
0answers
37 views
Struggling with normalisation (database design critique)
I'm relatively new to database design and have been reading up a lot on best practice, and I'm starting to design a schema for a new application that will be developed and I am struggling with a few ...
0
votes
4answers
240 views
Database/Storage engine suggestion for a project involving heavy inserts?
I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities ...
1
vote
0answers
65 views
“Replicate” Table structures with sp_addscriptexec
In our customers environment is a massive central server (running SQL Server Enterprise 2008R2) and some distributed PCs (running SQL Server Standard 2008R2).
The central server has a replication for ...
1
vote
2answers
31 views
Store n:m data with most n:1 tuples
I have these tables:
create table data (
id int identity not null,
ref int not null
);
create table reference (
refid int not null
);
with ~2 million rows in data and ~200k rows in ...
3
votes
5answers
245 views
Using Timestamp plus Integer as Primary Key
In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is:
Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between ...
0
votes
1answer
71 views
Database analysis
I have database which that store the sold products in Orders Table and another table called Transaction
to set the time and the total price for the individual order ,my question is: How to combined ...
0
votes
1answer
53 views
Need Advice on Normalizing Database
I have a group of database tables that lack normalization. They all have a similar primary key called 'doc_index', which is either a PK by itself or part a composite PK.
As you can see below, there ...
1
vote
2answers
63 views
How to find usage of each object in other objects from a database?
I am with the task of taking an old SQL Server database and identify all of it's objects and their relationships, because the one who made it did not bother to use any foreign key, he just made all ...
0
votes
1answer
98 views
Database revision needed for sports events database
This is not a code review but more of a database schema review.
I have a sports events website that I am doing in my spare time. I need assistance with the database design. I am about 80% completed. ...
6
votes
2answers
1k views
Bitmask Flags with Lookup Tables Clarification
I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
0
votes
0answers
40 views
Database design for large incoming data
I've been tasked to design a database to handle a large volume of incoming traffic from sensor readings, possibly 1,000s an hour when it's at full strength.
Each reading will be relatively short - ...
1
vote
1answer
68 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 ...
0
votes
0answers
56 views
Uploaded files corrupted except txt/csv
I have an ASP.NET MVC 4 Application with standard CRUD functionality plus a file Upload capability.
Currently I am struggling with the files being corrupted after the download.
I created a ...
1
vote
1answer
121 views
Horizontal schema vs Vertical schema
I am but a modest SQL DBA. I would like to request your advice on the design of the central part of our new proposed database.
Brent Ozar had something to say about this:
Building A SQL table ...
0
votes
1answer
47 views
Problem creating database using default mdf and ldf path
I'm trying to create a database using the default paths on several SQL Servers at once.
To achieve this I'm trying to use a script that should create the database on each server, however I've run into ...
1
vote
2answers
57 views
Best way to store immutable/read-only copies of data for logging?
I'm storing data about tasks like so
TASK (id, name, employee_id, machine_id, ...)
For logging purposes I would like to store any information about that TASK once it was made.
Since the reference ...
2
votes
2answers
208 views
Design of an application log database
We are generating logs like following example(this is a table no actual pipes):
2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms
2014-06-10 09:00:08.245 | Channel2 | Operation5 | ...
1
vote
1answer
50 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 ...
2
votes
0answers
83 views
Database Design - Multiple Dbs or Single - Security & Data
I've hit a brick wall with this and just cannot make a decision so want to throw this out there and see if there are any issues I have'n't considered.
Basic Topography
I have a system that currently ...
1
vote
1answer
114 views
Slow delete caused by many foreign keys
My DB has about 90 tables. Most of the tables have an UpdatedBy and AddedBy column that have foreign keys pointing back to the user table. With even moderate amounts of data spread throughout the ...
1
vote
1answer
59 views
Delete and Update of Foreign keys [closed]
I'm working on a school project. And I'm required to implement it using Java-EE and SQL Server. I want the design to withstand any kind of faulty user requests. My questions are how should I design ...
3
votes
1answer
98 views
Is this a step in the right direction for normalization? SQL 'inheritance' and Many-to-Many relationships
Context:
So, this is kind of a compound question, but they go together, and definitely wouldn't make sense split up into separate questions.
I've been working on a new database for what will end up ...
0
votes
0answers
72 views
Is there better way to design SQL tables
I need to design a quick application for small warehouse. I just want to check if there is a better design approach. I am using polymorphic associations for comments tables.
Business Rules
If ...
5
votes
3answers
15k views
How to Handle TimeZone Properly in SQL SERVER?
I have some issue which needed to be fixed quickly. My local development server is in middle east. But my production server is in UK. Now, I need to show the date to user to thier timezone. For ...
6
votes
2answers
6k views
Visio 2010 and SQL 2012 - Reverse Enginnering
We have upgraded to SQL 2012 and Am trying to reverse engineer our Dbs into Visio 2010. I setup a connecting using SQL 11 driver and Visio complains that that driver is not supported.
Any one run ...
3
votes
1answer
114 views
What is the correct way to ensure unique entries in a temporal database design?
I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I ...
0
votes
0answers
28 views
Enterprise system database strategy [duplicate]
Here is my current database design:
Separated databases based on area of domain (Sales, HR, Accounting, Core such as login and menus, etc).
Each database has a single service login, each with ...
6
votes
1answer
394 views
Database design for handling 1 billion rows and counting
We receive real-time GPS data at a rate of around 5000 pr. minute (from 4 TCP servers). Each server uses a single connection to insert the data, and buffers data in between inserts. Every 15 minutes ...
0
votes
1answer
62 views
Which one is correct and optimized in sql?
Imagine that I have three entities (EntityA, EntityB, EntityC) that can have some images. So there are two ways:
Make an image table for each entity. It means that EntityA has a image table named ...
1
vote
1answer
106 views
data warehouse design help
I'm struggling with the design phase for a data warehouse. It's not the traditional sales example you find on MSDN or all books etc. I've been tasked with creating an education data warehouse, that ...
2
votes
1answer
94 views
Clustered composite key high fragmentation and fill factor
I've recently made a table to hold the language preferences of my users as follow:
CREATE TABLE [dbo].[systemUserLangPreference](
[systemUserID] [int] NOT NULL,
[langID] [int] NOT NULL,
...
3
votes
1answer
76 views
Help with “Flexible” vs Fixed Tables
We are designing a database in SQL Server to handle sales commissions. A diagram of the current schema looks like this:
There are only a couple of us on the project, and our boss dropped this ...
8
votes
4answers
2k views
Best way to store units in database
I have inherited a large (SQLServer) database with hundreds of columns that represent amounts of one thing or another. The units for these values (e.g. "gallons", "inches", etc) are stored in the ...
2
votes
1answer
70 views
Confused on how a value gets added with the DEFAULT constraint
A recent definition I've read about Default Constraints:
DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added.
I believe I've seen code ...
0
votes
1answer
79 views
understanding (and modelling) grocery shopping lists
I am having a hard time understanding how multiple product orders are mapped out.
What is the best way of modelling a shopping list for multiple customers?
I am developing an C# application that ...
2
votes
1answer
37 views
How can I model and enforce constraints on a categorical item contained in a collection?
Scenario:
There is a collection of items where each item belongs to 1 category.
There is a collection of buckets which may contain at most 1 item from each category. Oh, and an item may belong to at ...
0
votes
1answer
69 views
Beginner table design
I have been coding in C# for a couple of years where I usually try to follow the 'single responsibility principle' which states that each class should ideally be responsible for only 1 goal. Now I ...
13
votes
3answers
4k views
Is nested view a good database design?
I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
3
votes
2answers
44 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 ...
2
votes
3answers
2k views
Software tool to visualize and print DB diagrams [closed]
I have some SQL Server databases and use SQL Server Management Studio to manage them.
However, I find that is not very friendly to create, design and print database diagrams (for existing tables).
...
1
vote
3answers
427 views
GUID stored in a varchar field
I have inherited several databases that use GUIDs as PKs. Instead of all the datatypes being uniqueidentifier most are varchar(50) and some varchar(100). Fields are true GUID some where created by
...
2
votes
1answer
70 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 ...
1
vote
1answer
150 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 ...
1
vote
1answer
118 views
MS Access + SQL Server for 1160 users - Is it worth it? (Please Help!) [closed]
Our school in Delhi (India) wants to develop a professional and robust in-house database (SIS) that should handle vast amount of student information, including admissions, registration, re-enrollment, ...
-1
votes
2answers
769 views
How to convert SQL Server database file to MongoDB? [closed]
I have .mdf / .sdf files created in SQL Server. I want to convert them to MongoDB. How can I do this?
0
votes
1answer
2k views
Float datatype with 2 digits after decimal point
In SQL server float datatype does not display leading zeros after decimal point
declare @num as float
set @num=5.20
select @num
will dispaly only 5.2
But i want it to display as 5.20
After a ...
27
votes
5answers
4k views
Why is there still a varchar data type?
Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)
After working with ...
