I have two similar dbs on two different servers. I’m trying to join their sys.columns tables, but I don’t understand why this wouldn’t work SELECT * FROM server1.db1.sys.columns t …
Tag: tsql
Find max value from coalesce function
This is my query: declare @t table (date1 date,date2 date,date3 date) insert into @t values (‘2019-01-01′,’2019-01-20′,’2019-02-10′) insert into @t values (null,null,’2019-02-01’) insert into @t …
How to use an IF statement within a view to drop/use a #Temp Table?
I need to create a view, which begins with the following SQL: IF OBJECT_ID(‘tempdb..#TempTable’) Is Not null Drop Table #TempTable I am also rebuilding this #TempTable further on in the view, and …
Maximizing query performance by joining with XML
While working on query performance optimisation, I noticed that the pattern below outperforms by a wide margin other, more obvious, ways of writing the same query. After looking at the execution plans, it appears this is due to parallelism. The table MyTable, has a clustered primary key on (Identifier, MyId, date). The @xml variable usually contains tens of entries and
float to string no scientific notation: SQL Server
Have a problem with some wonky ID’s that I can’t quite figure out. The above is a result of joining two tables, where ID1 = ID2. ID1 cannot be null, however ID2 can be null. What I’m attempting to arrive at is: So I can then drop the other column. I’ve tried casting, str function, removing the period in the
SQL Format(‘MMMM’) does not work in SQL Server 2008
I had this query select format([time], ‘MMMM’) as ‘Month’, count([time]) as ‘Application Usage’, count([time]) as ‘Application Usage’ from UserLogs UL where [time] >= …
Translate Excel business logic to T-SQL
I need to ‘translate’ some business logic from Excel to T-SQL, I’m having a hard time with it. It’s about figures from gates that count how many customers go IN and OUT of the stores. All DATA you …
Deploying multiple SQL jobs in the same .sql file
So this is something I already do with Stored Procedures, and a bunch of other database items, and now I’m trying to do it with jobs. I write a bunch of items to a single .sql file. Other programs I …
Large SQL table delete all duplicates
I have a database table with 33265902 (thirty-three million two hundred sixty-five thousand nine hundred two) records in it. Much of this is made up of duplicates. It is causing me issues as any …
Is there a way to write an insert statement with string_split which ignores duplicates?
I am trying to insert rows into a table using string_split command. It works fine if there are no duplicates. However, if there are duplicates, it errors out. I would like to know how to modify the …