Interview Question : Adding Each character of a number in a table

CREATE TABLE addnum ( id INT identity(1, 1) ,
                             val INT )
INSERT INTO addnum
SELECT ABS(CAST(NEWID() AS BINARY (6)) % 1000) + 1 randomNumber GO 1000
SELECT *
FROM addnum

;

WITH cte AS
  (SELECT id,
          cast(val AS varchar(10)) val,
          len(val) leng
   FROM addnum),
     cte1 AS
  (SELECT substring(val,1,1) x,
          id,
          1 num
   FROM cte
   UNION ALL SELECT Isnull(substring(cte.val,num+1,1),'0'),
                    cte.id,
                    num+1
   FROM cte1
   JOIN cte ON cte1.id=cte.id
   AND cte1.num<cte.leng)
SELECT cte.val,
       cte.id,
       sum(cast(x AS int)) addition
FROM cte1
JOIN cte ON cte.id=cte1.id
GROUP BY cte.val,
         cte.id
ORDER BY id

Dynamic Restore Script

Hi Folks! Few days back i was working on a project, I was trying to automate few things, I will share most of my work in next blogs, so i have also automate restore process for database having multiple files. It will work well with any number of files. You need to provide database name and backup location.

This script is for Sql Server. 

SET NOCOUNT ON

DECLARE @restoreTable TABLE (
 [LogicalName] NVARCHAR(128)
 ,[PhysicalName] NVARCHAR(260)
 ,[Type] CHAR(1)
 ,[FileGroupName] NVARCHAR(128)
 ,[Size] NUMERIC(20, 0)
 ,[MaxSize] NUMERIC(20, 0)
 ,[FileID] BIGINT
 ,[CreateLSN] NUMERIC(25, 0)
 ,[DropLSN] NUMERIC(25, 0)
 ,[UniqueID] UNIQUEIDENTIFIER
 ,[ReadOnlyLSN] NUMERIC(25, 0)
 ,[ReadWriteLSN] NUMERIC(25, 0)
 ,[BackupSizeInBytes] BIGINT
 ,[SourceBlockSize] INT
 ,[FileGroupID] INT
 ,[LogGroupGUID] UNIQUEIDENTIFIER
 ,[DifferentialBaseLSN] NUMERIC(25, 0)
 ,[DifferentialBaseGUID] UNIQUEIDENTIFIER
 ,[IsReadOnly] BIT
 ,[IsPresent] BIT
 ,[TDEThumbprint] VARBINARY(32) -- remove this column if using SQL 2005
 )

INSERT INTO @restoreTable
EXEC ('RESTORE FILELISTONLY FROM DISK = ''' + @backuppath + '''')

--SELECT * FROM @fileListTable
SET @Query = 'RESTORE DATABASE [' + @Dbname + '] FROM  DISK = N''' + @backuppath + ''' 
WITH  FILE = 1,'

SELECT @Query = @Query + ' 
MOVE N''' + LogicalName + ''' TO N''' + PhysicalName + ''','
FROM @restoreTable

SET @Query = @Query + ' 
NOUNLOAD,  REPLACE,  STATS = 5'

PRINT @Query
--Exec(Query)