rowid en SQL Server

Esta es la forma de obtener el número de fila en un select en SQL Server:

select ROW_NUMBER() OVER (ORDER BY codProductoSICEX) AS ID,
          codProductoInsc,
          codProductoSICEX
 from geneproducto
where codProductoSICEX >= '020000001201'
 order by 3

Fuente: http://stackoverflow.com/questions/11762700/how-do-i-get-the-row-id-of-the-table-in-sql-server

decode versus case

una función que retorna un resultado de acuerdo a los valores de un campo para usar dentro de un select

En ORACLE:

select DECODE(u.id 
             ,2, 'GOOD'
             ,3, 'POOR'
             ,4, 'EXCELLENT'
               , 'BAD')
  from university

En SQL Server:

select CASE u.id 
       WHEN 2 THEN 'GOOD' 
       WHEN 3 THEN 'POOR' 
       WHEN 4 THEN 'EXCELLENT' 
       ELSE 'BAD' 
   END CASE 
  from university u;

manejo de identitys

En SQL Server:

para conocer el último identity usado de una tabla (entre comillas el nombre de la tabla):

select IDENT_CURRENT('saniSituacionPac')

para resetear un identity de una tabla:

DBCC CHECKIDENT(saniSituacionPac, RESEED, 0)

Cambios en columnas

Para alter en columnas seguir la sgte. estructura de script de acuerdo al motor de base de datos:

SQL Server / MS Access:

ALTER TABLE table_name
ALTER COLUMN column_name datatype

My SQL / Oracle:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Fuente: http://www.w3schools.com/sql/sql_alter.asp

Alter Table

SQL Server / MS Access:

ALTER TABLE table_name
ALTER COLUMN column_name datatype

My SQL / Oracle:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Indices en SQL Server

El objetivo de un índice es acceder más rápido a los datos. Aparte de los índices que se crean para las llaves primarias se pueden crear otros índices de manera de optimizar consultas sobre campos específicos de las tablas, por ejemplo cuando en un WHERE se considere un campo diferente a la llave primaria.

La sintaxis para crear un índice es la sgte:

create <TIPODEINDICE> index <NOMBREINDICE>

on <TABLA>(<CAMPO(S)>);

El "TIPODEINDICE" indica si es agrupado (clustered) o no agrupado (nonclustered). Si no se especifica se crea uno "No agrupado"

SQL Server crea automáticamente índices cuando se establece una restricción "primary key" o "unique" en una tabla. Al crear una "primary key", si no se especifica, el índice queda como agrupado (clustered) a menos que ya exista un índice agrupado para dicha tabla. Al crear una llave "unique", si no se especifica, el índice será no agrupado (non-clustered).

Los índices clustered son útiles para las columnas que son muy consultadas por algún rango, o accesadas en orden ascendente o descendente.

Consideraciones para usar índices agrupados

• Columnas selectivas
• Columnas afectadas en consultas de rangos: BETWEEN, mayor que, menor que, etc.
• Columnas accedidas "secuencialmente"
• Columnas implicadas en JOIN, GROUP BY

Los índices nonclustered son útiles cuando se requiere accesar la información de múltiples maneras.

Consideraciones para usar índices no agrupados

• Columnas con datos muy selectivos
• Consultas que no devuelven muchas filas.
• Columnas en WHERE.
• Evitar acceso a páginas de datos realizando el acceso sólo por el índice.

Luego de crear los índices es necesario revisar que el desempeño de la consulta en cuestión haya mejorado y además ver que otras consultas que se hagan a la tabla no hayan sido afectado negativamente.

Se pueden crear todos los índices que se quieran sobre una tabla, pero se debe tener especial cuidado que eso no haga más lentas las consultas.

Algunas ayudas:

Para obtener la lista de indices de la base de datos:

select name from sysindexes;

 (Al crear los propios índices se puede poner algun prefijo de manera de identificarlos mejor, Ej.: IDX_<NOMBREINDICE>)

Para ver los indices de una tabla:

sp_helpindex <TABLA>;

Fuentes:
http://msdn.microsoft.com/es-es/library/ms345331(v=sql.90).aspx
http://bdatos.wordpress.com/base-de-conocimiento/microsoft-sql-server-perfomance/mssql-una-introduccion-a-los-indices-agrupados-y-no-agrupados-de-estructura-de-datos/
http://www.sqlserverya.com.ar/temarios/descripcion.php?cod=68&punto=62
http://www.zonacodigos.com/index.php/sql/59-creacion-de-indices
http://msdn.microsoft.com/es-es/library/ms130862.aspx

Caracteres especiales

Solo use lo sgte.:

SELECT columns FROM table WHERE column LIKE '%[%]%'

-- or

SELECT columns FROM table WHERE
column LIKE '%\%%' ESCAPE '\'

Fuente: http://sqlserver2000.databases.aspfaq.com/how-do-i-search-for-special-characters-e-g-in-sql-server.html

Respaldo de tablas

Para respaldar una tabla en otra:

En Oracle:

create table respaldo_pd_pagina7000

as select * from pd_pagina

En Sql Server:

select *

into respaldo_pd_pagina7000

from pd_pagina

Largo de campo TEXT

En SQL Server para medir el largo de un campo VARCHAR se usa la función LEN

select max(LEN(cont_epigrafe))

from contenidos

para medir el largo de un campo TEXT se usa la funcion DATALENGTH

select max(DATALENGTH(cont_bajada))

from contenidos

Fuente: http://blog.sqlauthority.com/2007/06/20/sql-server-find-length-of-text-field/

Respaldo Base de Datos

Nunca está demás recordar un script para respaldar una base de datos SQL Server con una simple instrucción:

backup database <nombre BD>
to disk='<ruta en el servidor>\<nombre archivo>.bak'

SQL Server: Limitando el numero de registros

En una consulta Oracle, cuando se quiere reducir el número de registros en una consulta se usa la sentencia:

rowcount <= 10

esto reduce el resultado a un número máximo de 10 registros.

En MySQL la solución similar sería con un

limit 10

en Sql Server encontré 2 soluciones:

1) Al principio del select

select top 10 ...

2) en un seteo previo a la consulta

set rowcount 10

para volver el seteo a la normalidad

set rowcount 0

La fuente acá: http://channel9.msdn.com/forums/Coffeehouse/102407-SQL-Server-and-LIMIT-clauses-on-SELECT-statements/

SQL Server: Tipos de datos enteros

Para identificar un campo entero de una tabla, en MS SQL Server existen estos 4 tipos de datos:

Tipo de datos	Intervalo	Almacenamiento
bigint	De -2^63 (-9.223.372.036.854.775.808) a 2^63-1 (9.223.372.036.854.775.807)	8 bytes
int	De -2^31 (-2.147.483.648) a 2^31-1 (2.147.483.647)	4 bytes
smallint	De -2^15 (-32.768) a 2^15-1 (32.767)	2 bytes
tinyint	De 0 a 255	1 byte

Fuente: http://technet.microsoft.com/es-es/library/ms187745.aspx

SQL Server: ntext

Tipos de Datos

ntext

Datos Unicode de longitud variable con una longitud máxima de 2^30 - 1 (1.073.741.823) caracteres. El tamaño del almacenamiento, en bytes, es dos veces el número de caracteres especificado. El sinónimo de ISO para ntext es national text.

text

Datos no Unicode de longitud variable de la página de códigos del servidor y con una longitud máxima de 2^31-1 (2.147.483.647) caracteres. Cuando la página de códigos del servidor utiliza caracteres de doble byte, el almacenamiento sigue siendo de 2.147.483.647 bytes. Dependiendo de la cadena de caracteres, el espacio de almacenamiento puede ser inferior a 2.147.483.647 bytes.

Los tipos de datos ntext, text e image se quitarán en una versión futura de SQL Server.

Microsoft sugiere evitar su uso en nuevos trabajos de desarrollo.

http://msdn.microsoft.com/es-es/library/ms187993.aspx

Me encontré con unos de estos datos en una migración y no podía hacer comparaciones del tipo <> por no ser compatible con el tipo de dato ntext (en este caso el campo nm_observacion):

        select rtrim(ltrim(nm_observacion)) 
              ,fc_recepcion
          from db_SNP_RNASQL.dbo.cc_recepcion
         where rtrim(ltrim(nm_observacion)) is not null
           and rtrim(ltrim(nm_observacion)) <> ''

Tampoco podía calcular el largo de los datos para ver su tamaño y cambiar a un varchar con tranquilidad

No servía el len():

       select max(len(nm_observacion))
         from db_SNP_RNASQL.dbo.cc_recepcion  

por lo que fue necesario ocupar la función DATALENGTH():

       select max(DATALENGTH(nm_observacion))
         from db_SNP_RNASQL.dbo.cc_recepcion  

Fuente: http://www.mssqltips.com/tip.asp?tip=1188

El DECODE en SQL Server

En Oracle existe la funcion DECODE que permite evaluar un valor para seleccionar otro.

 select decode(v_mes, 1, 'Enero', 2, 'Febrero', 'Otro')
   into v_valor
   from dual


En SQL Server, el simil se hace con un CASE, asi:


  select @v_valor = case @v_mes
    when 1 then 'Enero'
    when 2 then 'Febrero'
    else 'Otro'
  end;

Cambio de nombres de tablas y columnas en SQL Server

Para hacer un cambio de nombre de tabla en SQL Server hay que hacerlo con el sgte. script:

sp_rename 'nombreAntiguoDeLaTabla', 'nombreNuevoDeLaTabla'


Para hacer un cambio de nombre de campo en SQL Server hay que hacerlo con el sgte. script:

sp_rename 'nombreDeLaTabla.nombreAntiguoDelCampo', 'nombreNuevoDelCampo','COLUMN'

Problemas de idiomas en SQL Server

Haciendo una migración en bases de datos MS SQL Server me encontré con que una de las tablas tenía la definición de un campo en un idioma diferente por lo que al hacer la sgte. consulta:

select c1.idciudad, c2.cd_comuna
  from db_SNP_RNASQL.dbo.__comuna c1
      ,db_SNP_RNASQL.dbo.tb_comuna c2
 where c1.comuna = c2.nm_comuna
 order by 2

me arrojaba el sgte. error:

No se puede resolver el conflicto de intercalación entre "Modern_Spanish_CI_AS" y "Traditional_Spanish_CI_AS" de la operación equal to.

La solución fue aplicar una especie de traductor de estas columnas quedando la consulta así:

select c1.idciudad, c2.cd_comuna
  from db_SNP_RNASQL.dbo.__comuna c1
      ,db_SNP_RNASQL.dbo.tb_comuna c2
 where c1.comuna COLLATE Modern_Spanish_CI_AS =
     c2.nm_comuna COLLATE Modern_Spanish_CI_AS
order by 2

La fuente de este conocimiento en capitalfederal.com

Identity

En una migración de MS SQL Server me encontré con algunas tablas que tienen PK con un identity por lo que es necesario bloquear esta propiedad antes de hacer un insert masivo con PK definidos con anterioridad. Para ello es necesario ejecutar el script con las siguientes instrucciones:

        --saca identity de la tabla
        set identity_insert sifaComuna on

        insert into sifaComuna
              (idComuna
              ,idRegion
              ,nombreComuna)
        select cd_comuna
              ,cd_region
              ,nm_comuna
          from db_SNP_RNASQL.dbo.tb_comuna

        --vuelve a poner identity en la tabla
        set identity_insert sifaComuna off

Posterior a este insert masivo, el campo con identity quedará seteado para ser insertado con el valor que viene a continuación de acuerdo con el mayor valor ingresado y el incremento definido para el identity.

perdí la URL desde donde saqué esto.