Find out latest transaction for particular Customers.

select * from  [dbo].[Transaction] order by name

ID	name	Transdate	Transnumber	Product
4	CP	2019-02-12 00:00:00.000	1004	TOY
7	CP	2019-03-01 00:00:00.000	1007	TOY
1	rahul	2019-02-02 00:00:00.000	1001	book
2	rahul	2019-02-12 00:00:00.000	1002	book
3	RAVI	2019-02-15 00:00:00.000	1003	TOY
5	RP	2019-02-13 00:00:00.000	1005	BOOK
8	RP	2019-02-28 00:00:00.000	1008	BOOK
6	SUMIT	2019-02-12 00:00:00.000	1006	BOOK

select max(Transdate),name,product from [dbo].[Transaction] group by product,name

transaction date	name	product
2019-03-01 00:00:00.000	CP	TOY
2019-02-12 00:00:00.000	rahul	book
2019-02-15 00:00:00.000	RAVI	TOY
2019-02-28 00:00:00.000	RP	BOOK
2019-02-12 00:00:00.000	SUMIT	BOOK

Sql table update from one table to other table based on Primary key (ID match both table)

Sql table update from one table to other table based on Primary key (ID match both table)

We have 2 table custmain & custdetail I want update custmain email ID by custdetail emailID.

UPDATE  Custmain

SET

    Custmain.EmailID = CD.uEmailID,Custmain.Name=CD.Fname

FROM

    Custmain as CM

INNER JOIN

    CustDetail as  CD

ON 

    CM.Csid=CD.CsIDU

 

How do you use T-SQL Full-Text Search to get results like Google?

Full-Text Search run against character-based data in SQL Server tables. These queries can include words or phrase searching. Before we can run full-text queries on a table, first we need to create a full-text index on the table. Only one full-text index is allowed per table and this index can contain up to 1024 columns.

The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, char, nvarchar, text, ntext, image, xml, or varbinary.

Full text queries perform searches against text data, in full-text indexes by operating on words and phrases based on rules of a particular language.

To implement full-text indexing in SQL Server, you should take the following steps:

1. Create a full-text catalog, if necessary.
2. Create the full-text index.
3. Modify the list of noise words (SQL Server 2005) or stop words (SQL Server 2008), if necessary.
4. Modify the thesaurus for the language being used, if necessary.

Step 1: Check Search Query with like keyword

                                                    Check output of like query

Step 2: Create Full Text Catalog

                                       Now provide a name to full text catalog.

new catalog has been created in Storage folder.

Step 3: Create Full Text Index

Select one Unique Index. “Full Text Index” table must have at least one unique index.

Select columns name and language types for columns.

Select change tracking.

Now select the full-text catalog for index.

Step 3: Populate the Index

FREETEXT :

FREETEXT command provides the ability to search for a matched term based on the meaning of the terms as opposed to the exact character string. Check here like and Free-text search output.

CONTAINS:

Searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server. CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types.

 

How can get MSSQL Table, StorePocedures, View & Triggers by query

 Query for Table:

 SELECT COUNT(*) AS TABLECOUNT FROM sys.Tables

Query for Store Procedures:

 select count(*) as ProceduresCount from sys.procedures

Query for Triggers:

 SELECT count(*) AS TriggersCount FROM sys.triggers

Query for Views:

 SELECT count(*) AS ViewsCount FROM sys.views

SELECT 'Count' = COUNT(*), 'Type' = CASE type

                WHEN 'C' THEN 'CHECK constraints'

                WHEN 'D' THEN 'Default or DEFAULT constraints'

                WHEN 'F' THEN 'FOREIGN KEY constraints'

                WHEN 'FN' THEN 'Scalar functions'

                WHEN 'IF' THEN 'Inlined table-functions'

                WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraints'

                WHEN 'L' THEN 'Logs'

                WHEN 'P' THEN 'Stored procedures'

                WHEN 'R' THEN 'Rules'

                WHEN 'RF' THEN 'Replication filter stored procedures'

                WHEN 'S' THEN 'System tables'

                WHEN 'TF' THEN 'Table functions'

                WHEN 'TR' THEN 'Triggers'

                WHEN 'U' THEN 'User tables'

                WHEN 'V' THEN 'Views'

                WHEN 'X' THEN 'Extended stored procedures'

    END

    FROM sys.objects

    GROUP BY type

    ORDER BY type

Count	Type
5	NULL
47	NULL
3	NULL
62	Default or DEFAULT constraints
33	FOREIGN KEY constraints
27	Stored procedures
45	System tables
1	Table functions
54	User tables