Technical tips for SQL Server

As you were probably aware, last Friday we had techweets which was an initiative by South Asia MVPs to post day long technical tweets. In case you missed it, I'm sharing the tips through this blog which I'd tweeted as a part of this.

1. SQLServer supported float notations are Scientific and Alt. scientific as per ex: SELECT '2d5' + 2e6 + 1e-5.  

2. Lookup() function can be used in SSRS 2008 R2 to merge two dataset fields
   within the same tablix.  

3. fn_dblog() function can be used to retrieve deleted data from active portion of tran log if its not cleared

4. sys.dm_os_windows_info DMV can be used in SQL 2012 to get os locale settings of the server.  

5. sys.dm_server_services DMV can be used from SQL 2008 R2 to get running status of SQL Server services.  

6. Composable DML enables DML data exposed through OUTPUT clause to be used as a source for INSERT query

7. cross server cross db object dependencies can be viewed using sys.sql_expression_dependencies in SQL 2008.  

8. SQL Data Sync can be used for automated periodic data sync up between Win Azure SQL and on premise SQL DB

9. Filtered indexes have has_filter column value as 1 in sys.indexes view.  

10. Filetable last_access_time value remains unchanged until disablelastaccess setting of os is reset by fsutil.  

11. Runtime debugging of SSIS package data flow paths can be done by adding datataps in SSIS 2012.  

12. Pagination can be easily implemented in SELECT queries using ORDER BY..OFFSET construct in SQL 2012

13. sp_foreignkeys gives the details of foreignkeys referring the linked server table's primary key.  

14. Quarter value for a date can be obtained using formula QUOTIENT((MONTH([DateField])-1),3)+1 in DAX.  

15. The SQL agent jobs dependent on an object can be identified using command field in msdb sysjobsteps table.  

16. SSIS 2012 supports ODBC connection managers which can used to connect to SQL Azure instances.  

17. Date and time values passed to SQL Server are interpreted as per language settings of the server.  

18. REPLACE function preserves trailing spaces in SQL 2008 as against trimming performed in SQL 2005.  

19. CDC is implemented in SSIS 2012 using CDC Control flow task and CDC Source and Splitter tasks in data flow  

20. local-name() Xquery function can be used to return node/attribute names within a XML document in T-SQL.

21. PARSE() function in SQL 2012 parses and converts string values to any datatype in specified culture format.

22. Table Value Constructor can be used with FOR XML PATH to generate adhoc XML from set of variable values.

23. Shared cache connection managers enable reuse of cache among packages in SSIS 2012 project deployment model.  

24. DQS cleansing task is available in SSIS 2012 for source data cleansing against a DQS Knowledge base.  

25. HalfYear value for a date can be obtained using formula QUOTIENT((MONTH([ShipDate])-1),6)+1 in DAX.  

26. Expression task is available for variable value manipulations in SSIS 2012 data flow task.  

27. Java.util functions can be used inside SSIS script task for zipping and unzipping of files  

28. Selective XML Indexes enables you to index selected xpath contents within a XML document in SQL 2012.  

29. THROW is preferred over RAISERROR for raising exceptions in SQL 2012. 
30. 29. 29. 29. For easy insertion/modification of sparse columns add a columnset column in table including all of them.

31. sql:column() function can be used to pass table column values inside XML Xpath queries.  

32. TOKEN function in SSIS 2012 can be used for parsing a delimited string and getting a substring within it.   

33. max_worker_thread setting in msdb syssubsystems table controls the no of concurrent running sql agent jobs.   

34. sql:variable() function can be used to pass variable values inside XML Xpath queries.  

35. SQLNativeClient11 the default provider with SQL 2012 wont support SQL2000 linked servers.use v10 if required

36. Attributes within XML node can be retrieved by using NodeName/@* argument within Xpath query.   

37. Running value calculations can be done inline in SQL 2012 using UNBOUNDED PRECEDING option in PARTITION BY.   

38. ORDER BY used inside view from SQL 2005 will not guarantee ordered data retrieval from it.   

39. SSRS 2012 reports can be rendered as images using URL parameters rs:Format=IMAGE&rc:OutputFormat=JPEG/BMP/..   

40. Fast load option can be used in OLEDB destinations in SSIS for faster data transfer.  

41. SQL Data Sync can be used for automated periodic data sync up between Win Azure SQL and on premise SQL DB

42. Filter option in object explorer can be used for quick pattern search for object names and script them out.

43. HOST_NAME () returns name of workstation which runs the t-sql statement.  

44. ISNUMERIC returns 1 for any expression which is valid numeric type ex:$,+,- etc.  

45. Table valued parameters can be passed only in READONLY mode in SQL routines.  

46. The metadata of resultset can be specified using WITH RESULT SETS option in EXECUTE statement SQL 2012.

47. EVENTDATA function can be used to return details on the event inside a DDL or logon trigger..  

48. Style value of 126,127 can be used for parsing XML based date format values to datetime in CONVERT()

49. To select a value among enumerated list by passing an index, use CHOOSE function in SQL 2012  

50. Table Value Constructor can be used with FOR XML PATH to generate adhoc XML from set of variable values.

If you need any more detail/ clarification on any of above technical tip, feel free to comment and I'll reply with requested information.

We're planning more initiatives like this in future too so make sure you follow me on twitter (@visakh16) to get the updates.