Posts

Showing posts from April, 2018

Split rows into equal number of records per group in sql server

In SQL server by using function NTILE we can split the equal number of records from a table to number of groups

SELECT *,NTILE(5) over( ORDER by Id) GroupNo
from Table


Here records will be assigned to 5 groups (Group No: 1,2,3,4,5).

Get Random alphanumeric number with specific length using SQL Server

Create a function to get the new guid

create view [dbo].[MyNewGuid] as
select newid() as NewID;

Call below user defined function and pass the number of characters to be returned.

CREATE FUNCTION [dbo].[ufn_RandomString](
    @pStringLength int = 10 -- No of characters to be returned
    ,@isupper bit=0 -- will be returned in upper case or lowercase
) returns varchar(max)
as begin
    declare  @RandomStr varchar(max);
    with
    a1 as (select 1 as N union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1 union all
           select 1),
    a2 as (select
                1 as N
           from
                a1 as a
                cross join a1 as b),
    a3 as (select
                1 as N
           from
                a2 as a
                cross join a2 as b),
    a4 as (select
                1 as N
           from
           …