search

DEFRA / software-development-standards

Standards and guidance relating to software development in Defra
https://defra.github.io/software-development-standards/
Other
45 stars 17 forks source link

Initial thoughts on the tool to use for T-SQL #16

Closed Jozzey closed 5 years ago

Jozzey commented 5 years ago

I would be great if some other T-SQL developers could trial the use of SQL Prompt and let us have their comments.

If anyone knows of any other tools that can analyse & format T-SQL that are known to work well it would be good to know, as I couldn't find anything else worth using.

dandpclements commented 5 years ago

I cant comment re Sql Prompt but I have contacted Diane Lloyd to establish we have budget for this and to ask what the approval process is for purchasing it

ben-sagar commented 5 years ago

I'd be interested to understand how this can link into CI, what standards it applies and how those might be available to other tools.

I'd agree @Jozzey that some wider input from other T/SQL developers would be useful, particularly anyone that is using code quality metrics in CI.

Jozzey commented 5 years ago

I received the following feedback from Lee Gorden:

Had a few of my team have a go at the tool below.

General feedback all seem to really like it. Lifted a couple of quotes off our Slack channel below.

“Been using this SQL extension some more. as long as the standard auto format settings we go with aren't to weird its pretty decent, i can see it being useful if for no other reason that speeding somethings up. you can wrap statements in CTEs etc really easily and there are short cuts for making views etc and probably a whole load of other quick functions i haven't found yet. still not entirely convinced its worth the money they're changing for it but if the people in charge aren't bothered im not either.”

“Yea I've been using it this week its actually really really good.”

One of the points raised was: -

“Lee - I thought we were promoting a code first approach? So we don't want to encourage people to write reams of SQL or has that plan now changed

seems like we want to control something that we don't want people to do”

Jozzey commented 5 years ago

I'd be interested to understand how this can link into CI, what standards it applies and how those might be available to other tools.

@bensagar-ea Currently at Weybridge we don't use CI or source control for SQL Server, we just manually write deployment scripts. So this isn't something I can easily try out. Is there CI/source control software for SQL Server that we should be using in Defra?

From reading the documentation for SQL Prompt I can't find any mention of it being able to integrate into a CI process. However Redgate that make SQL Prompt also make CI software for SQL Server so it may work with this although I can't find any mention of it after skimming the CI documentation.

The code analysis rules it uses are available here https://documentation.red-gate.com/codeanalysis

Jozzey commented 5 years ago

I've just added some SQL Server Database Coding Standards and Guidelines that I found online which looked pretty good to me. Would be good to get other peoples opinions.

My trial version of SQL Prompt has now expired but I don't think that any of these rules contradict those used by SQL Prompt.

Cruikshanks commented 5 years ago

Hold merging.

If these standards were found online, I'd like to investigate whether the source could be directly referred to rather than maintaining our own version of the standards. As new versions come out and the wider community standard changes its a much easier maintenance task is someone else is doing the updates to the guidance 😁.

Jozzey commented 5 years ago

Hold merging.

If these standards were found online, I'd like to investigate whether the source could be directly referred to rather than maintaining our own version of the standards. As new versions come out and the wider community standard changes its a much easier maintenance task is someone else is doing the updates to the guidance .

I found the original document here http://www.sourceformat.com/pdf/sql-coding-standard-sqlserver.pdf My document isn't an exact copy of this one. Following discussion with our SQL Server DBA in Weybridge we've made some changes, one of which was an error in one of the examples.

The document I based mine on, appears to be based on/or is another version of this document https://blog.sqlauthority.com/i/dl/SQLServerGuideLines.pdf

Personally I think it would be better to maintain our own version rather than link to either of the sources I have given. Once agreed these standards aren't likely to change unless there is something we want to add/remove for our own reasons (something we won't be able to do if we link to a document). I can't see that there would be any major change in the T-SQL world any time in the near future that would require alterations to the standards. So keeping them up to date shouldn't really take much effort. The ones at sqlauthority.com were last updated in 2008.

Cruikshanks commented 5 years ago

Thanks for coming back to me @Jozzey . That answers my questions and all makes sense. In which case I'd be happy for this to be merged 😁

Cruikshanks commented 5 years ago

@Jozzey I've updated this to fit in with the new structure. I have also made some tweaks so wondered if you want to check it before I merge it in.

Jozzey commented 5 years ago

@Jozzey I've updated this to fit in with the new structure. I have also made some tweaks so wondered if you want to check it before I merge it in.

Looks good @Cruikshanks Thanks for applying your markdown magic.