To get the first date of the current month, you could a query similar to the following: SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())) +1, GETDATE()), 105) Day1;
Get all the tables with a count of their records with the following query: CREATE TABLE #Temp ( TableName VARCHAR(MAX), Rows INT ); EXEC sp_MSForEachTable @command1 = ‘INSERT INTO #Temp(TableName, Rows) SELECT ”?”, COUNT(*) FROM ?’ SELECT * FROM #Temp ORDER BY Temp.Rows DESC; DROP TABLE #Temp;
If you want to do statistics in SQL, the following tests will be very useful to look into: Pearson’s Correlation Kendall’s Tau Rank Correlation Simple Linear Regressions The Kruskal-Wallis Test The Mann-Whitney U Test Student’s T Test
Working with and comparing multiple queries at the same time can be a pain, at least for me. Two independent query windows take up more space on screen, or having to switch from one query tab to another can become tedious. Solution: The Window Menu You can display two different
Sometimes you want to select all the columns, without using the *. Specifying column names speeds up your query. The problem comes in with large tables or tables with difficult column names. Solution: Type SELECTClick the + next to the desired table in the Object ExplorerDrag the Columns folder to
I hate repetitive tasks. I am not a robot. However, the problem is that there are some tasks that can be quite repetitive or just take too many steps to complete. Say, for example, you have a huge database with lots of tables and stored procedures. Let’s assume further that
This is a neat trick I have learnt recently. When dealing with large lists of information, we, as developers, sometimes need to copy them, then add commas manually. Say, for instance, you have a list of numbers, and you have constructed the query to resemble the query below: SELECT StudentID
We can get all columns in all tables of a specific data type. The example below gets all INT columns in the entire database SELECT OBJECT_NAME(sys.columns.OBJECT_ID) as TableName, sys.columns.name as ColumnName FROM sys.columns JOIN sys.types ON sys.columns.user_type_id = sys.types.user_type_id WHERE sys.types.name = ‘INT’
For building faster SQL queries, follow these hints. 1. Use Smaller batches Always try to delete data or update data in smaller batches. If you try to delete thousands of records in one go, it will impact performance. If you try to update large batches of data in one go,