# Beginning of the Minute (T-SQL) and One Usage Example

We can use dateadd and datefiff functions to retrieve the beginning of current minute with this query:

```
SELECT  dateadd(mi, datediff(mi, 0, CURRENT_TIMESTAMP), 0) beginningOfTheMinute

```

We can use this method to retrieve last five seconds in any given minutes from a datetime column.
Here is a query to solve the problem I saw in this thread:

There are two solutions given in the thread but with this method we can come up with a query like this:

``` SELECT pulse_timestamp,pulse_value
--dateadd(mi, datediff(mi, 0, pulse_timestamp)+1, 0),
WHERE  pulse_timestamp=dateadd(s,-5, dateadd(mi, datediff(mi, 0, pulse_timestamp)+1, 0))
```

You can test the query with Mr. Celko’s sample table:

```
(pulse_timestamp DATETIME2(0) NOT NULL PRIMARY KEY,
pulse_value INTEGER NOT NULL
CHECK (pulse_value &gt; 0));

VALUES
('2012-11-29 07:58:48', 930), ('2012-11-29 07:58:53', 932), ('2012-11-29 07:58:58', 933),--
('2012-11-29 07:59:03', 935), ('2012-11-29 07:59:08', 935), ('2012-11-29 07:59:13', 937),
('2012-11-29 07:59:18', 938), ('2012-11-29 07:59:23', 940), ('2012-11-29 07:59:27', 941),
('2012-11-29 07:59:33', 943), ('2012-11-29 07:59:38', 944), ('2012-11-29 07:59:43', 945),
('2012-11-29 07:59:48', 946), ('2012-11-29 07:59:53', 948), ('2012-11-29 07:59:58', 949), --
('2012-11-29 08:00:03', 1), ('2012-11-29 08:00:08', 2), ('2012-11-29 08:00:13', 3),
('2012-11-29 08:00:18', 4), ('2012-11-29 08:00:23', 6), ('2012-11-29 08:00:28', 07),
('2012-11-29 08:00:33', 9), ('2012-11-29 08:00:38', 9), ('2012-11-29 08:00:43', 11),
('2012-11-29 08:00:48', 12), ('2012-11-29 08:00:53', 14), ('2012-11-29 08:00:58', 15),--
('2012-11-29 08:01:03', 17), ('2012-11-29 08:01:08', 18), ('2012-11-29 08:01:13', 19),
('2012-11-29 08:01:18', 20), ('2012-11-29 08:01:23', 22), ('2012-11-29 08:01:28', 23),
('2012-11-29 08:01:33', 25), ('2012-11-29 08:01:38', 26), ('2012-11-29 08:01:43', 27),
('2012-11-29 08:01:48', 28), ('2012-11-29 08:01:53', 30), ('2012-11-29 08:01:58', 31), --
('2012-11-29 08:02:03', 33), ('2012-11-29 08:02:08', 34), ('2012-11-29 08:02:13', 35);

```

# Count Positive/Negative Numbers in a Row (T-SQL)

```
CREATE TABLE [dbo].[TableName](
[col1] [int] NOT NULL,
[col2] [int] NOT NULL,
[col3] [int] NOT NULL,
[col4] [int] NOT NULL,
[col5] [int] NOT NULL,
[Id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[TableName] ON

GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (-15, 12, 9, 5, -7, 1)
GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (-3, 5, -15, 6, -16, 2)
GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (10, -14, 8, 17, -16, 3)
GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (-11, -10, 5, 2, -4, 4)
GO
SET IDENTITY_INSERT [dbo].[TableName] OFF
GO

SELECT [col1],[col2],[col3],[col4],[col5],pCount,nCount
FROM   (SELECT
--row_number() over(order by newid()) rn,
*
FROM   TableName) src
CROSS apply (SELECT SUM (CASE
WHEN col >= 0 THEN 1
ELSE 0
END) pCount
FROM   (VALUES ([col1]),
([col2]),
([col3]),
([col4]),
([col5])) d (col)) c1
CROSS apply (SELECT SUM (CASE
WHEN col < 0 THEN 1
ELSE 0
END) nCount
FROM   (VALUES ([col1]),
([col2]),
([col3]),
([col4]),
([col5])) d (col)) c2

```

# Update Duplicate Column With Null (T-SQL)

```

create table tableName (_g_id int identity(1,1), field1 varchar(50))
insert into tableName values ('aaa'),('aaa'),('ccc'),('aaa'),('bbb'),('bbb')

SELECT field1, Row_number() over(partition by field1 order by newid() ) rn

FROM tableName

;WITH mycte
AS (SELECT field1,ROW_NUMBER()
OVER(
partition BY field1
ORDER BY NEWID() ) rn
FROM   tableName)
UPDATE mycte
SET    field1 = NULL
WHERE  rn > 1

SELECT * FROM   tableName

drop table tablename

```

# Importing Large Text File with bcp Command

```
--Run from command line to import one million records and skip the header row
bcp [myDB].dbo.[myTableExistingInDB] in "C:\temp\myFileTest.txt" -c -T -F 2 -S "myServer\myInstanceName"

```

Or

```--Run from SSMS:

DECLARE @sql VARCHAR(4000)
SET @sql='bcp  myDB.dbo.ATable in "C:\temp\test3.txt" -c -T -F 2 -S"'+ @@servername
exec master..xp_cmdshell @sql

```

# Running Total with Reset – SQL Server 2012

The trick part is to create a group based the resetflag column with a SUM Over first.

```CREATE TABLE [dbo].[test1](
[rn] [int] NULL,
[ResetFlag] [int] NULL,
[Value] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (1, 1, 100)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (2, 0, 4)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (3, 0, 8)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (4, 1, 105)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (5, 0, 21)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (6, 0, 5)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (7, 1, 130)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (8, 0, 10)
GO
; with mycte as
(select *, sum(resetflag) Over(order by rn) grn from test1)
SELECT rn,ResetFlag,Value,SUM(value)
OVER(
partition BY grn
ORDER BY rn) AS groupRunningTotal
FROM   mycte
```

# All Combinations of Numbers– T-SQL

This sample code is to show a way for how to get all combination of numbers with T-SQL. Joe Celko has many great solutions with standard compliant SQL query. His SQL books is on my never finishing reading list.
Here is the one I have learned:

```
;WITH mycte(i)
AS (
SELECT 1 as i
Union ALL
SELECT i +1 as seq FROM mycte WHERE i < 4)

SELECT m1.i AS n1, m2.i AS n2, m3.i AS n3, m4.i AS n4
FROM mycte AS m1
CROSS JOIN mycte AS m2
CROSS JOIN mycte AS m3
CROSS JOIN mycte AS m4
WHERE m1.i NOT IN (m2.i, m3.i, m4.i)
AND m2.i NOT IN (m3.i, m4.i)
AND m3.i NOT IN (m4.i)

--http://www.sqlservercentral.com/blogs/steve_jones/2011/06/07/combinations-and-permutations/
/*
n1	n2	n3	n4
4	1	2	3
3	1	2	4
4	1	3	2
2	1	3	4
3	1	4	2
2	1	4	3
4	2	1	3
3	2	1	4
4	2	3	1
1	2	3	4
3	2	4	1
1	2	4	3
4	3	1	2
2	3	1	4
4	3	2	1
1	3	2	4
2	3	4	1
1	3	4	2
3	4	1	2
2	4	1	3
3	4	2	1
1	4	2	3
2	4	3	1
1	4	3	2

*/

```

# T-SQL Restore Column’s Filestream Feature After Modifing Filestream Table From SSMS 2008

When you modify a filestream table to add new column or some other changes through SSMS 2008, the filestream attribute of the column will be lost after the change with the data type varbinary(max) only.
Here is a good user case in this MSDN thread: “Imagine you have a production table with thousands and uploaded files in varbinary(max) column
but stored in the file system using the FILESTREAM capability in 2008. A new or existing developer forgets and makes a simple table
modification using SSMS. Suddenly the uploads begin to fail. The DBA finally determines that the FILESTREAM attribute has been removed
from the varbinary column on this table. How to you place the FILESTREAM attribute back on the varbinary(max)
column without destroying the existing data? ”

The way to get back the filestream feature back (to store files in the filegroup folder) is to create a new column with filestream feature and copy data
from the old column to this new column. The filestream feature will be restored in the table.

Here is the script to finish this process:

```

/* Use your filestream database */

USE yourdatabase
GO

/* Rename the varbinary(max) column  to soemthing like oldColumnName */
EXEC sp_RENAME '[filstramTableName].[binaryfile]', 'oldbinaryfile' , 'COLUMN'
GO

/* create a new varbinary(max) FILESTREAM column to the table */
ALTER TABLE  [dbo].[binaryfile]
ADD [binaryfile] varbinary(max) FILESTREAM NULL
GO

/* Copy the contents of varbinary(max) column to varbinary(max) FILESTREAM column
to trigger the file move to the filegroup folder. It may take some time to finish the process.
*/

UPDATE  [dbo].[filstramTableName]
SET [binaryfile] = [oldbinaryfile]
GO

/* drop the old column */
ALTER TABLE  [dbo].[filstramTableName]
DROP COLUMN oldbinaryfile
GO

```

# When LTRIM/RTRIM Does Not Trim For You, You May Need to Remove Special Character You Cannot See (T-SQL)

```
DECLARE @STR varchar(max)='This sentense has tab and new line

'

--TAB = char(9)
--LF = char(10)
--CR = char(13)

SELECT REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@STR)),CHAR(9), ''),CHAR(10),''),CHAR(13),'')

```