What is the significance of COLLATE in a database script file?

I have db script I use for a piece of software I've created - and I
have (and understand its meaning:

COLLATE SQL_Latin1_General_CP1_CI_AS

My DB works with or without that statement - so I'm trying to figure
out the significance of having it or not having it.  I guess I'm having
difficulty in understanding why I even need it in the first place (even
though I do know what it's used for).

It basically decides the ordering of the data and whether the database or
whatever object to which its applied is case sensitive. The different types
of collations can be found by this query, it gives the discription too.

select * from ::fn_helpcollations()

CI = case insesitive.
AS = Accent sensitive.

There is a Server level collation default;
a database can have a different collation default,
a table can have a different collation default,
a column can have a different collation,
AND you can even use the collation indicator in a query to affect the order
for that specific query.

When you use various tools, EM/QA, to script out databases, tables, etc. the
collation for that object is included -EVEN if it is the same for the
database -which can be helpful if you execute that script creating the
object in another server/database and the object specific collation is
important to preserve.

Show quoteHide quote

column dataType
Joining 2 select statement with like fields
Applying a function to a subquery
Query from a dynamic table
Case statement question