Normally if you need to drop a foreign key in SQL, you’d use the ALTER TABLE statement. But if you’re using SQLite, that’s not an option.
Category: DBMS
Database Management Systems
SQLite DROP TABLE
In SQLite, you can drop a table with the DROP TABLE statement.
You can optionally add the IF EXISTS clause to suppress any errors that might occur if the table doesn’t exist.
Also, if the table is referenced by a foreign key, there are a few things to be aware of.
Continue readingFind Out Which Partition a Given Value Would be Mapped to in SQL Server (T-SQL)
If you have a partitioned table or index in SQL Server, and you want to determine which partition a given value would be mapped to, you can do this nice and quickly with the $PARTITION system function.
All you need to know is the name of the partition function (and of course, the value you’re interested in).
Continue readingHow Implicit Transactions Work in SQL Server
There are four transaction modes in SQL Server. One of these is implicit mode.
In SQL Server, an implicit transaction is when a new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
This is not to be confused with autocommit mode, where the transaction is started and ended implicitly.
Continue readingHow to Fix “Conversion failed when converting the value to data type” in SQL Server
SQL Server error Msg 245, Level 16 tells us that there was a problem when trying to convert a value to a specific data type.
You’ll get this error if you try to insert the wrong data type into a column.
To fix this issue, make sure the data type of the value you’re trying to insert, matches the column’s type.
Continue reading3 Ways to Return the Number of Rows in Each Partition in SQL Server (T-SQL)
If you’ve previously created a partitioned table in SQL Server, and you now want to know how many rows are being stored in each partition, here are three queries you can use.
In particular, you can:
- Query the
sys.dm_db_partition_statsview - Query the
sys.partitionsview - Use the
$PARTITIONfunction in a query
Below are examples of all three.
Continue readingExecute a Trigger Only When Certain Columns are Updated (SQL Server)
SQL Server has the UPDATE() function that you can use within your DML triggers to check whether or not a specific column has been updated.
While this function only accepts one column, there’s nothing to stop you from including multiple UPDATE() clauses with AND or OR to test for multiple column updates.
Find Out if a Partition is Compressed in SQL Server (T-SQL)
In SQL Server, you can query the sys.partitions system catalog view to find out whether or not a partition has been compressed.
In particular, the data_compression column tells you whether it’s compressed or not. The data_compression_desc column tells you what type of compression it uses. If it isn’t compressed, it returns NONE.
How to Fix “The select list for the INSERT statement contains fewer items than the insert list”
SQL Server error 120 occurs when you don’t specify enough columns in your INSERT list when using a SELECT list for the values to insert.
To be more specific, it happens when you use a SELECT list in your INSERT statement, but the SELECT list doesn’t return as many columns as you’re specifying with the INSERT.
This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.
Return All Rows From a Specific Partition in SQL Server (T-SQL)
When you create a partitioned table in SQL Server, you specify which values go into each partition.
This is done when you create the partition function. When you create the partition function, you specify boundary values, which determine which values go into each partition.
Once you’ve created your partitioned table, and you’ve inserted data, you can run a normal SELECT statement to return data, just as you would with a non-partitioned table (actually, even non-partitioned tables have one partition).
But did you know that you can also specify which partition you want data from?
You can do this with the help of the $PARTITION system function in your WHERE clause.