SQL Server Performance & Troubleshooting – Where Milliseconds Matter 🚀

Hi Guys, Welcome back!   After yesterday's post , here is another one today, always light! Another trick for faster inserts! Enjoy the reading! Multivalues The T-SQL syntax allows the specification of more than one set of values ​​in the INSERT statement  For example: INSERT INTO Table (code,descr) VALUES ('0001','first item'), ('0002','second item'), ('0003','third item') We can specify a maximum of 1000 set of values. Let's see what we can do with it ... In some cases we could use this possibility to gain speed. The test Come on! follow me!   Let's create a simple heap table and insert some data, let's say 100,000 rows CREATE TABLE MOV (id int, Qty float) Now i use this loop to fill the table: DECLARE @i INTEGER ; SET @i = 0; WHILE @i < 100000 -- 100K BEGIN EXEC (' INSERT INTO MOV (id) VALUES (1) ') SET @i = @i + 1 END No...

Hi Guys! Today a light post to read to start the week!   An easy and practical trick related to massive insertions. Do we insert indexes before or after populating a table? Enjoy the reading!     A massive insert Suppose you have to insert a large number of rows into a table that doesn't exist yet. This table will have a clustered index let's say on the ID field. Let's start with a question: When do we create our clustered index? I have seen many times procedures that created the table and put the clustered index on it, then mass insertion took place. Let's do our test! Let's create our table with the command: CREATE TABLE [dbo].[Movements]( [Id] [int] [Qty] [float] NULL, [Price] [float] NULL ) ON [PRIMARY] Right now our table has no indexes so it's called a heap table . Let's create our clustered index: CREATE CLUSTERED INDEX CI_MOVEMENTS_ID ON Movements(ID) Now we insert 10 million rows with this command: INSERT INTO Movement...