(CC) Stéphane Denis

Showing all posts tagged #t-sql:

T-SQL Recommandations - à propos des conventions et la nomenclature des objets

Posted on February 18th, 2015

Il n'y a pas, à proprement parler, de convention officielle pour ce qui est du style et de la nomenclature des tables, des champs et autres objets que vous définissez en SQL. Comme dans tout contexte de programmation, commencez par savoir s'il existe une norme explicite ou implicite déjà en place avant même d'imposer votre style et une norme en particulier. Au besoin, consultez le standard ISO sur les métadonnées ou les modèles de références de Microsoft pour les bases de données. Vous y verrez des contradictions importantes... l'homogénéité passe avant le purisme, consultez vos collègues avant d'entreprendre des ajouts!

T-SQL Recommandations - à propos des schémas et de la structure d'accès

Posted on February 17th, 2015

Séparez les schémas objets de la notion de propriétaire. Les schéma par défaut est dbo, qui signifie database owner est un vestige de l'époque où SQL Server isolait les ensembles de bases de données par usagers. Depuis la version 2005, les tables appartiennent à un schéma (ne pas confondre avec un diagramme). Il s'agit d'un regroupement de tables portant un nom. Il est donc possible d'avoir plusieurs tables de même noms dans des schémas différents d'une même base de données. Les programmeurs C# peuvent faire l'analogie avec les namespaces puisqu'on utilise le point comme délimiteur, ou bien on peut aussi les voir comme des dossiers de tables.

Sur SQL Server on a donc la structure d'accès à 4 niveau séparés par des points :
Serveur.BaseDeDonnées.Schéma.Objet ou même plus simplement Serveur...Objet ou encore le très commun Schéma.Objet

Notez qu'il n'est pas toujours nécessaire de spécifier le serveur, la base de donnée ou le schéma car le contexte nous place déjà dans une base de donnée et un schéma par défaut. C'est suffisant pour beaucoup d'utilisations.

Cette notation est fort utile pour intégrer plusieurs sources de données dans une opération, mais peut devenir très lourde si on ne fait pas attention en utilisant du code généré par un outil. Gardez votre code clair et léger en tenant compte du contexte! Au besoin changez de base de donnée à l'aide de la commande USE. Il n'y a malheureusement pas l'équivalent aussi simple pour changer de schéma courant. Le schéma par défaut étant un attribut de l'utilisateur, il est possible de le changer avec la commande ALTER USER, mais je ne recommande pas cette approche lourde.

Plus de détails sur le site de Microsoft.

T-SQL Chargement de données

Posted on February 17th, 2015

Bien que plusieurs logiciels choisissent de faire leur chargement de données "un enregistrement à la fois" à l'aide de la commande INSERT, le T-SQL offre la possibilité de charger massivement des données tabulaires à partir d'une liaison de données ou d'un fichier.

Il s'agit essentiellement d'utiliser la commande BULK INSERT pour effectuer le chargement à partir d'un fichier de type CSV.

Les deux dernières options sont facultatives:

Si vous fournissez une colonnes identitée, vous aurez sans-doute besoin de mettre le paramètre KEEPIDENTITY afin d'utiliser les numéros que vous fournissez au lieu d'en générer de nouveaux.
Dans certains cas vous aurez besoin de préserver les accents en utilisant le paramètre CODEPAGE = 'RAW'

Ce système à de nombreuses limitations:

Vous devez fournir un fichier avec la même structure que la table cible. Toutes les colonnes doivent être parfaitement compatibles. Il aussi est possible de fournir un fichier de directives pour arrimer un format différent, mais ce n'est pas simple.
Si vous importez des nombres en virgules flottantes, le format de ceux-ci ne sera peut-être pas compatible en raison des paramètres régionaux (virgule vs point).
Le système ne supporte pas les titres de colonnes en première rangées. En aucun cas vous ne pourrez laisser les entêtes de colonnes CSV.

Voir le site de Microsoft pour plus de détails sur la commande BULK INSERT.

T-SQL Les types de données

Posted on February 16th, 2015

[Bon résumé sur les types SQL, tiré de W3Schools, à retravailler pour faire des recommandations]

En gros et gras le plus populaire... adéquat si vous n'avez besoin d'aucune optimisation, mais à optimiser en contexte industriel.

Type	Description	Storage
char(n)	Fixed width character string. Maximum 8,000 characters	Defined width
varchar(n)	Variable width character string. Maximum 8,000 characters	2 bytes + number of chars
varchar(max)	Variable width character string. Maximum 1,073,741,824 characters	2 bytes + number of chars
text	Variable width character string. Maximum 2GB of text data	4 bytes + number of chars
nchar	Fixed width Unicode string. Maximum 4,000 characters	Defined width x 2
nvarchar	Variable width Unicode string. Maximum 4,000 characters
nvarchar(max)	Variable width Unicode string. Maximum 536,870,912 characters
ntext	Variable width Unicode string. Maximum 2GB of text data
bit	Allows 0, 1, or NULL
binary(n)	Fixed width binary string. Maximum 8,000 bytes
varbinary	Variable width binary string. Maximum 8,000 bytes
varbinary(max)	Variable width binary string. Maximum 2GB
image	Variable width binary string. Maximum 2GB

Numériques:

Data type	Description	Storage
tinyint	Allows whole numbers from 0 to 255	1 byte
smallint	Allows whole numbers between -32,768 and 32,767	2 bytes
int	Allows whole numbers between -2,147,483,648 and 2,147,483,647	4 bytes
bigint	Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807	8 bytes
decimal(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0	5-17 bytes
numeric(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0	5-17 bytes
smallmoney	Monetary data from -214,748.3648 to 214,748.3647	4 bytes
money	Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
float(n)	Floating precision number data from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.	4 or 8 bytes
real	Floating precision number data from -3.40E + 38 to 3.40E + 38	4 bytes

Date types:

[Clarifier et recommander l'usage du GMT]

Data type	Description	Storage
datetime	From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds	8 bytes
datetime2	From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds	6-8 bytes
smalldatetime	From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute	4 bytes
date	Store a date only. From January 1, 0001 to December 31, 9999	3 bytes
time	Store a time only to an accuracy of 100 nanoseconds	3-5 bytes
datetimeoffset	The same as datetime2 with the addition of a time zone offset	8-10 bytes
timestamp	Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

Autre:

Data type	Description
sql_variant	Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier	Stores a globally unique identifier (GUID)
xml	Stores XML formatted data. Maximum 2GB
cursor	Stores a reference to a cursor used for database operations
table	Stores a result-set for later processing