object[] myArray = new object[] { 1,2,3};
ArrayList = new ArrayList(myArray);

COALESCE SQL

COALESCE(expression1,...n) is equivalent to this CASE expression:

SET NOCOUNT ON;
GO
USE tempdb;
IF OBJECT_ID('dbo.wages') IS NOT NULL
DROP TABLE wages;
GO
CREATE TABLE dbo.wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
);
GO
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
VALUES
(10.00, NULL, NULL, NULL),
(20.00, NULL, NULL, NULL),
(30.00, NULL, NULL, NULL),
(40.00, NULL, NULL, NULL),
(NULL, 10000.00, NULL, NULL),
(NULL, 20000.00, NULL, NULL),
(NULL, 30000.00, NULL, NULL),
(NULL, 40000.00, NULL, NULL),
(NULL, NULL, 15000, 3),
(NULL, NULL, 25000, 2),
(NULL, NULL, 20000, 6),
(NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';
GOHere is the result set.

Total Salary

------------

20800.0000

41600.0000

62400.0000

83200.0000

10000.0000

20000.0000

30000.0000

40000.0000

45000.0000

50000.0000

120000.0000

56000.0000

SQL right, left Modulo

declare @a int, @b int, @c int, @d int
select @a = 568788
select @b = left(@a,3)
select @c = right(left(@a,3),2)
select @d = right(left(@a,3),2)%10
SELECT @b, @c, @d

--Output
568 68 8

SQL GROUP BY techniques

SELECT
C.CustomerID, C.CustomerName,
C.CustomerType, C.Address1, C.City,
C.State, S.TotalSales
FROM
Customers C
INNER JOIN
(SELECT
CustomerID, SUM(Sales) as TotalSales
FROM
Sales
GROUP BY
CustomerID) S
ON
C.CustomerID = S.CustomerID

MS SQL Check for empty String

MS SQL one cannot use the <> '' on text, ntext and varchar data types:

SQL:

SELECT * FROM myTable WHERE myField LIKE '_%'

OR If you want to get all the field with empty string use

SQL:

SELECT * FROM myTable WHERE myField NOT LIKE '_%'

What’s the maximum length for VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types?

The maximum storage size for VARCHAR(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes). The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. Since each character in a VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.

The maximum storage size for NVARCHAR(MAX) is also 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes). The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. Since each Unicode character in an NVARCHAR data type uses two bytes, the maximum length for an NVARCHAR(MAX) data type is 1,073,741,822.

The maximum storage size for VARBINARY(MAX) is the same as the maximum storage size for VARCHAR(MAX) and NVARCHAR(MAX), which is 2^31-1 (2,147,483,647 bytes or 2GB - 1 bytes). The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.

Full-Text Indexing Workbench

Full-Text Indexing Workbench