Count Number Of Word In Sql Server

We Count HowMany Words Present in This String Through Function in Sql Server
Then Use This Code

CREATE FUNCTION fnCountOccurences     
               (@ShortString VARCHAR(100),     
                @LongString  VARCHAR(8000))     
RETURNS INT     
AS     
  BEGIN     
    DECLARE  @Text      VARCHAR(8000),     
             @Frequency INT     
         
    SET @Text = @LongString     
         
    SET @Text = REPLACE(@Text,@ShortString,'')     
         
    SET @Frequency = (len(rtrim(@LongString)) - len(rtrim(@Text)))     
                    / len(rtrim(@ShortString))     
         
    RETURN (@Frequency)     
  END

We Get OutPut:-

 SELECT[dbo].[fnCountOccurences](',','a,b,c,c,')
Execute
OutPut- 4

Read More

Reset Identity Column in Sql Server

We Want To Reset Identity Column Then Use Code.If We Delete column By Id Like We delete 5 no id and
After Than we Insert New then We get 6 no Id .We Show Data 1 to 4 id then 6 because 5 no id We Deleted Then Use Code and Get 5 id In Example-
Example-
SELECT * FROM vibha1<Table Name> --Here Vibha1 is TableName
We get Values-

Delete data  from table
Ex.
DELETE FROM vibha1 WHERE id=6
One Value Delete then Insert Value
INSERT INTO vibha1(name) VALUES('vibha1')

Get Values-

Then We Use Code
Syntax: DBCC CHECKIDENT( <TableName>, RESEED, <ValuesThen Start>)
And Delete id 7 record
DELETE FROM vibha1 WHERE id=7
Then  Execute This
 DBCC CHECKIDENT( vibha1 , RESEED, 5)
Then Insert
INSERT INTO vibha1(name) VALUES('vibha1')
Now Get Result

Read More

Asp.net- MultiView

MultiView and View controls allow you to divide the content of a page into different groups, displaying only one group at a time. Each View control manages one group of content and all the View controls are held together in a MultiView control.
The MultiView control is responsible for displaying one View control at a time. The View displayed is called the active view.

The syntax of MultiView control is:
<asp:MultView ID= "MultiView1" runat= "server">

</asp:MultiView>

The syntax of View control is:

 <asp:View ID= "View1" runat= "server">
</asp:View> 

However, the View control cannot exist on its own. It would render error
 if you try to use it stand-alone. It is always used with a Multiview 
control as:

<asp:MultView ID= "MultiView1" runat= "server">
   <asp:View ID= "View1" runat= "server"> </asp:View>
</asp:MultiView> 

Properties

ActiveViewIndex- A zero based index that denotes the active view. If no view is active, then the index is -1.

Example-

 Aspx Source Code-

<table border="0" cellpadding="2" cellspacing="3" width="100%">
<tr>
<td>
<asp:LinkButton ID="lnkTab1" runat="server" OnClick="lnkTab1_Click">Tab1</asp:LinkButton></td>
<td>
<asp:LinkButton ID="lnkTab2" runat="server" OnClick="lnkTab2_Click">Tab2</asp:LinkButton></td>
<td>
<asp:LinkButton ID="lnkTab3" runat="server" OnClick="lnkTab3_Click">Tab3</asp:LinkButton></td>
</tr>
<tr>
<td colspan="3">
<asp:MultiView ID="MultiView1" runat="server">
<table width="100%" cellpadding="2" cellspacing="5">
<tr>
<td>
<asp:View ID="View1" runat="server">
Content 1 goes here</asp:View>
</td>
<td>
<asp:View ID="View2" runat="server">
Content 2 goes here</asp:View>
</td>
<td>
<asp:View ID="View3" runat="server">
content 3 goes here</asp:View>
</td>
</tr>
</table>
</asp:MultiView></td>
</tr>
</table

C# Code

protected void Page_Load(object sender, EventArgs e)

{

  if (!IsPostBack)

   {

   SetDefaultView();

   }

}

private void SetDefaultView()

{

MultiView1.ActiveViewIndex = 0;

}

protected void lnkTab1_Click(object sender, EventArgs e)

{

MultiView1.ActiveViewIndex = 0;

}

protected void lnkTab2_Click(object sender, EventArgs e)

{

MultiView1.ActiveViewIndex = 1;

}

protected void lnkTab3_Click(object sender, EventArgs e)

{

MultiView1.ActiveViewIndex = 2;

}

OutPut-

Read More

Table Structure Acces in Database

Table Structure Get From  Database in Sql Server

If We Want To get Any  Table Structure from Database .
Show How Many Column Created and Datatype also Then Use This Code

Create Procedure in Sql Server

CREATE Procedure dbo.Gettable     
@tablename nvarchar(100)   
as     
    DECLARE    
           @object_name SYSNAME  ,      
          @object_id INT       
        , @SQL NVARCHAR(MAX)       
           
    SELECT       
          @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'       
        , @object_id = [object_id]       
    FROM (SELECT [object_id] = OBJECT_ID(@tablename, 'U')) o       
           
    SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((       
        SELECT CHAR(13) + '    , [' + c.name + '] ' +        
            CASE WHEN c.is_computed = 1       
                THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)       
                ELSE        
                    CASE WHEN c.system_type_id != c.user_type_id        
                        THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'        
                        ELSE '[' + UPPER(tp.name) + ']'        
                    END  +        
                    CASE        
                        WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')       
                            THEN '(' + CASE WHEN c.max_length = -1        
                                            THEN 'MAX'        
                                            ELSE CAST(c.max_length AS VARCHAR(5))        
                                        END + ')'       
                        WHEN tp.name IN ('nvarchar', 'nchar')       
                            THEN '(' + CASE WHEN c.max_length = -1        
                                            THEN 'MAX'        
                                            ELSE CAST(c.max_length / 2 AS VARCHAR(5))        
                                        END + ')'       
                        WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')        
                            THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'       
                        WHEN tp.name = 'decimal'       
                            THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'       
                        ELSE ''       
                    END +       
                    CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id        
                        THEN ' COLLATE ' + c.collation_name       
                        ELSE ''       
                    END +       
                    CASE WHEN c.is_nullable = 1        
                        THEN ' NULL'       
                        ELSE ' NOT NULL'       
                    END +       
                    CASE WHEN c.default_object_id != 0        
                        THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +        
                             ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)       
                        ELSE ''       
                    END +        
                    CASE WHEN cc.[object_id] IS NOT NULL        
                        THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]       
                        ELSE ''       
                    END +       
                    CASE WHEN c.is_identity = 1        
                        THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +        
                                        CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'        
                        ELSE ''        
                    END        
            END       
        FROM sys.columns c WITH(NOLOCK)       
        JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id       
        LEFT JOIN sys.check_constraints cc WITH(NOLOCK)        
             ON c.[object_id] = cc.parent_object_id        
            AND cc.parent_column_id = c.column_id       
        WHERE c.[object_id] = @object_id       
        ORDER BY c.column_id       
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') +        
        ISNULL((SELECT '       
        , CONSTRAINT [' + i.name + '] PRIMARY KEY ' +        
        CASE WHEN i.index_id = 1        
            THEN 'CLUSTERED'        
            ELSE 'NONCLUSTERED'        
        END +' (' + (       
        SELECT STUFF(CAST((       
            SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +       
                    CASE WHEN ic.is_descending_key = 1       
                        THEN ' DESC'       
                        ELSE ''       
                    END       
            FROM sys.index_columns ic WITH(NOLOCK)       
            WHERE i.[object_id] = ic.[object_id]       
                AND i.index_id = ic.index_id       
            FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'       
        FROM sys.indexes i WITH(NOLOCK)       
        WHERE i.[object_id] = @object_id       
            AND i.is_primary_key = 1), '') + CHAR(13) + ');'       
           
    PRINT @SQL


Use This Code We Show table Structure like

SELECT * FROM tbl_Acc_Type
After Execution We get Table
 
ID    AccType
1    Payment
2    Receipt

We Use table Structure Then Use this Procedure To Directaly Show Structure
Syntax-  GetTable  TableName
Ex.-   GetTable tbl_Acc_Type

After Execution We get Table Structure

CREATE TABLE [dbo].[tbl_Acc_Type]
(
      [ID] [INT] NOT NULL IDENTITY(1,1)
    , [AccType] [NVARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL     
        , CONSTRAINT [PK_tbl_Acc_Type] PRIMARY KEY CLUSTERED ([ID])
);

We Will Create This Table in Other Database and use Structure Also

 

Read More

Unicque Row Num and Same Rank in Sql server

 Use Of  Row_Number() ,Rank() function , Dense_Rank()

Row_Number()

This function will assign a unique id to each row returned from the query.

Consider the following query:

DECLARE @Table TABLE (
      Col_Value varchar(2)
)

INSERT INTO @Table (Col_Value)
      VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C');

SELECT
      Col_Value,
      ROW_NUMBER() OVER (ORDER BY Col_Value) AS 'Row ID'
FROM
      @Table;    

After executing it, we will get:

 Col_Value  Row ID
           A     1
           A     2
           A     3
           B     4
           B     5
           C     6
           C     7
As we notice, each and every row has a unique ID.

Rank()

This function will assign a unique number to each distinct row, but it leaves a gap between the groups. Let me explain with a query, we will use the same query we used above with Rank().

SELECT
      Col_Value,
      Rank() OVER (ORDER BY Col_Value) AS 'Row ID'
FROM
      @Table;

After executing it, we will get:

Col_Value Row ID
     A    1
     A    1
     A    1
     B    4
     B    4
     C    6
     C    6 

As we can see,rowid is unique for each distinct value, but with a gap. What is this gap?
This gap represents number of occurrence. For example: value a is repeated thrice and has rank 1, the next rank will be 1+3=4. Same with the next value 4+2=6 and so on.

Dense_Rank()

 his function is similar to Rank with only difference, this will not leave gaps between groups.
 So if we use the same query used for Rank, then:

SELECT
      Col_Value,
      DENSE_RANK() OVER (ORDER BY Col_Value) AS 'Row ID'
FROM
      @Table;

 After executing it, we will get:

Col_Value Row ID
        A     1
        A     1
        A     1
        B     2
        B     2
        C     3
        C     3

	So it is clear that it generates a unique id for each group and without repetition. As we are clear now what these functions do, we can use them in different scenarios based our need. For example: Row_Number() can be used if we want to skip and fetch some records. Take a look at this query:
	Similarly, we can use Dense_Rank()when we need to calculate  row id with Select Distinct.
	SELECT DISTINCT Col_Value, DENSE_RANK() OVER (ORDER BY Col_Value) AS 'Row ID' FROM @Table; Will return:-
	Col_Value Row ID         A     1         B     2         C     3

Read More

COALESCE function

Concatenate many rows into a single text string using SQL Server
Using  COALESCE function-

 What is COALESCE function-
Concatenate many rows into a single text string with comma separated using SQL Server COALESCE   function.

My table format is like this:
 (Select Name from Tbl_Customer)

Name
Jeet
Nitin
Pulkit
Rahul
Vibha

 I need output like this:
     Name
1. Jeet ,Nitin,Rahul,Pulkit,Vibha

Use This Code 
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name(ColumnName)
FROM  Tbl_Customer
select @Names

Read More

Rank Given In Table

Rank Given in Table in SQL Server
If We Want to Give Indviual Rank And Same Rank to Same data Then Use

Use Of  ROW_NUMBER() Function 
1.This function will assign a unique id to each row returned from the query.
2.RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.


DENSE_RANK()
Trivially, DENSE_RANK() is a rank with no gaps, i.e. it is “dense”. We can write:

Code
Create table-
CREATE TABLE Tbl_CityMaster1
(
Id INT PRIMARY KEY IDENTITY,
CityName nvarchar(20),
C_Id int
)
Data Insertd Then OutPut
OutPut-
Id  CityName   C_Id
1    Allahabad    1   
2    Allahabad   1
3    Allahabad    1   
4    Patna        1   
5    Patna        2   
6    Patna        3   
7    Varanasi    3   
8    Varanasi    3   
9    Varanasi    3   
10    Varanasi    3    

Give Rank 

Query-
SELECT
    Id,CityName,[C_id],
    Row_index = ROW_NUMBER() OVER(PARTITION BY [C_Id] ORDER BY [C_Id]),
    SameRank = DENSE_RANK() OVER (ORDER BY [C_Id])
FROM Tbl_CityMaster

OutPut

Id CityName C_Id RowNo SameRank
1    Allahabad    1    1      1
2    Allahabad    1    2      1
3    Allahabad    1    3      1
4    Patna           1    2      2
5    Patna           2    2      2
6    Patna           3     2      2
7    Varanasi      3     1      3
8    Varanasi      3     2      3
9    Varanasi      3     3      3
10    Varanasi    3    4       3

Read More