Fabricio Araujo
  • Member for 10 years, 5 months
  • Last seen more than a month ago
  • Rio de Janeiro, Brazil
When to use TINYINT over INT?
13 votes

All things become gain complexity when databases gets bigger: maintenance windows needs to be enlarged or rescheduled backups (the end-of-day full backup becomes an absurd time-eater, so you need an ...

View answer
Should you design the database before the application code is written?
9 votes

I have in mind the follwing rule: "you can only get from database the information which you have data to generate". So, I design first the database later the code. Why? No matter what metodology/...

View answer
Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?
Accepted answer
7 votes

Directly answering the question, there are two ways to execute the operation. If the number of varchar columns involved on the table is small (one or two), it is more practical to create pseudo-temp ...

View answer
UPDATE ... FROM ... syntax for iSeries for a MS SQL Server developer
7 votes

Try using the MERGE statement from SQL2003 Standard - which is the equivalent of UPDATE..SET..FROM syntax. Example: MERGE INTO dbo.t1 AS Target USING (SELECT a,b,c FROM dbo.t2) AS Source ON (Target.b ...

View answer
Why would you want to avoid dynamic SQL in stored procedures?
6 votes

Dynamic SQL is a tool. And as a tool, it has some applications - for administrative works it is a blessing, for example. Not so good on SP used by applications, especially if you didn't manage the ...

View answer
Can I force a user to use WITH NOLOCK?
Accepted answer
5 votes

Seems the nearest you can get without using SPs is using SET READ_COMMITTED_SNAPSHOT, which will affect all users of the database. The READ_COMMITTED_SNAPSHOT database option controls if the ...

View answer
Why does = NULL not work in Oracle?
5 votes

In SQL Server, we have an connection setting to get =NULL to behave equally to IS NULL. But in latest versions is not recommended anymore - it's even marked as deprecated. The recommended is the SQL ...

View answer
Updating a table with millions of records, its been 4 days
4 votes

First of all, change the query to: UPDATE t1 SET costPercentage = ISNULL(t2.PaymentIndex, 1.0), t2.TopUp_Amt = (ISNULL(t2.PaymentIndex, 1.0) - 1.0) * ISNULL(dbo.table1.Initial_Tariff_Amt,...

View answer
Is it more secure to go through a 3rd database to connect two databases using the same login?
4 votes

The answer is: it depends. If the private database is not accessed from outside internal LAN (or only through an VPN), this scheme adds security as someone using the credentials of the WebSite would ...

View answer
Query performance of a NULL vs an empty ('') varchar in SQL Server 2012
3 votes

Using your info about the distribution of values (and some guessing), I would say that your problem is that MSSQL is doing an table scan when receiving an WHERE clause for empty string. Why? Suposing ...

View answer
Can someone explain why select with nolock will query a potion of updated data?
3 votes

The hint NOLOCK is equivalent to the transaction isolation level READ UNCOMMITTED, just restricted to the scope of one table access method. What RU does that makes the non-committed appear on your ...

View answer
INT or CHAR for a Type Field
3 votes

Just as an complement to great gbn's answer. Maybe you could create something like that: create table dbo.VehicleType ( VehicleTypeId int not null primary key, Name varchar(50) not null, ...

View answer
Manually set log file size after shrink SQL Server 2008 R2
2 votes

In complement of Aaron's answer about simple mode, you could schedule 2 (or more) differential backups per day, that way reducing the data loss window of your database operation while retaining SIMPLE ...

View answer
Schema blocking in READ_COMMITTED_SNAPSHOT
2 votes

AFAIK, snapshot isolation works on DML only. DDL still gets it's Sch-M/S locks, which - in your present case - are caused by drop/recreate FKs. Another way of doing that is creating an price map (...

View answer
How to undo every change made to a database in a controlled environment?
1 votes

I'd divide the tests in "themed" sections and each section would get their own transaction, which is rolled back to allow the next section of tests runs on the controlled data. The tests itself would ...

View answer
Migration from SQL Server 2008 to Postgres 9.2
0 votes

TinyInt and SmallDateTime (AFAIK) are non-SQL Standard datatypes. EDIT: SQL Standard types nearest equivalents are, respectively, SMALLINT and TIMESTAMP. I suggest you do an conversion of columns ...

View answer
Set SA to weak Password in SQL Server 2012
0 votes

I agree with HLGEM. They must change the login account to another one (even if it needs sysadmin or - less problematic, dbowner - privileges on a first moment). On the other way, you can change the ...

View answer
Long JOINS Returns No result all tables have one to many relationship with a particular table
0 votes

The reason for this not to work, is that one or more that tables is either empty or have NULLs on the FK column. Since INNER JOINs require that exists at least one row that conform to the ...

View answer
Optimisation Newbie: How much of a 'sin' is redundancy?
0 votes

You could also add one (or more) trigger-filled column to pre-calculated the count of the most needed aggregated options...

View answer
Physical Address Schema
0 votes

If you have business rules based on address, you would need to create an addressing table with ZIP codes, and getting the street address (is what Google came for the portuguese word "logradouro" which ...

View answer
Single versus Multiple databases
0 votes

The most irritanting and common answer on IT: it depends. If most of data is used (shared) by all applications, a single database would simplify things. BUT if the applications (or modules) share ...

View answer