selecting varchar: Is null or is empty?

When using TSql to check if a varchar is null or is empty we can remember:
- A nullable varchar is
1. not null when store a string or an empty string;
2. null only when not initialized, even with an empty string.

update fied1 = 'A string', field2 = '', field3 = null

We have:
Select * from table where field1 is null (0 row)
Select * from table where field2 is null (0 row)
Select * from table where field3 is null (1 row)
Select * from table where field1 = '' (0 row)
Select * from table where field2 = '' (1 row*)
Select * from table where field3 = '' (0 row)
Select * from table where (field2 = '' and field2 is null) (0 row) 
Select * from table where (field2 = '' and field2 is NOT null) (1 row**)

Well, the objective is to pay attention to our predicates of where clause, because sometimes some results are not expected when selecting empty strings

* this can be true for any amount of spaces
** pay special attention to this predicates working together

/*------------------------

Select * from select_varchar where field1 is null 
Select * from select_varchar where field2 is null 
Select * from select_varchar where field3 is null 
Select * from select_varchar where field1 = '' 
Select * from select_varchar where field2 = '' 
Select * from select_varchar where field3 = '' 
Select * from select_varchar where (field2 = '' and field2 is null) 
Select * from select_varchar where (field2 = '' and field2 is NOT null) 

------------------------*/
field1     field2     field3
---------- ---------- ----------

(0 row(s) affected)

field1     field2     field3
---------- ---------- ----------

(0 row(s) affected)

field1     field2     field3
---------- ---------- ----------
A String              NULL

(1 row(s) affected)

field1     field2     field3
---------- ---------- ----------

(0 row(s) affected)

field1     field2     field3
---------- ---------- ----------
A String              NULL

(1 row(s) affected)

field1     field2     field3
---------- ---------- ----------

(0 row(s) affected)

field1     field2     field3
---------- ---------- ----------

(0 row(s) affected)

field1     field2     field3
---------- ---------- ----------
A String              NULL

(1 row(s) affected)

TSQL RowNum from Oracle equivalent, and the PhysLoc column

I was looking for the Row_Number() sintax on Internet:

SELECT *, ROW_NUMBER() OVER (ORDER BY [column name]) as rowNum

FROM [table name]

order by ROW_NUMBER() OVER (ORDER BY [column name])

And I have found this, as a gift:

SELECT  id, %%PhysLoc%% from dbo.A ORDER BY %%PhysLoc%%;

                                             

In Oracle we used to use something like that to improve performance in updates, specially in non-normalized tables where PK was not set yet, and sometimes better than a PK index. 

Tips for toggle, or flip, bit columns

Some tips I record here just to get them in touch.

Looking for a toggle function in T-SQL, I've found these tips in StackOverflow:

update table_name set status = status ^ 1  where code = 123 [Using XOR]
update table_name set status = 1 - status  where code = 123 [Using minus logically]
In my case, I had a nullable field, with many nulls inside, so I had to adapt:
update table_name set status = isnull(status,0) ^ 1  where code = 123
update table_name set status = 1 - isnull(status,0)  where code = 123

I have never needed to do this directly to the database, but only in high level tiers.

Usually I would do it in a more readable way, like in Mayo contribution:

UPDATE tblTest SET MyBitField = CASE WHEN MyBitField = 1 THEN 0 ELSE 1 END

and...

UPDATE tblTest SET 
   MyBitField = CASE 
      WHEN MyBitField = 1 THEN 0 
      WHEN MyBitField = 0 THEN 1
      ELSE NULL -- or 1 or 0 depending on requirements
   END

Thanks to gbn, Mayo and Austin Salonen. You did great.

Source: http://stackoverflow.com/questions/1397332/how-to-flip-bit-fields-in-t-sql

About triggers, after and instead of

The following picture shows an overview of data flow when using triggers in SQL Server. More details and more arrows flows were not shown to be a simple diagram. Some anottations about:

• Truncate table statement does not fire triggers
• WriteText does not fire triggers, with or without logging
• Old versions of SQL need set the DISALLOW RESULTS FROM TRIGGERS to 1, due to avoiding returning results when running a trigger
• Use sp_settriggerorder to order the first and last AFTER trigger when using many of them in the same object. Other triggers between the last and first will run randomly
• SERVER scoped DDL triggers are in \Server Objects\Triggers folder
• DATABASE scoped DDL triggers are in \Programmability\Database Triggers folder
• LOGON trigger run only when authentication is successfull
• Inside an Instead Of trigger is not necessary to rollback a command, because its underlying commando (the original one) is ignored. If needed execute the original statement inside the trigger. The statement won't call the instead off trigger again (recursively), but the After triggers will run, whether exists.
• Code triggers carefully because they can call each other recursively when changing other tables than the underlying table (where the trigger is attached).

Customize email alias for from field in SSRS

When you want to change the from information sent by SSRS in your subscriptions just set up an account with an alias before the email account in the tag of the RSReportServer.config file. Follow me:

1. Open the file in \Microsoft SQL Server\MSRS.MSSQLServer\Reporting Services\ReportServer\rsreportserver.config
2. Locate the tag inside the tag, subsections (or just search for using a search tool)
3. Change the original email configuration from email@domain.com to "Alias you want" email@domain.com
4. Save your changes and submit the report. Depending on your email toos you will see the alias in the from field with or without the email following it.

I hope to have contributed.

Cheers.

Usando o GO para executar um comando vezes

CREATE TABLE #tmp (id int primary key identity(1,1), counter int default 0)
GO

-- Inserindo dados na tabela
INSERT INTO #tmp DEFAULT VALUES
GO 10

--Vai executar o Insert 10 vezes, sem necessidade de loop

Preenchendo campo com valores sequenciais

Dica muito útil para preencher um campo inteiro com valores sequenciais, o que não é muito raro de precisar para quem vive dando consultoria para clientes.

Segue a dica original: http://www.sqlserverdicas.com/2011/11/update-com-incremento.html

E esta é a minha utilização, baseada na dica original.


DECLARE @counter int
SET @counter = (select max(codcliente)+1 from clientes);

update clientes 
set @counter = CodCliente = @counter + 1 
where codcliente = 0

Assim eu pude completar os codigos que estavam zerados com valores sequenciais, começando do último mais um, e seguindo em frente.