They are different things. A synonym is an alias for the object directly, a view is a construct over one or more tables. Some reasons to use a view: May need to filter, join or otherwise frig with the structure and semantics of the result set May need to provide legacy support for an underlying … Read more
Foreign keys with constraints(in some DB engines) give you data integrity on the low level(level of database). It means you can’t physically create a record that doesn’t fulfill relation. It’s just a way to be more safe.
Create a system queue, each message added to this queue has a list of “consumers” and the content. The main message pump processes each message and sends the message to all consumers. Lets say 2 people befriend each other. You add a message to the main system queue that A is friends with B and … Read more
Are you looking for something like this? http://sqlfiddle.com/ or http://sqltest.net/
If you’re absolutely sure your numbers will always stay within the range of smallmoney, use that and you can save a few bytes. Otherwise, I would use money. But remember, storage is cheap these days. The extra 4 bytes over 100 million records is still less than half a GB. As @marc_s points out, however, … Read more
I did as Donnie suggested, but without the role field, because it complicates things. This is the final implementation: DDL: CREATE TABLE Employee ( ast VARCHAR(20) not null, firstname VARCHAR(200) not null, surname VARCHAR(200) not null, … PRIMARY KEY(ast) ); CREATE TABLE Administrative ( employee_ast VARCHAR(20) not null REFERENCES Employee(ast), PRIMARY KEY(employee_ast) ); CREATE TABLE … Read more
Yes – you can have a foreign key that references a unique index in another table. CREATE UNIQUE INDEX UX01_YourTable ON dbo.YourTable(SomeUniqueColumn) ALTER TABLE dbo.YourChildTable ADD CONSTRAINT FK_ChildTable_Table FOREIGN KEY(YourFKColumn) REFERENCES dbo.YourTable(SomeUniqueColumn)
For simple equality checks (=), a B-Tree index on a varchar or text column is simple and the best choice. It certainly helps performance a lot. And a UNIQUE constraint (like you mentioned) is already implemented with such an index, so you would not create another one. Of course, a B-Tree index on a simple … Read more
VARCHAR(MAX) if it’s all going to be ascii-based, say for basic HTML tepmplates NVARCHAR(MAX) if the HTML could contain any content NVARCHAR will double your storage use as it uses double the amount of space as VARCHAR. HTML itself does not require NVARCHAR, only the content in-between the HTML tags could based on the language, … Read more
There are several approaches to multi-tenant databases. For discussion, they’re usually broken into three categories. One database per tenant. Shared database, one schema per tenant. Shared database, shared schema. A tenant identifier (tenant key) associates every row with the right tenant. MSDN has a good article on the pros and cons of each design, and … Read more