Is there a difference between writing the following in a query:
SELECT * FROM dbo.Product
SELECT * FROM [dbo].[Product]
No difference. The purpose of the brackets is to deal with cases where you have db objects that have names that might confuse the DBMS, e.g. objects with spaces in their names or which are named after reserved words like
Trigger (among other reasons, as pointed out in the comments).
For the purposes of consistency, you can surround all your "objects" (schemae like
dbo, tables, and columns, etc.) with brackets.
SELECT [MyColumn] FROM [MyDatabaseName].[dbo].[My Table Name]
Note, I think that naming your objects with "unconventional" names, is generally bad practice (with some exceptions). But obviously if you're just writing queries against existing schemae that you don't control, it is what it is.