Hot answers tagged

41

I have seen database interfaces (e.g. framework libraries) that return 'null' as a string for null columns. I believe there was a flag that would turn this on or off for debugging. This flag allows developers to easily determine if the empty field was a result of a null value or an empty value. This is a bad setting, especially in production, and would ...


27

SQL Server 2008 - Filtered unique index CREATE UNIQUE INDEX IX_Foo_chk ON dbo.Foo(chk) WHERE chk = 'Y'


25

There's a good chance that a good chunk of your confusion stems from the journalist's. The article talks about problems using entire application systems, not just databases. Completely reasonable since this is a piece of writing aimed at mass consumption, but technical details are glossed over or misunderstood by the author. Likely a number of these issues ...


18

The article itself includes a link to a Stack Overflow question that demonstrates the problem; it was in a Flex application where the code: currentChild.appendChild("Fred"); would append an element containing the word Fred to an XML document but the code: currentChild.appendChild("null"); would append an empty element, not an element containing the ...


17

Should I start indexing right from the start or when performance problem arises? Indexing strategy tends to evolve as usage patterns emerge. That said, there are also strategies and design guidelines that can be applied up front. Choose a good clustering key. You can usually determine the appropriate clustered index at design time, based on the ...


15

The reason why this works in PostgreSQL is that the system catalogs are regular tables. So creating a new function, for example, just requires inserting a row into the pg_proc table, changing the default value of a column just requires making an update to some row in pg_attrdef, and so on. Since tables are transactional anyway, you'd almost have to go out ...


14

SQL Server 2000, 2005: You can take advantage of the fact that only one null is allowed in a unique index: create table t( id int identity, chk1 char(1) not null default 'N' check(chk1 in('Y', 'N')), chk2 as case chk1 when 'Y' then null else id end ); create unique index u_chk on t(chk2); for 2000, you may need SET ...


13

Oracle: Since Oracle doesn't index entries where all indexed columns are null, you can use a function-based unique index: create table foo(bar integer, chk char(1) not null check (chk in('Y', 'N'))); create unique index idx on foo(case when chk='Y' then 'Y' end); This index will only ever index a single row at most. Knowing this index fact, you can ...


12

One thing I don't think I've seen mentioned: We aren't just talking about SQL. The name might start/end in the database... but to get there normally involves MULTIPLE channels. Database, Sql, php, html, javascript... Java, C#, VB, Perl, Phython, Ruby, bash, batch, etc, etc, etc... Each of these steps in the pipeline might involve converting data from one ...


11

I think this is a case of structuring your database tables correctly. To make it more concrete, if you have a person with multiple addresses and you want one to be the default, I think you should store the addressID of the default address in the person table, not have a default column in the address table: Person ------- PersonID Name etc. DefaultAddressID ...


11

MySQL: create table foo(bar serial, chk boolean unique); insert into foo(chk) values(null); insert into foo(chk) values(null); insert into foo(chk) values(false); insert into foo(chk) values(true); select * from foo; +-----+------+ | bar | chk | +-----+------+ | 1 | NULL | | 2 | NULL | | 3 | 0 | | 4 | 1 | +-----+------+ insert into foo(chk) ...


10

I have been working with Pentaho for about a year now. Pentaho is a full Open Source suite for Business Intelligence. It's strenght is that it relies on independently managed project : Pentaho Data Integration (Kettle) ->ETL Pentaho Report Designer (PRD) -> Report designer Mondrian -> R-OLAP cube and much more.. You can use them as a whole (Pentaho ...


9

PostgreSQL: create table foo(bar serial, chk char(1) unique check(chk='Y')); insert into foo default values; insert into foo default values; insert into foo(chk) values('Y'); select * from foo; bar | chk -----+----- 1 | 2 | 3 | Y insert into foo(chk) values('Y'); ERROR: duplicate key value violates unique constraint "foo_chk_key" --edit or ...


9

SQL Server: How to do it: The best way is a filtered index. Uses DRI SQL Server 2008+ Computed column with uniqueness. Uses DRI See Jack Douglas' answer. SQL Server 2005 and before An indexed/materialised view which is like a filtered index. Uses DRI All versions. Trigger. Uses Code, not DRI. All versions How not to do it: Check constraint with a ...


9

There are 2 bits of information. This means 2 fields. It is that simple. In practical terms, selecting 2 columns is zero extra complexity. Having to read a large field to parse one bit out is unnecessary complexity and performance overhead. A couple of SO questions on storing salts with hashes http://stackoverflow.com/q/1219899/27535 ...


9

If I want to move record 0 to the start, I have to reorder every record No, there's a simpler way. update your_table set order = -1 where id = 0; If I want to insert a new record in the middle, I have to reorder every record after it That's true, unless you use a data type that supports "between" values. Float and numeric types allow you to ...


8

There's really risks associated with both approaches: Option a) Index from the start, but not realize you have created a number of indexes which are never used. These add some overhead (most noticeably to queries that modify data, but also with optimization of SELECT statements trying to identify the best index). You will need to discipline yourself to ...


8

Most don't? Bummer. I principally use SQL Server and it does. I know Oracle doesn't but I thought Oracle might be an aberration. In SQL Server, I'm quite certain you can run multiple DDL statements in a single transaction although I also think there's a couple of restrictions (which I have all forgotten). You can do a create or an alter or a drop of ...


8

Well, I think you have some mixed concepts: An index improves performance of READ OPERATIONS ( those of SELECT ) while increase the processing time of INSERT/UPDATE OPERATIONS ( So they don't improve all CRUD operations, as you've heard ). As each time you insert a new row, you should update the index, if you have too much indexes you are increasing the ...


8

This answer shows an implementation using a SQL Server (2005+) CLR function. -- Enable CLR (if necessary) EXECUTE sys.sp_configure @configname = 'clr enabled', @configvalue = 1; RECONFIGURE; Assembly and function CREATE ASSEMBLY DBA AUTHORIZATION dbo FROM ...


8

The following solution is for SQL Server. The approach is similar to Serg's in that the query uses only the DATEADD and DATEDIFF functions. It does not, however, account for negative intervals (FromDate > ToDate), and it derives years and months from the total month difference: WITH MonthDiff AS ( SELECT t.FromDate, t.ToDate, ...


7

Community Wiki answer for the various links originally left as comments on this popular question How does a surname of Null cause problems in many databases? (Programmers SE) Falsehoods Programmers Believe About Names by Patrick McKenzie Licensed to Bill by David Mikkelson (snopes.com) An example of coding that will cause this issue in the application ...


6

I think you're not understanding what is meant by datawarehouse. It's not a tool. Or an application. Or a database. It doesn't mean "big database". You said, we worked with MS Business Intelligence and MSSQL as Data Warehouse storage. MSSQL wasn't just the storage for the DWH, it is the DWH. A datawarehouse is a database which is specifically ...


6

Unless the attributes between the different types are totally different (which I doubt), go for the second option and store the address type in a column of the address table.


6

My version, implemented in SQL Server 2008R2 SP2. CREATE FUNCTION dbo.ReadableInterval( @FromDate AS date, @ToDate AS date ) RETURNS TABLE AS RETURN ( with YearStep as ( select max(n1.Number) as YearNumber from dbo.Numbers as n1 where n1.Number <= DATEDIFF(YEAR, @FromDate, @ToDate) -- see comment (A) and DATEADD(YEAR, ...


5

The answer to your question is "no." And no, I don't think it will ever be possible at least in any helpful way. Relational databases are designed to operate over sets of tuples. Non-relational databases are not. This means that one of the key tradeoffs is being able to have very fast access (with "good enough" consistency controls) to single pieces of ...


5

In general, the wider the table the slower the query. But slower doesn't necessarily mean slow. The number of joins probably has more to do with the use of id numbers than anything else. Natural keys can speed performance dramatically when the important information is carried in the key. (This happens more often than you might think.) The M:N table is the ...


5

I'm inclined to agree with @Catcall, database recovery should be top of the list. The implications of both backup and recovery options are typically the most poorly understood outside of a DBA team and the most likely to result in disaster. Ensure you have defined and agreed (by technical and non-technical management) RPO (Recovery Point Objective) and RTO ...


5

The two things I deal with every day. Disaster recovery. Performance tuning. (Both for individual queries, and for the dbms itself.) Your disaster recovery plan needs to be scripted, tested, and practiced. I'm using scripted in the sense of something an actor would follow, not something written in Python. It should tell everyone who needs to be ...


5

Possible approaches using widely implemented technologies: 1) Revoke 'writer' privileges on the table. Create CRUD procedures that ensure the constraint is enforced at transaction boundaries. 2) 6NF: drop the CHAR(1) column. Add a referencing table constrained to ensure its cardinality cannot exceed one: alter table foo ADD UNIQUE (bar); create table ...



Only top voted, non community-wiki answers of a minimum length are eligible