Mahmoud Salem Mahmoud

if you search about Underscore this query will not get a true value Where Username Like '%_d' so you need to use : Where Username LIKE '%[_]d'

user following CASE statement: UPDATE [account] SET balance = ( CASE WHEN ((balance - 10.00)

In case you want to return zero, in case a zero devision would happen, you can use: SELECT ( 45 / NULLIF( 0, 0 ) ) AS value For every divisor that is zero, you will get a zero in the result set

if you make simple devide you will get integer without round value so you need to cast int to float Ex : SELECT (CAST(Number1)AS FLOAT)/Number1) AS AnswerFloat1

Use the ~ operator: DECLARE @Bool bit SET @Bool = 0 SET @Bool = ~@Bool SELECT @Boole

select top 10 percent * from [yourtable] order by newid() In response to the "pure trash" comment concerning large tables: you could do it like this to improve performance. select * from [yourtable] where [yourPk] in (select top 10 percent [yourPk] from [yourtable] order by newid()) The cost of this will be the key scan of values plus the join cost, which on a large table with a small percentage selection should be reasonable.

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer DBCC CHECKIDENT (yourtable, reseed, 34)

select * from ItemsPackage where ItemId IN (3,5,2) This will be match as OR Expression to make it get ALL item Id you can make it : select PakageId from ItemsPackage where ItemId in (3,5,2) group by PakageId having count(PakageId) = 3 -- Count of In clause (3,5,2)

To returns the last IDENTITY value produced on a connection get last Identity your Session added by your Connection if they any trigger add to any other tables it will return it SELECT @@IDENTITY To returns the last IDENTITY value produced on a connection and by a statement in the same scope SELECT SCOPE_IDENTITY() To returns the last IDENTITY value produced in a table it returns the identity value generated for a specific table in any session and any scope SELECT IDENT_CURRENT('tablename') To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

If you want to generate a new uniqueidentifier in SQL you can simply use the NEWID() function -- Example 1 SELECT NEWID() -- Example 2 DECLARE @EmployeeID uniqueidentifier SET @EmployeeID = NEWID() -- Example 3 INSERT INTO Employees(EmployeeID, Name) VALUES (NEWID(), 'msm2020')

SELECT Name, Color FROM Production.Product WHERE CONTAINS((Name, Color), 'Red');

WE can make it with Table Query CREATE TABLE [dbo].[user]( [userID] [int] IDENTITY(1,1) NOT NULL, [fcode] [int] NULL, [scode] [int] NULL, [dcode] [int] NULL, [name] [nvarchar](50) NULL, [address] [nvarchar](50) NULL, CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED ( [userID] ASC ), CONSTRAINT [UQ_codes] UNIQUE NONCLUSTERED ( [fcode], [scode], [dcode] ) ) ON [PRIMARY] OR : ALTER TABLE dbo.User ADD CONSTRAINT ucCodes UNIQUE (focde, scode, dcode)

use Usage: EXECUTE xp_fileexist <filename> [, <file_exists INT> OUTPUT] OR Create a function like so: CREATE FUNCTION dbo . fn_FileExists (@ path varchar ( 512 )) RETURNS BIT AS BEGIN       DECLARE @ result INT       EXEC master . dbo . xp_fileexist @ path , @ result OUTPUT       RETURN cast (@ result as bit ) END ; GO Edit your table and add a computed column (IsExists BIT). Set the expression to: dbo . fn_FileExists ( filepath )

To resolve the collation conflict add following keywords around "=" operator. SELECT ID FROM ItemsTable INNER JOIN AccountsTable WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT Collation can affect following areas: 1) Where clauses 2) Join predicates 3) Functions 4) Databases (e.g. TempDB may be in a different collation database_default than the other databases some times)

I'm using full text index search. There are few cases when for our customer it is important to find words even with only one character. Unfortunately it seems that SQL server ignores such words, although I haven't found assurance for that in docs. So here is the scenario. Suppose I want to find word 3. My table data are as follows: select * from test_fts ; id txt ----------- -------------------------------------------------- 1 3 trees 2 33 trees 3 green trees Now I'm trying to find the word 3. select * from test_fts where contains ( txt , '"3"' ) id txt ----------- -------------------------------------------------- Informational : The full-text search condition contained noise word ( s ). select * from test_fts where contains ( txt , '"3*"' ) id txt ----------- -------------------------------------------------- 2 33 trees OK 3 is a nois...

SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search September 5, 2008 by pinaldave Full Text Index helps to perform complex queries against character data.  These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns. Software developer Monica who helped with screenshots also informed that this feature works with RTM (Ready to Manufacture) version of SQL Server 2008 and does not work on CTP (Community Technology Preview) versions. To create an Index, follow the steps: Create a Full-Text Catalog Create a Full-Text Index Populate the Index 1) Create a Full-Text Catalog Full – Text can also be created while creating a Full-Text Index in its Wizard. 2) Create a Full-Text Index 3) Populate the Index As the Index Is created and populated, you c...

You can view all the full text enabled value for each DB with this code: select name , DATABASEPROPERTY ( name , 'IsFulltextEnabled' ) from master .. sysdatabases where dbid > 4 you had to make the catalogs manually: use MyDatabaseName go EXEC sp_fulltext_database 'enable' go