New answers tagged

3

Using UPDATE, DELETE or INSERT to move data can take quite a lot of time and use resources (IO) on both data and log files/disks. It is possible to avoid filling the transaction log with potentially lots of records while working on a big table: Using partition switching, only the metadata is changed. This is performed really quickly (almost instantaneous). ...


4

The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column, and (2) make sure that the IDENTITY starts from the most recent value in the existing rows. First off, some test data to play with: CREATE TABLE ...


3

You cannot modify an existing column by adding the identity property to it. There are some interesting workarounds; one of which is detailed in the answer here: Why is removing the Identity property on a column not supported Community Wiki answer; originally a comment by Max Vernon. Feel free to edit this answer to improve it


2

If you want to start with a new identity value you need to reseed your identity. Have a look at the documentation for CHECKIDENT DBCC CHECKIDENT (yourtable, reseed, starting point)


2

With the 1st design, you can enforce that a Part is related (through SubItem and Type) to the same Item. With the second, you can't (through DDL alone, you need triggers as you already mentioned to enforce that or make sure all CRUD operations are done through stored procedures that take care of the restriction). Otherwise you may have a Part that is related ...


0

Tried to create a database with the exact tables, PKs and FKs as yours and I receive no error. Tried both creating the tables with the constraints and without it, creating the relationships via PHPMyAdmin. This "Error" you're getting really doesn't have a message or a error number? To answer you, yes, you can set two columns as Primary Keys that are also ...


1

You can query from sql cache for long time: SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS QueryText, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st You ...


6

SQL employs a concept known as domain name integrity which means that the names of objects have a scope given by their container. Column names have to be unique, but only within the context of the table that contains the columns. Table names have to be unique, but only within the context of the schema that contains the tables, etc. When you query columns ...


3

A simple analogy. Think of two different people in the same postal district (database schema) having the same address (table name) and name (field name). What's the poor postman going to do? You'll have to check the system catalogue (you didn't specify your RDBMS) - but I'd be very surprised if any of the major RDBMSs allowed it! Normally, there's a ...


1

Firstly, I agree with John M's comment to your question. You should do some reading about the concepts of database. You probably misinterpret between accessing your data and how your data organized in the database. When you need to find names whose last_name equals to 'Bluthe', just do it: SELECT last_name FROM names WHERE last_name = 'Bluthe' That's it. ...


0

You are running in Windows, correct? Perhaps an older version of Windows and an older version of MySQL? File operations are slow. ALTER TABLE, especially when changing the PRIMARY KEY, requires creating 1-3 files, copying over some stuff (not much in your case), removing the old 1-3 files, then renaming the files. ALTER on an empty table is not a ...



Top 50 recent answers are included