When I started writing database queries I didn’t know the JOIN keyword yet and naturally I just extended what I already knew and wrote queries like this: Now that I know that this is the same as an INNER JOIN I find all these queries in my code and ask myself if I should rewrite them. Is there something…
Tag: sql
Solutions for INSERT OR UPDATE on SQL Server
Assume a table structure of MyTable(KEY, datafield1, datafield2…). Often I want to either update an existing record, or insert a new record if it doesn’t exist. Essentially: IF (key exists) run …
A script to change all tables and fields to the utf-8-bin collation in MYSQL
Is there a SQL or PHP script that I can run that will change the default collation in all tables and fields in a database? I can write one myself, but I think that this should be something that readily available at a site like this. If I can come up with one myself before somebody posts one, I will
How do I enforce data integrity rules in my database?
I’m designing this collection of classes and abstract (MustInherit) classes… This is the database table where I’m going to store all this… As far as the Microsoft SQL Server database knows, those are all nullable (“Allow Nulls”) columns. But really, that depends on the class stored the…
Keep pagination repeatable if change operations are performed
If one wants to paginate results from a data source that supports pagination we have to go to a process of: defining the page size – that is the number of results to show per page; fetch each page requested by the user using an offset = page number (0 based) * page size show the results of the fetched
Difference between BYTE and CHAR in column datatypes
In Oracle, what is the difference between : and Answer Let us assume the database character set is UTF-8, which is the recommended setting in recent versions of Oracle. In this case, some characters take more than 1 byte to store in the database. If you define the field as VARCHAR2(11 BYTE), Oracle can use up…
How do I perform an IF…THEN in an SQL SELECT?
How do I perform an IF…THEN in an SQL SELECT statement? For example: Answer The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server. You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works: CASE
How can I prevent SQL injection in PHP?
If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST[‘user_input’]; …
What are the use cases for selecting CHAR over VARCHAR in SQL?
I realize that CHAR is recommended if all my values are fixed-width. But, so what? Why not just pick VARCHAR for all text fields just to be safe. Answer The general rule is to pick CHAR if all rows will have close to the same length. Pick VARCHAR (or NVARCHAR) when the length varies significantly. CHAR may al…
How can you handle an IN sub-query with LINQ to SQL?
I’m a bit stuck on this. Basically I want to do something like the following SQL query in LINQ to SQL: Any help would be gratefully received. Answer Have a look at this article. Basically, if you want to get the equivalent of IN, you need to construct an inner query first, and then use the Contains() me…