Tag: database-design

Change collations of all columns of all tables in SQL Server

I imported a database with some data to compare with another database. The target database has collation Latin1_General_CI_AS and the source database has SQL_Latin1_General_CP1_CI_AS. I did change the collation of the source database in general to Latin1_General_CI_AS using the SQL Server Management Studio. But the tables and columns inside remains with the old collation. I […]

Which SQL Server field type is best for storing price values?

I am wondering what’s the best type for a price field in SQL Server for a shop-like structure? Looking at this overview we have data types called money, smallmoney, then we have decimal/numeric and lastly float and real. Name, memory/disk-usage and value ranges: Money: 8 bytes (values: -922,337,203,685,477.5808 to +922,337,203,685,477.5807) Smallmoney: 4 bytes (values: -214,748.3648 […]

Why would you want to put an index on a view?

Microsoft SQL Server allows you to add an index to a view, but why would you want to do this? My understanding is that a view is really just a subquery, i.e., if I say SELECT * FROM myView, i’m really saying SELECT * FROM (myView’s Query) It seems like the indexes on the underlying […]

Storing enum values in database

FYI: I explicitly mean SQL Server 2000-8 and C#. So DBMSs with enum support like MySql is not the subject of my question. I know this question has been asked multiple times in SO. But still, I see in answers that different approaches are taken to store enum values in db. Save enum as int […]

Is it good database design to have admin users in the same table as front-end users?

I have users who can login on a front-end page, and admins who can login on an admin page. Should both users and admins be “Users” with different roles, or should they be split in different tables?

Why database designers do not make IDENTITY columns start from the min value rather than 1?

As we know, In Sql Server, The IDENTITY (n,m) means that the values will start from n, and the increment value is m, but I noticed that all database designers make Identity columns as IDENTITY(1,1) , without taking advantage of all values of int data type which are from (-2,147,483,648) to (2,147,483,647), I am planning […]

Naming of boolean column in database table

I have ‘Service’ table and the following column description as below Is User Verification Required for service ? Is User’s Email Activation Required for the service ? Is User’s Mobile Activation required for the service ? I Hesitate in naming these columns as below IsVerificationRequired IsEmailActivationRequired IsMobileActivationRequired or RequireVerification RequireEmailActivation RequireMobileActivation I can’t determined which […]

creating a SQL table with multiple columns automatically

I must create an SQL table with 90+ fields, the majority of them are bit fields like N01, N02, N03 … N89, N90 is there a fast way of creating multiple fileds or is it possible to have one single field to contain an array of values true/false? I need a solution that can also […]

What would the tsql be for this type of database schema?

I asked this question, and someone suggested this type of schema. I’m not familiar with how super/subtypes work. Can you show me a TSQL example of how I would create this database? Another problem I’m having trouble wrapping my head around, is given an order, and a collection of items in that order, how would […]

Twitter like Model using SQL Server/Azure or Graph DB

Is it possible to design a twitter like DB using SQL server? a DB that will ensure high scalability and fast queries. I am building a .NET platform that requires a similar model like twitter (User, Follower, Tweet) and looking into what will fit best in terms of fast queries and scalability. Will it be […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.