Jeg har efterhånden arbejdet med Microsoft SQL Server i mange år.  Da jeg første gang stiftede bekendtskab med SQL Server, hed det Microsoft SQL Server 6.5.  Siden er der kommet mindst versionerne 7, 2000, 2005, 2008 (R2), Azure og 2012 til.

Til trods for det, vil jeg på ingen måde kalde mig ekspert i hverken sproget T-SQL eller i administration af SQL Server.  Det er mere et nødvendigt onde end en lidenskab.  Jeg foretrækker dog langt mere at skrive rå SQL end en ORM, men det kan måske være et emne til et andet indlæg.

Dermed sker det heller ikke sjældent, at jeg opdager nye ting i T-SQL.  Som f.eks. forleden, da jeg faldt over keywords SOME, ANY og ALL.  Tilsyneladende blev de tre operators introduceret med SQL Server 2005, så de kan næppe kaldes nye.  Men de er altså gået min næse forbi til trods for dens størrelse.

Det sjove ved SOME og ANY er, at de fungerer på samme måde.  SOME lader til at være Microsoft’s opfindelse, mens ANY er standarden.  På dansk svarer “some” til ordet “nogle”, mens “ANY” svarer til “nogen”.  Ordet “any” (og “nogen”) bruges i negeringer og i spørgsmål, så måske har Microsoft tænkt, at man skal bruge ANY i kombination med NOT (altså en negering):

SELECT ...
FROM Tabel
WHERE Kolonne > SOME(SELECT ...)

SELECT ...
FROM Tabel
WHERE NOT Kolonne > ANY(SELECT ...)

Jeg tror, de fleste har prøvet det: Man sidder og roder i SQL Server Management Studio med 200 forskellige queries på forskellige databaser, og pludselig er man kommet til at køre en DROP eller DELETE statement i den forkerte database.

Der findes en option i SSMS, der kan hjælpe til med at mindske risikoen for at lave den type bommerter: Man kan farvekode sine queries alt efter, hvilken server man er forbundet til.  F.eks. kan man give alle queries, der er forbundet til ens produktionsserver, en rød farve, mens queries mod testserveren er grønne (den farvekombination virker selvfølgelig kun, hvis man ikke er rød/grøn farveblind).  Det ser ud som vist herunder.

image

Farven sættes i “Connect to Database Engine” skærmbilledet.  Tryk “Options >>” og vælg fanen “Connection Properties”.  Sæt et hak i “Use Custom Color” og vælg den ønskede farve.  SSMS husker den valgte farve pr. server.  Nogle gange er det nødvendigt at genstarte SSMS for at ændringen får effekt på nye queries.

image

Betragt følgende stump SQL og overvej resultatet:

SET ANSI_NULLS ON;
SELECT 
CASE 
    WHEN NOT 'EnStreng' IN ('A', NULL) THEN 'Ja'
    ELSE 'Nej'
END

Hvis du gættede på, at resultatet ville blive ‘Nej’, er du klogere end mig (hvilket mange er).  Sagen er, at med ANSI_NULL ON, vil alle sammenligninger med NULL returnere værdien UNKNOWN, og alle boolean operatorer vil returnere UNKNOWN, når de bruges sammen med UNKNOWN.  Derfor vil første case aldrig evaluere til TRUE.

Som hovedregel må man aldrig bruge =, < og > ved sammenligninger med NULL, når ANSI_NULL er ON.

For fremtidige versioner af SQL Server (hvad så end det betyder) vil ANSI_NULLS altid være ON.  Mere info her: http://msdn.microsoft.com/en-us/library/ms188048(v=sql.110).aspx

 

Forleden blev jeg mindet om SQLCMD Mode i SQL Server Management Studio.  Jeg brugte det flittigt i en periode for nogle år tilbage, men er af en eller anden grund kommet væk fra det.  SQLCMD Mode gør det muligt at kalde sqlcmd.exe direkte i sine SQL scripts i SSMS, og det kan være ganske nyttigt. 

Opsætning

For at slå SQLCMD Mode til, skal man trykke på knappen SQLCMD Mode i værktøjslinjen i SSMS.  Som standard er denne knap ikke synlig.  For at tilføje den skal man gå ind under Tools –> Customize menuen.  Under fanen Commands skal man trykke Add Command og finde knappen under Query kategorien.  Man kan også bruge menuen Query –> SQLCMD Mode.

image

Når SQLCMD Mode er slået til, kan man fra SSMS eksekvere SQLCMD kommander ved at skrive et kolon (‘:’) foran kommandoen.  SQLCMD kommandoer fremhæves som grå linjer i SSMS. 

Ændre forbindelse til SQL Server

En af de gode kommandoer er :Connect.  Med :Connect kan man forbinde til en SQL Server direkte fra sit SQL script:

image

Det betyder, at man kan gemme forbindelsesinformation direkte i sine SQL filer.

Variable

En anden nyttig funktion er muligheden for at definere variable. I nedenstående eksempel benytter jeg det til hurtigt at generere noget testdata.  Jeg har defineret en tabel kaldet Table_1, der har default værdier på alle kolonner.  Vha. SQLCMD’s mulighed for at definere variable og SSMS’ definition af GO, kan jeg eksekvere min INSERT statement 10 gange:

image

Eksekvering af cmd prompt kommandoer

SQLCMD kan selvfølgelig også andre sjove ting.  F.eks. kan man eksekvere almindelige cmd prompt kommandoer vha. ‘!!’, hvis man skulle få lyst til det:

image

Standardopsætning

Hvis man som standard ønsker at åbne alle SQL scripts i SQLCMD Mode i SSMS, skal man gå ind under Tools –> Options og tilvælge det:

image

Når man sidder med T-SQL, er det ofte nyttigt at se nogle enkelte rækker for de tabeller, man arbejder med.  Bare for at se hvad man har med at gøre.  I SQL Server Management Studio er det muligt at lave en genvejstast til at vælge de første X rækker fra en tabel.

  1. I Management Studio vælg menuen Tools –> Options. 
  2. Gå til Keyboard –> Query Shortcuts. 
  3. Vælg en tastaturgenvej (f.eks. Ctrl+3) og skriv “SELECT TOP 10 * FROM” (skriv ikke et tabelnavn).
  4. Tryk “Ok”.

image

Start nu en ny query og opret en forbindelse til en database.  Skriv et tabelnavn (og ikke andet), markér det og tryk Ctrl+3 (hvis altså det var Ctrl+3, du valgte ovenfor).  Voila!

image

 

Det spændende ved programmering er, at man hele tiden lærer noget nyt.  Jeg vil forsøge næsten dagligt at skrive på dotninjas.dk, hvad jeg har lært af små ting fornylig.

I det, der efterhånden har udviklet sig til en lille følgeton om FILESTREAMs i SQL Server, er vi nu kommet til FULLTEXT søgning. For selvom SQL Server håndterer FILESTREAM data som eksterne filer, understøttes FULLTEXT søgning i filerne, og søgningen kan endda gøres afhængig af formatet på indholdet i filerne.  HTML filer er et godt eksempel på dette.

Hvis man forestiller sig et website, hvor brugeren har mulighed for at søge i en række artikler, der ligger gemt som HTML, vil brugerne af et sådant website formentlig have en forventning om, at de gennem søgningen kun søger i artiklernes egentlige tekstindhold.  De forventer ikke at kunne søge efter de HTML tags, der tilfældigvis findes i de gemte artikler.  Så hvis der søges efter “h1”, skal søgningen kun give et resultat, hvis en artikels brødtekst rente faktisk indeholder “h1”, og ikke hvis artiklen har en overskrift med et “<h1>” tag.

FULLTEXT søgning i FILESTREAMs understøtter netop et sådant scenarie.  Ved at fortælle SQL Server, hvilket format de gemte filer er i, vil SQL Server sørge for, at søgningen tilpasses filformatet.  Dette gøres ved at angive en TYPE COLUMN, når man opretter sit FULLTEXT INDEX:

   1:  CREATE TABLE [Article]
   2:  (
   3:      [ArticleId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
   4:      [Title] [nvarchar](4000) NOT NULL,
   5:      [Content] [varbinary](max) FILESTREAM  NULL,
   6:      [FileExtension] [nvarchar](5) NOT NULL,
   7:      CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED 
   8:      (
   9:          [ArticleId] ASC
  10:      ) ON [PRIMARY] FILESTREAM_ON [FILESTREAM1]
  11:  ) ON [PRIMARY] FILESTREAM_ON [FILESTREAM1]
  12:  GO
  13:   
  14:  CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
  15:  GO
  16:   
  17:  CREATE FULLTEXT INDEX ON Article
  18:  (
  19:      Title,
  20:      Content TYPE COLUMN FileExtension
  21:  ) KEY INDEX PK_Article;
  22:  GO

I linje 20 angives, at kolonnen FileExtension indeholder filtypen for Content, hvor artiklernes indhold ligger som FILESTREAM.  For at lade SQL Server foretage en fornuftig søgning i vores HTML artikler, skal vi bare sørge for, at kolonnen FileExtension indeholder teksten “.htm”.

SQL Server understøtter som standard en lang række formater.  Der findes et catalog view kaldet sys.fulltext_document_types, som returnerer de filformater, SQL Server understøtter.  Og flere kan tilføjes – man kan endda selv implementere nogen (i COM).

Med SQL Server 2008 forbedrede Microsoft muligheden for at gemme filer i databasen ved at introducere FILESTREAM.  Med FILESTREAM bliver filer gemt som fysiske filer på disk, mens al tilgang til filerne gik gennem T-SQL.  Dette blev gjort for at forbedre performance betragteligt ift. tidligere versioner af SQL Server, hvor BLOBs blev anvendt til at gemme filer.

Med SQL Server 2012 udbygger Microsoft FILESTREAM yderligere ved at tillade, at man tilgår de fysiske filer direkte gennem Windows I/O API’en.  Den nye funktionalitet kaldes FileTables, og det ser ret interessant ud.  FileTables tillader, at man kan tilgå filerne uden at skulle gå den tunge vej gennem transaktioner, samtidig med at filerne administreres af SQL Server, og dermed indgår i den daglige vedligehold med backups etc.  Fordi FileTables bygger på almindelige filer, tillader det også bruge af standard Windows directories.  FileTables bygger ovenpå FILESTREAM, hvillket betyder, at f.eks. FULLTEXT search virker i filerne.

Det er en længere historie at sætte FILESTREAM og derefter FileTables op i SQL Server 2012.  Når det er gjort, har SQL Server oprettet et fil share på serveren, som man skal bruge til at tilgå filerne, hvis man ønsker at bruge Windows I/O API’en.  Funktionen FileTableRootPath bruges til at få stien til dette share:

   1:  SELECT FileTableRootPath();

For at benytte FileTables kræves en speciel tabel databases.  Denne tabel indeholder en række for hver fil, der gemmes i det oprettede fil share.  Det betyder, at SQL Server intercepter alle I/O til dette fil share.  Filsystemet i Windows understøtter som sådan ikke ACID transaktioner, men så vidt jeg kan forstå, benytter SQL Server diverse locks på tabellen til at styre concurrency

Med et share og en FileTable på plads, kan man nu meget nemt skrive filer til databasen uden brug af SQL (men husk – det er uden en transaktion):

   1:  string rootShare = @"\\machine\blabla";
   2:  for (int i = 0; i < 10; i++)
   3:  {
   4:      File.WriteAllLines(rootShare + String.Format("\\TextFile_{0}.txt", i), new string[] { "Hello " + i });
   5:  }

Følgende SQL afslører, at der rent faktisk er dukket 10 rækker op i vores FileTable:

   1:  SELECT file_stream.GetFileNamespacePath()
   2:  FROM MyFileTable

FileTables er tilgængeligt i den SQL Server 2012, der er til download d.d. (RCO hedder den vist) – og det er også tilgængeligt i Express edition. Sweet.

Det kan undertiden være nyttigt at skulle gemme filer i SQL Server.  Før SQL Server 2008 havde man den mulighed at gemme filer i en BLOB, dvs. man gemte filen binært i en kolonne af type VARBINARY.  Flere har talt imod denne løsning, fordi den – i hvert fald hvis man skal tro rygterne – kunne være ekstremt skadelig for performance.  Flere anbefalede i stedet en løsning, hvor filerne fysisk bliver gemt på disk på et fileshare, og man i stedet nøjes med at gemme filens placering i en kolonne på SQL Server.  Det er naturligvis en lidt skrøbelig løsning, fordi man altid skal huske at opdatere fileshare og database som ét.  Det kan gøres i en transaktion, men det er indiskutabelt en lidt besværlig løsning.

Til det formål introducerede SQL Server 2008 type FILESTREAM.  Med FILESTREAM gemmes filerne fysisk på disk et sted, som SQL Server administrerer, men al tilgang til filerne går gennem SQL Server med T-SQL.

FILESTREAM er som default ikke tilgængeligt på en nyinstalleret SQL Server, så der skal en lille smule arbejde til for at kunne benytte det.

Vha. ADO.NET kan man læse og skrive til en FILESTREAM kolonne.  Det er ikke svært, men det kræver lidt arbejde, hvor man skal have fat i mindre fashionable ting som SqlFileStream og GET_FILESTREAM_TRANSACTION_CONTEXT.  Desuden er der krav om, at både læsning og skrivning omkranses af en transaktion, som man selv er ansvarlig for at oprette.  Alt i alt kunne man godt ønske sig, at der var en lidt mere elegant måde at gøre det på.

Heldigvis findes der en sådan elegant løsning (ellers ville jeg ikke have noget at skrive om), og den findes i Entiry Framework.

Givet følgende tabel med en FILESTREAM kolonne,

   1:  CREATE TABLE Ad
   2:  (
   3:    AdId UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL PRIMARY KEY,
   4:    UserId UNIQUEIDENTIFIER NOT NULL,
   5:    Photo VARBINARY(MAX) FILESTREAM NULL,
   6:    Url NVARCHAR(512) NOT NULL,
   7:    ExpiryDate DATETIME2 NOT NULL
   8:  )

vil Entity Framework automatisk bl.a. generere en property Ad.Photo af typen byte[].  Bemærk i øvrigt, at en tabel, der benytter FIILESTREAM, skal have en ROWGUIDCOL kolonne.  Som i gamle dage er kolonnen med filen stadig af typen VARBINARY, men med et ekstra lille FILESTREAM bagefter, som sørger for al den filbaserede magi.

Med Entity Framework kan man nu gemme sine filer i SQL Server meget nemt ved blot at omsætte sin fil til et byte array:

   1:  using (var db = GetContext())
   2:  {
   3:      Stream fs = File.OpenRead(filePath);
   4:      using (var reader = new BinaryReader(fs))
   5:      {
   6:          var ad = new Ad()
   7:          {
   8:              // Set other properties here.
   9:              Photo = reader.ReadBytes((int) fs.Length)
  10:          };
  11:          db.Ad.AddObject(ad);
  12:          db.SaveChanges();
  13:      }
  14:  }

På tilsvarende vis kan filer læses meget simpelt.  Entity Framework sørger for alt det beskidte arbejde.

Jeg husker den glade sommer (husker dog ikke helt hvornår det var) hvor jeg lå i solen og prøvede at læse op til en SQL Server eksamen. Det lykkedes aldrig at komme forbi kapitlet om opbygningen af den fysiske filstruktur i databasen. Det hører jo med til en MS certificering, så den blev ikke til noget.

SQL Server er den database jeg har arbejdet mest med, men til mit seneste hobby webprojekt var det naturlige valg MySql. Pandasan var så flink at vise mig XAMPP som findes i en light udgave, hvilket var præcis det jeg skulle bruge til mit hygge project. Uden at skulle installere services kan man ha' MySql kørerende på sin lokale maskine på få minutter.

For et hobby website har MySql syntax en meget nyttig kommando LIMIT. Denne kommando alene har tidligere gjort at jeg har prøvet kræfter med MySql. Paging bliver et spørgsmål om man lige kan gange sidestørrelse med sidenummer og det bør være overkommelig matematik for en programør. MySql provideren til .Net findes på MySql's website og er dermed ikke en 3rd party komponent som det var for nogle år siden.

Det virker generelt som om der er sket en del med MySql siden jeg sidst så på det. Og man kan også spørge om Are Commercial Databases Worth It?. Star Wars sammenligninger giver altid letforståelige forklaringer. Jeg har ikke selv oplevet de store forbedringer i SQL Server for mine små projekter. Og t4rzsan har jo set lidt på Parametriserede queries vs. stored procedures, så den konto er også brugt selvom MySql har fået Stored Procedures for snart lang tid siden.

Betyder det så noget at Oracle har købt SUN og dermed også MySql? Jeg har i forbindelse med et kursus på ITU stødt på Oracle og det var ikke overbevist. Det virker unødigt komplekst for at sælge nogle konsulenttimer, hvilket jeg mener er at narre folk. Selvom der er andre firmaer som bygger deres forretning på denne model, er det jo ikke et argument for at gøre det selv.

Nok med brok, se at komme igang med MySql. XAMPP kan downloades som zip og køres næsten uden setup. Det er et værdigt alternativ til SQL Server (også Express).