SQL developer

https://www.techonthenet.com/sql_server/indexes.php ALTER procedure [dbo].[AddNumberFromString] as begin DECLARE @string varchar(100) Declare @addstr int SET @string = 'ma46h1es2hgggggg5' set @addstr=0 WHILE PATINDEX('%[^0-9]%',@string) <> 0 SET @string = STUFF(@string,PATINDEX('%[^0-9]%',@string),1,'') declare @count int set @count=1 while(@count<=len(@string)) begin set @addstr=(SUBSTRING(@string,@count,1))+@addstr set @count=@count+1 end select @addstr as total end -------Missing Number--------------------------- SELECT ID2, Name2, City2, PinCode2 FROM [dbo].[tbl2] DECLARE @tempdata AS TABLE (  ID INT ) DECLARE @MaxID INT = 0, @Counter INT = 1 SELECT @MaxID = MAX(ID2) FROM [dbo].[tbl2] WHILE (@Counter < @MaxID)  BEGIN   INSERT INTO @tempdata VALUES (@Counter)   SET @Counter = @Counter + 1  END SELECT ID FROM @tempdata AS T LEFT JOIN [dbo].[tbl2] AS T2 ON (T2.ID2 = T.ID) WHERE T2.ID2 IS NULL

Relational-databases/indexes/indexes

SQL Server DBA Performance Tuning Questions Question Difficulty = Easy Question 1: Name five different tools which can be used for performance tuning and their associated purpose. Performance Monitor\System Monitor - Tool to capture macro level performance metrics.  Additional information Automate Performance Monitor Statistics Collection for SQL Server and Windows Windows Reliability and Performance Monitor to troubleshoot SQL Server Profiler - Tool to capture micro level performance metrics based on the statements issued by a login, against a database or from host name. Additional information: Tip Category - Profiler and Trace Server Side Trace - System objects to write the detailed statement metrics to a table or file, similar to Profiler. Additional information: SQL Server Performance Statistics Using a Server Side Trace Dynamic Management Views

Q:Find the missing numbers create table MSeq ( id int ) select * from MSeq Declare @minnum int Declare @maxnumber int select @maxnumber=max(ID)from MSeq select @minnum=min(ID) from MSeq --Create table #tblSeq --( -- id int --) Declare @count int set @count=1 --select id into #tblSeq from MSeq begin while(@count<=@maxnumber) begin insert into #tblSeq values(@count) set @count=@count+1 end end select M.id from #tblSeq M left join  MSeq S on S.id=M.id where S.id is null

https://www.dbrnd.com/2015/06/query-to-find-missing-number-id-sql-server/ Query to Find Missing Number and ID in SQL Server In this post, I will give you one full demo to find a missing number of identity column in SQL Server. Before a few days ago, I was working on production report and found that some number is missing. This sequence and missing number are very important. Immediately one requirement come to my desk is, please find a list of all missing number for investigation purpose. Below is a full demo script to find the missing number in SQL Server. First Create one sample table with data. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 /*Create one table to store Numbers*/   CREATE TABLE dbo.tbl_Numbers ( ID INTEGER ) GO   /*Insert some test records. You can also see I have missed many number which we are going to find by query*/   INSERT INTO dbo.tbl_Numbers VALUES (1),(2),(3),(4),(6),(7),(9),(1