A database is the main container, it contains the data and log files, and all the schemas within it.  You always back up a database, it is a discrete unit on its own.

Schemas are like folders within a database, and are mainly used to group logical objects together, which leads to ease of setting permissions by schema.

### EDIT for additional question

    drop schema test1

&gt;Msg 3729, Level 16, State 1, Line 1&lt;br&gt;
Cannot drop schema &#39;test1&#39; because it is being referenced by object &#39;copyme&#39;.

You cannot drop a schema when it is in use.  You have to first remove all objects from the schema.

Related reading:

1. https://stackoverflow.com/questions/529142/what-good-are-sql-server-schemas
2. [MSDN: User-Schema Separation](http://msdn.microsoft.com/en-us/library/ms190387.aspx)

Schema is a way of categorising the objects in a database. It can be useful if you have several applications share a single database and while there is some common set of data that all application accesses.

Database is like container of data with schema, and schemas is layout of the tables there data types, relations and stuff

Schema says what tables are in database, what columns they have and how they are related. Each database has its own schema. 

Schema in SQL Server is an object that conceptually holds definitions for other database objects such as tables,views,stored procedures etc.

I have the situation where I&#39;m initializing my model in DatabaseInitializer() for EF 4.1 and get this annoying error `&quot;Validation failed for one or more entities. See &#39;EntityValidationErrors&#39; property for more details.&quot;` So, I go to this EntityValidationErrors and there is a field `{System.Data.Entity.Validation.DbEntityValidationResult}` which gives me no information at all about what field it was unable to initialize. 
Is there a way to get more info about this error?

**To clear things out:**

I know how to fix the string length problem. What I&#39;m asking is how do I get the exact field name that is breaking the model. 

Getting exact error type in from DbValidationException

I am trying to get the executing assembly version in C# 3.0 using the following code:

    var assemblyFullName = Assembly.GetExecutingAssembly().FullName;
    var version = assemblyFullName .Split(&#39;,&#39;)[1].Split(&#39;=&#39;)[1];

Is there another proper way of doing so?


How can I get the executing assembly version?

What&#39;s the difference between a Database and a Schema in SQL Server? Both are the containers of tables and data.

If a Schema is deleted, then are all the tables contained in that schema also deleted automatically or are they deleted when the Database is deleted?


Difference between database and schema

<p>What's the difference between a Database and a Schema in SQL Server? Both are the containers of tables and data.</p>
<p>If a Schema is deleted, then are all the tables contained in that schema also deleted automatically or are they deleted when the Database is deleted?</p>


is is `good` or `doesn&#39;t matter` or `bad` to INCLUDE the primary key in a covering index??

    CREATE NONCLUSTERED INDEX index_name_here ON dbo.table_name_here
    (column_to_index_here) 
    INCLUDE (primary_key_column,other_column_here)
    WITH( STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, --&lt;default junk from SSMS
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

I&#39;m thinking it doesn&#39;t matter, since the PK will be in the index anyways.

**EDIT** - to clarify.  
my `primary_key_column` is clustered, but you can explain/give info when it isn&#39;t too.

I will be joining to `dbo.table_name_here` on the `column_to_index_here` column and will be then joining to other tables on `primary_key_column` and `other_column_here`.


good/doesn&#39;t matter/bad to include Primary Key in covering index?

The `SQL` query that I have used is :

    ALTER TABLE oldtable RENAME TO newtable;

But, it gives me an error.

&gt; Server: Msg 156, Level 15, State 1, Line 1  
&gt; Incorrect syntax near the keyword &#39;TO&#39;.


How to rename a table in SQL Server?

Very simplified, I have two tables Source and Target.

    declare @Source table (SourceID int identity(1,2), SourceName varchar(50))
    declare @Target table (TargetID int identity(2,2), TargetName varchar(50))
    
    insert into @Source values (&#39;Row 1&#39;), (&#39;Row 2&#39;)

I would like to move all rows from `@Source` to `@Target` and know the `TargetID` for each `SourceID` because there are also the tables `SourceChild` and `TargetChild` that needs to be copied as well and I need to add the new `TargetID` into `TargetChild.TargetID` FK column. 

There are a couple of solutions to this.

 1. Use a while loop or cursors to insert one row (RBAR) to Target at a time and use `scope_identity()` to fill the FK of `TargetChild`. 
 2. Add a temp column to `@Target` and insert `SourceID`. You can then join that column to fetch the `TargetID` for the FK in `TargetChild`.
 3. `SET IDENTITY_INSERT OFF` for `@Target` and handle assigning new values yourself. You get a range that you then use in `TargetChild.TargetID`.

I&#39;m not all that fond of any of them. The one I used so far is cursors.

What I would really like to do is to use the `output` clause of the insert statement.

    insert into @Target(TargetName)
    output inserted.TargetID, S.SourceID
    select SourceName
    from @Source as S

But it is not possible

    The multi-part identifier &quot;S.SourceID&quot; could not be bound.

But it is possible with a merge.

    merge @Target as T
    using @Source as S
    on 0=1
    when not matched then
      insert (TargetName) values (SourceName)
    output inserted.TargetID, S.SourceID;

Result

    TargetID    SourceID
    ----------- -----------
    2           1
    4           3

I want to know if you have used this? If you have any thoughts about the solution or see any problems with it? It works fine in simple scenarios but perhaps something ugly could happen when the query plan get really complicated due to a complicated source query. Worst scenario would be that the TargetID/SourceID pairs actually isn&#39;t a match. 

MSDN has this to say about the `from_table_name` of the [output][1] clause.

&gt; Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

For some reason they don&#39;t say &quot;rows to insert, update or delete&quot; only &quot;rows to update or delete&quot;.

Any thoughts are welcome and totally different solutions to the original problem is much appreciated. 

  [1]: http://msdn.microsoft.com/en-us/library/ms177564.aspx

Using merge..output to get mapping between source.id and target.id

I created a simple trigger on Albums table. But that trigger is not visible in object explorer in programmability in triggers folder. I refreshed that folder as well as restarted the SSMS but still no luck. However i can see that trigger under that table in triggers folder. Is this a feature or a bug?

I am using SQL Server 2008.


Can&#39;t see the triggers that I created in SQL Server Management Studio 2008

I&#39;m assigned to migrate a database to a mid-class ERP.
The new system uses composite primary keys here and there, and from a pragmatic point of view, why?

Compared to autogenerated IDs, I can only see negative aspects;

* Foreign keys becomes blurry
* Harder migration or db-redesigns
* Inflexible as business change. (My car has no reg.plate..)
* Same integrity better achieved with constraints.

It&#39;s falling back to the design concept of candiate keys, which I neither see the point of.

Is it a habit/artifact from the floppy-days (minimizing space/indexes), or am I missing something?

//edit//
Just found good SO-post: https://stackoverflow.com/questions/159087/composite-primary-keys-versus-unique-object-id-field
//


Why are composite primary keys still around?

I&#39;m using PostgreSQL and I&#39;m trying to list all the tables that have a particular column from a table as a foreign-key/reference. Can this be done? I&#39;m sure this information is stored somewhere in `information_schema` but I have no idea how to start querying it.

PostgreSQL: SQL script to get a list of all tables that have a particular column as foreign key

Can anyone tell me what is the difference between a primary key and index key. And when to use which?

What is the difference between a primary key and a index key

In a three layer architecture with the database on the fourth, what does `scaling horizontally` and `scaling vertically` mean?

What does scale horizontally and scale vertically mean?

 - What is it a BLOB?
 - How can I use it?
 - What are the differences between DBMS&#39;s BLOBs. I would like to save data using BLOBs into any DBMS and then read that BLOB with a library.



What is it exactly a BLOB in a DBMS context

I am looking for sample code in which cursor adapter is used with sqlite?

Cursor adapter and sqlite example

What&#39;s the difference between `FOR` and `AFTER` triggers?

Difference between FOR and AFTER triggers?

In both queries 1 and 2, the text from the textbox is inserted into the database. What&#39;s the significance of the parameterized query here?

1. Passing `txtTagNumber` as a query parameter

        SqlCommand cmd = new SqlCommand(&quot;INSERT INTO dbo.Cars &quot; +&quot;VALUES(@TagNbr);&quot; , conn);
        cmd.Parameters.Add(&quot;@TagNbr&quot;, SqlDbType.Int);
        cmd.Parameters[&quot;@TagNbr&quot;].Value = txtTagNumber.Text;

3. Converting `txtTagNumber` to an integer before constructing the query

        int tagnumber = txtTagNumber.Text.ToInt16(); /* EDITED */
        INSERT into Cars values(tagnumber.Text); /* then is it the same? */

Also, here I would use Regular Expression validation to stop insertion of illegal characters.




How do parameterized queries help against SQL injection?

We are using ASP.NET (Framework 2) and setting database connection strings (SQL2005) in web.config.

We are currently using &quot;`providerName=SqlServer`&quot;.

All our data accesses are done using `System.Data.SqlClient` - should we therefore change to `providerName=System.Data.SqlClient`? I find many examples of this providerName on the web, but very little explaining what providerName=SqlServer actually means.

Is there a difference? I&#39;m worried that the providerName we currently specify is actually referencing a legacy (and maybe slower) client, or is there an even more efficient client than SqlClient for use with ASP.NET?

SQL providerName in web.config

I have the following:

    CREATE NONCLUSTERED INDEX [MyTableIndex]
    ON [dbo].[tablename] ([tablename_ID],[tablename_Field1])
    INCLUDE ([Tablename_Field2],[Tablename_Field3])

I want to create an if statement to check if this exists. How do I do this?


How do I check if a nonclustered index exists in SQL Server 2005

I am trying to order by date. I want the most recent dates coming in first. That&#39;s easy enough, but there are many records that are null and those come before any records that have a date.

I have tried a few things with no success:

    ORDER BY ISNULL(Next_Contact_Date, 0)
    
    ORDER BY ISNULL(Next_Contact_Date, 999999999)
    
    ORDER BY coalesce(Next_Contact_Date, 99/99/9999)

How can I order by date and have the nulls come in last? The data type is `smalldatetime`.

SQL Server ORDER BY date and nulls last

How do I instruct mysqldump to backup with column names in insert statements?  
In my case I didn’t a normal back up with insert sql’s resulting in  

    LOCK TABLES `users` WRITE;
    /*!40000 ALTER TABLE `users` 
    INSERT INTO `users` VALUES (1

structure.  

Now I went ahead and **removed a column** from the schema in users. After this when I run the backup sql’s I get a **column number mismatch error**.  

To fix this how do I go about instructing mysqldump to write column names too? 
Here is how I do it now

    mysqldump --host=${dbserver} --user=${dbusername} --password=${dbpassword} \
              --no-create-db --no-create-info --extended-insert --single-transaction \
              --compress tablename  

On a broader level, what’s the best practice to manage these schema changes? 




Mysqldump: create column names for inserts when backing up

Is there a fast way of getting all column names from all tables in `MySQL`, without having to list all the tables?

How to get all columns&#39; names for all the tables in MySQL?

I have a PostgreSQL database for my Rails application. In the schema named &#39;public&#39; the main Rails models tables are stored etc. I have created a &#39;discogs&#39; schema which will have tables with names that are sometimes the same as in the &#39;public&#39; schema - which is one of the reasons that I&#39;m using schemas to organize this.

How would I setup models from the &#39;discogs&#39; schema in my app? I will be using Sunspot to let Solr index these models as well. I&#39;m unsure of how you would do this.

Using multiple PostgreSQL schemas with Rails models

Is there a difference between a schema and a database in MySQL?  In SQL Server, a database is a higher level container in relation to a schema.

I read that `Create Schema` and `Create Database` do essentially the same thing in MySQL, which leads me to believe that schemas and databases are different words for the same objects.

Difference Between Schema / Database in MySQL

Is there any way to copy database structure without data in MySQL, so the new database will be the same as it is copied from, but with empty tables.

After getting some suggestions I tried the command, but I am getting syntax error, my `username = root` and `password = nothing`. I guess the default one. I am trying following command, 

    mysqldump -u root -p -d xyz_db | mysql -u root -p -Dnew_db

what I am missing or misplacing in command?

Content Type	Original Author	Original Content on Stackoverflow
Question	sqlchild	View Question on Stackoverflow
Solution 1 - Sql Server	RichardTheKiwi	View Answer on Stackoverflow
Solution 2 - Sql Server	ceth	View Answer on Stackoverflow
Solution 3 - Sql Server	Saghir A. Khatri	View Answer on Stackoverflow
Solution 4 - Sql Server	Robert	View Answer on Stackoverflow
Solution 5 - Sql Server	Hardik Mishra	View Answer on Stackoverflow

Difference between database and schema

Sql Server Problem Overview

Sql Server Solutions

Solution 1 - Sql Server

EDIT for additional question

Solution 2 - Sql Server

Solution 3 - Sql Server

Solution 4 - Sql Server

Solution 5 - Sql Server

How can I get the executing assembly version?

Getting exact error type in from DbValidationException

Attributions