CREATE TABLE dbo.MyTable  
    (Col1 int NOT NULL,  
     Col2 nchar(10) NULL,  
     Col3 nvarchar(50) NULL);  
GO  

-- Creating
CREATE CLUSTERED INDEX 
    IX_TableName_ColName ON dbo.MyTable (Col1);   
GO  

-- Deleting
DROP INDEX 
    IX_TableName_ColName ON dbo.MyTable,
    [, ... n]  [;]
GO  

CREATE FUNCTION 
      GetProductInfoByID(@ProductID INT)
RETURNS TABLE
AS
    RETURN
      (SELECT ProductID,
              Name,
              ProductNumber,
              ProductSubcategoryID
       FROM   Production.Product
       WHERE  ProductID = @ProductID) 

SELECT * FROM GetProductInfoByID(1);

-- Usage
SELECT SO.SalesOrderID,
       SO.SalesOrderDetailID,
       SO.CarrierTrackingNumber,
       SO.UnitPrice,
       SP.ProductID,
       SP.Name
FROM   [Sales].[SalesOrderDetail] SO
       CROSS|OUTER APPLY 
           dbo.GetProductInfoByID(So.ProductID) SP 

CREATE FUNCTION dbo.GetAdventureBikes ()
RETURNS @Product TABLE (
  ProductID     INT,
  ProductName   NVARCHAR(50),
  ProductNumber NVARCHAR(25),
  SubCategory   NVARCHAR(50))
AS
  BEGIN
    INSERT @Product
      (ProductID,
       ProductName,
       ProductNumber,
       SubCategory)
    SELECT P.ProductID,
       P.Name   AS ProductName,
       P.ProductNumber,
       PSC.Name AS SubCategory
    FROM   Production.Product P
       INNER JOIN Production.ProductSubcategory PSC
         ON P.ProductSubcategoryID = PSC.ProductSubcategoryID
       INNER JOIN Production.ProductCategory PC
         ON PSC.ProductCategoryID = PC.ProductCategoryID
    WHERE  P.ProductSubcategoryID IS NOT NULL
    AND PC.ProductCategoryID = 1;
      
    INSERT @Product
       (ProductID,
       ProductName,
       ProductNumber,
       SubCategory)
    SELECT P.ProductID,
       P.Name   AS ProductName,
       P.ProductNumber,
       PSC.Name AS SubCategory
    FROM   Production.Product P
       INNER JOIN Production.ProductSubcategory PSC
       ON P.ProductSubcategoryID = PSC.ProductSubcategoryID
       INNER JOIN Production.ProductCategory PC
       ON PSC.ProductCategoryID = PC.ProductCategoryID
    WHERE  P.ProductSubcategoryID IS NOT NULL
    AND PC.ProductCategoryID = 3;

    RETURN
  END
-- EXECUTION
SELECT ProductName, 
       ProductNumber, 
       SubCategory  
FROM dbo.GetAdventureBikes();
                       

CREATE FUNCTION dbo.GetProductsByCategory (@CategoryID INT)
RETURNS TABLE
AS
    RETURN
    (SELECT P.ProductID,
          P.Name   AS ProductName,
          P.ProductNumber,
          P.ListPrice,
          PC.Name  AS Category,
          PSC.Name AS SubCategory
    FROM  Production.Product P
          INNER JOIN Production.ProductSubcategory PSC
            ON P.ProductSubcategoryID = PSC.ProductSubcategoryID
          INNER JOIN Production.ProductCategory PC
            ON PSC.ProductCategoryID = PC.ProductCategoryID
    WHERE  P.ProductSubcategoryID IS NOT NULL
          AND PC.ProductCategoryID = @CategoryID) 

-- EXECUTION
SELECT ProductName, ProductNumber, SubCategory 
     FROM dbo.GetProductsByCategory(1);

CREATE FUNCTION dbo.GetProductCount ()
RETURNS INT
AS
BEGIN
    DECLARE @Count INT = 0;
    SELECT @Count=COUNT(1) 
         FROM Production.Product
    RETURN @Count;
END
GO
-- EXECUTION
SELECT dbo.GetProductCount()

-- Parameterized Scalar Function
CREATE FUNCTION dbo.Divide (@A INT, @B INT)
RETURNS INT
AS
BEGIN
   RETURN @A / @B
End
GO
-- EXECUTION
SELECT dbo.Divide (4,2)

-- Scalar function syntax
CREATE|ALTER FUNCTION [ schema_name. ] function_name   
( [ { @param_name [ AS ]
          [ type_schema_name. ] param_data_type   
    [ = default ] [ READONLY ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]  


-- Table-Valued function syntax
CREATE|ALTER FUNCTION [ schema_name. ] function_name   
( [ { @param_name [ AS ]
        [ type_schema_name. ] param_data_type   
    [ = default ] [ READONLY ] }   
    [ ,...n ]  
  ]  
)  
RETURNS TABLE  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    RETURN [ ( ] select_stmt [ ) ]  
[ ; ]  

-- Create indexes in parallel
SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX q1 ON test_btree (id);

SELECT dbms_metadata.get_ddl('VIEW', view_name) FROM user_views;

select wc.Id as ContractId, TemporaryContractType, wc.DateFrom as ContractDateFrom, wc.DateTo as ContractDateTo, wc.ContractIdx, 
r.EmploymentPercentage, r.Id as RevisionId, r.DateFrom as RevisionDateFrom, r.DateTo as RevisionDateTo, r.MoveContract, 
'(' + CONVERT(varchar(30), wc.CandidateIdx) + ') ' + kand.FNAVN + ' ' + kand.ENAVN as CandidateInfo,
'(' +  CONVERT(varchar(30), r.ConsultantIdx) + ') ' + k.FORNAVN + ' ' + k.ETTERNAVN as ConsultantInfo, 
s.Id as SignatureId, s.SignatureStatus, s.DateSigned, s.SignatureType,
ol.ORDRELINJEID, ol.START as Ordrestart, ol.FORLENGT as Ordreslutt--, d.Id, d.Inbound
from Employment.WorkContract wc
inner join Employment.Revision r on wc.Id = r.WorkContractIdx
inner join KONSULENT k on r.ConsultantIdx = k.ID
inner join Employment.Signature s on r.SignatureIdx = s.Id
inner join KANDIDAT kand on wc.CandidateIdx = kand.ID
--inner join Documents.Signature_Document sd on sd.SignatureIdx = s.Id
--inner join Documents.Document d on d.Id = sd.DocumentIdx
full outer join ORDRELINJE ol on ol.WorkContractIdx = wc.Id
 where CandidateIdx in (480369)
 order by r.Id asc

mysql -u root -p database < script.sql