APPLY (msdn) är ett nytt SQL-kommando i SQL Server från och med version 2005 och finns i två olika varianter, CROSS APPLY och OUTER APPLY.
Kan inte säga att jag förstått dessa funktionerna helt, men jag har hittat ett bra användningsområde för CROSS APPLY. Nämligen att kunna bygga upp VIEWs med data från en relationstabell och sammanfoga dessa i en ny kolumn separerade med valfritt tecken. Detta kallas tydligen "aggregate concatenation".
Läste även att denna metod går emot huvudideén för hur en relationsdatabas är tänkt att fungera, så har du ett behov att göra såna här SQL-frågor kan det vara snart att tänka om ifall det går att göra på ett bättre sätt. Eller kanske hämta ut datan separat och bygga ihop vyn i buisness-lagret.
Kommer här visa hur det kan fungera genom ett exempel, här först databasdiagrammet.
Sen ett skript (skapat med SubSonic) som bygger upp hela databasen:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Products]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Products]( [Id] [int] IDENTITY(1,1) NOT NULL, [ProductName] [nvarchar](150) COLLATE Finnish_Swedish_CI_AS NULL, CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END /****** Object: Table [dbo].[Colors] Script Date: 07/07/2008 16:58:21 ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Colors]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Colors]( [Id] [int] IDENTITY(1,1) NOT NULL, [ColorName] [nvarchar](150) COLLATE Finnish_Swedish_CI_AS NULL, CONSTRAINT [PK_Colors] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END /****** Object: Table [dbo].[ProductColor] Script Date: 07/07/2008 16:58:21 ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductColor]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[ProductColor]( [ProductId] [int] NOT NULL, [ColorId] [int] NOT NULL, CONSTRAINT [PK_ProductColor] PRIMARY KEY CLUSTERED ( [ProductId] ASC, [ColorId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END /****** Object: View [dbo].[ListOfColorsView] Script Date: 07/07/2008 16:58:21 ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ListOfColorsView]')) EXEC dbo.sp_executesql @statement = N'CREATE VIEW dbo.ListOfColorsView AS SELECT DISTINCT ProductName, ListOfColors = LEFT(o.list, LEN(o.list) - 1) FROM Products CROSS APPLY (SELECT CONVERT(VARCHAR(12), ColorName) + '','' AS [text()] FROM dbo.Colors c INNER JOIN dbo.ProductColor ON c.Id = dbo.ProductColor.ColorId WHERE (dbo.ProductColor.ProductId = dbo.Products.Id) ORDER BY c.ColorName FOR XML PATH('''')) o(list) ' IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductColor_Colors]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductColor]')) ALTER TABLE [dbo].[ProductColor] WITH CHECK ADD CONSTRAINT [FK_ProductColor_Colors] FOREIGN KEY([ColorId]) REFERENCES [Colors] ([Id]) ALTER TABLE [dbo].[ProductColor] CHECK CONSTRAINT [FK_ProductColor_Colors] IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductColor_Products]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductColor]')) ALTER TABLE [dbo].[ProductColor] WITH CHECK ADD CONSTRAINT [FK_ProductColor_Products] FOREIGN KEY([ProductId]) REFERENCES [Products] ([Id]) ALTER TABLE [dbo].[ProductColor] CHECK CONSTRAINT [FK_ProductColor_Products]
Och lite testdata:
ALTER TABLE [Colors] NOCHECK CONSTRAINT ALL GO ALTER TABLE [Colors] DISABLE TRIGGER ALL GO
SET IDENTITY_INSERT [Colors] ON PRINT 'Begin inserting data in Colors' INSERT INTO [Colors] ([Id], [ColorName]) VALUES(1, 'Red') INSERT INTO [Colors] ([Id], [ColorName]) VALUES(2, 'Blue') INSERT INTO [Colors] ([Id], [ColorName]) VALUES(3, 'Green') SET IDENTITY_INSERT [Colors] OFF ALTER TABLE [Colors] CHECK CONSTRAINT ALL GO
ALTER TABLE [Colors] ENABLE TRIGGER ALL GO
ALTER TABLE [Products] NOCHECK CONSTRAINT ALL GO ALTER TABLE [Products] DISABLE TRIGGER ALL GO
SET IDENTITY_INSERT [Products] ON PRINT 'Begin inserting data in Products' INSERT INTO [Products] ([Id], [ProductName]) VALUES(1, 'Test product 1') INSERT INTO [Products] ([Id], [ProductName]) VALUES(2, 'Test product 2 - No Colors') SET IDENTITY_INSERT [Products] OFF ALTER TABLE [Products] CHECK CONSTRAINT ALL GO
ALTER TABLE [Products] ENABLE TRIGGER ALL GO
ALTER TABLE [ProductColor] NOCHECK CONSTRAINT ALL GO ALTER TABLE [ProductColor] DISABLE TRIGGER ALL GO
PRINT 'Begin inserting data in ProductColor' INSERT INTO [ProductColor] ([ProductId], [ColorId]) VALUES(1, 1) INSERT INTO [ProductColor] ([ProductId], [ColorId]) VALUES(1, 2) INSERT INTO [ProductColor] ([ProductId], [ColorId]) VALUES(1, 3) ALTER TABLE [ProductColor] CHECK CONSTRAINT ALL GO
ALTER TABLE [ProductColor] ENABLE TRIGGER ALL GO
Det mest intressanta är ju själva vyn som separerar datan från relationstabellen med ett kommatecken mellan varje värde.
SELECT DISTINCT ProductName, ListOfColors = LEFT(o.list, LEN(o.list) - 1) FROM Products CROSS APPLY (SELECT CONVERT(VARCHAR(12), ColorName) + ',' AS [text()] FROM dbo.Colors c INNER JOIN dbo.ProductColor ON c.Id = dbo.ProductColor.ColorId WHERE (dbo.ProductColor.ProductId = dbo.Products.Id) ORDER BY c.ColorName FOR XML PATH('')) o(list)
Svaret från denna vyn ser ut så här:
Test product 1 Blue,Green,Red Test product 2 - No Colors NULL
Har funderat på ibland hur man kan göra foor-loop i SQL Server. Efter en sökning hittade jag svaret hos SQLAuthority som också visar upp exempel på break och continue.
I mitt fall ville jag köra en insert för varje loopning och mitt skript såg ut ungefär som följer. Variablen intFlag används också för att sätta in ett stigande heltal i tabellen.
DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <=100) BEGIN PRINT @intFlag
INSERT INTO TheTable (IntValue,StringValue,CounterValue) VALUES (1,'MyDefaultString,@intFlag)) SET @intFlag = @intFlag + 1 END GO
Vi har tidigare visat hur man kan göra för att kopiera ett enskild fält i SQL Server mellan två databaser. Idag är det dags för att visa hur man kopierar all data i en tabell. Det man ska tänka på är att tillfälligt tillåta insättning av primärnycklar, för att dessa ska gå att kopiera.
SET IDENTITY_INSERT [TargetDB].[dbo].[TableToCopy] ON INSERT INTO [TargetDB].[dbo].[TableToCopy] (Id,Field1,AnotherField,ABoolField) SELECT Id,Field1,AnotherField,ABoolField FROM [SourceDB].[dbo].[TableToCopy] SET IDENTITY_INSERT [TargetDB].[dbo].[TableToCopy] OFF
Att vara slarvig med att stänga databaskopplingar i en webb-app kan ha förödande effekt på prestanda. Att spåra buggarna kan vara svårt ifall man inte är säker på hur man gör.
Ett bra tips kan vara att använda något av följande kommandon i SQL-managern.
EXEC sp_who
EXEC sp_who 'active'
Det finns även en odokumenterad version som heter sp_who2 och som visar några fler parametrar som CPUTime, DiskIO, LastBatch och ProgramName. Samma som innan alltså fast med en 2:a på slutet.
EXEC sp_who2
Med dessa kommandon får man upp en lista över de kopplingar som är igång och dess parametrar. Ifall det är fler än 100-200 stycken samtidigt så kan det betyda att man har problem. En normal sajt bör endast ha några få kopplingar öppna åt gången.
När det börjar gå riktigt åt skoken så kommer följande felmeddelande dyka upp i event-loggarna.
The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Mer läsning om sp_who finns hos dotnetyuppie eller om hur man bör stänga kopplingar i sin kod på 15seconds
Att förlora stora mängder av data är aldrig roligt. Vi hade just ett sådant upplevelse där ett felkonfigurerat skript uppdaterade ALLA rader i databastabellen istället för den tänkta enradsuppdateringen. En riktig klassiker med andra ord.
Som tur var hade vi en relativt färsk backup, och Glenn kunde förda över datan från backup-databasen, med ett ihopknåpat superskript. Så här ser det ut för den som är intresserad.
UPDATE [RealDB].[dbo].[TheTable] SET LostDataColumn = (SELECT LostDataColumn FROM [BackupDB].[dbo].[TheTable] WHERE [BackupDB].[dbo].[TheTable].Id = [RealDB].[dbo].[LostDataColumn].Id)
Här på Code Odyssey har vi börjat använda SubSonic i våra projekt och det är verkligen en fantastisk hjälp när man jobbar med SQL-Server. Men eftersom verktyget är öppen källkod och inte har varit sammanknutet med Microsoft så har vi varit lite nervösa inför framtiden.
Vi har funderat på hur det kommer se ut när LINQ är redo att använda. Kommer det vara ännu bättre än SubSonic, och kommer vi då lockas att byta DAL-lösning en gång till. Har vi satsat på rätt teknik och hur framtidssäker är den?
Men med skaparen på plats i Redmond känns det inte osäkert längre. Rob kommer få i uppgift att bygga ihop SubSonic med det nya MVC-ramverket som är under utveckling. Själva SubSonic kommer även fortsättningsvis vara öppen källkod under MPL 1.1-licens.
Om du inte har hört något om Microsofts planerade Model-view-controller (MVC) arkiterktur, så rekommenderar jag läsa hos Scott Guttrie och Scott Hanselman. Hos den senare finns video-presentationerna från ALT.NET-konferensen i Austin där MVC-ramverket presenterades publikt för första gången.
På de dyrare licenserna av MS SQL Server så får man med Profiler som är ett bra verktyg för att se vilka querys som körs. Denna funktion finns tyvärr inte på SQL Server Express.
Programmet bjuder inte på några fantastiska finesser men klarar av det grundligaste. Förhoppningsvis blir det bättre i framtida versioner. Tack till vår Glenn för fyndet.
Har man ingen strategi för att ta backup av loggfilerna för transaktionerna kan dessa växa något enormt. Då kan det bli läge att krympa ner dem och jobba fram en struktur så att de ska hålla sig på en rimlig nivå.
Istället bör man se till att göra backup på loggen, då frigörs det utrymme som inte behövs. Här följer några kommandon som fungerade bra för mig. Obs var försiktigt när du jobbar med filerna och läs på ordentligt innan precis vad kommandona gör. Jag använde SQL 2005, med detta fungerar säkert på SQL 2000 också.
1. Först skapade jag en Checkpoint
Use MyDatabaseName go Checkpoint
2. Sen gjorde jag en "backup" av loggfilen med alternativet "TRUNCATE_ONLY". Det innebär att ingen backupfil skapas, den frigör bara det utrymme på redan avklarade transaktioner som är onödiga att spara. Med kommandot "SHRINKFILE" förminskas loggfilen, i detta fall till 30MB
BACKUP LOG MyDatabaseName WITH TRUNCATE_ONLY DBCC SHRINKFILE('DatabasNamn_Log', 30)
The Code Project visar hur man kan göra detta genom programmering. Kolla även Webbproffs där jag gjort lite fler anteckningar.
Nu är det dags för oss att börja att brottas ordentligt med SQL Server 2005, och vi håller på att flytta en del databaser från 2000.
Något som kan ställa till med problem är att de gamla användarna följer med när man importerar databaserna. Vi har tidigare visat hur man kan ändra ägare på tabeller. Enligt min erfarenhet är det bäst att alltid köra med 'dbo' som ägare.
Vid import kan det bli en del konstigheter som resulterar att man inte kan ändra rättigheter alls en användare, och man får följande felmeddelande.
User, group, or role already exists in the current database. (Microsoft SQL Server, Error: 15023)
Detta beror på att Security identification numbers (SID) förväxlade eller 'föräldrarlösa' i sysusers-tabellen, som Falafel Software förklarar.
För att se en lista på övergivna inloggningar kan man göra följade stored procedure:
