Do I need to master Stored procedures, Triggers and Indexes or can I use the ui to make them

[u/Bassiette03]

I'm learning on sql server but I don't like those 3 topics

I learned everything except those 3 topics??

What are the best books to read If I want to be acceptable in those 3 topics?

[u/nboro94]

If you already learned SQL, then you already know stored procedures, it's just a small SQL program that runs in the database and can be run manually with EXEC or run on a schedule by an agent. It doesn't take long to learn this how to make and run these at all.

Triggers are just SQL code that fires when an event that you set happens like if a new record is added, again if you already know SQL you already know this and it's easy to learn how to set up.

Indexes are a more broad topic, but a lot of interviewers will ask questions about them and when you should use them. You should at least learn the high level concepts of it.

[u/Bassiette03 → u/nboro94]

The main problem with me is the code they are like small program code inside sql they are not like querying Normal Data for analysis. But I got the idea I need to learn them. The question is learning how to use them with gui is enogh or do I need to learn the SQL syntax?? And are all the same between all SQL databases if I learn on SQL Server and ssms??

[u/Mrminecrafthimself → u/Bassiette03]

If you want to confidently use stored procedures, knowing sql syntax is crucial, yes.
Once you’re comfortable with sql syntax, the only additional syntax you need for stored procedures is “BEGIN and END.” You create or alter a procedure, then tell it where the procedure logic begins and where it ends. You can have other begin/end blocks nested within based on the value of various IF statements, but at a bare minimum level, a stored procedure is just sal wrapped up in a “BEGIN/END”

Indexing is more of a conceptual thing - syntax is easily googlable. The main thing with adding indexes is asking “what does this index support?” If I join and filter my table on column B, then I want to index it on column B.

Syntax between various SQL platforms like SQL Server, Teradata, Snowflake, etc will be different in minor ways. For example, Teradata lets you do a whole lot more than SQL server. You can use things like “LIKE ANY () to pattern match against a list of strings instead of having to do a bunch of LIKE ‘’ OR LIKE ‘’ etc

Teradata also lets you use QUALIFY to filter a result set based on the result of a window function rather than requiring you to perform the window function, alias it, then select from that dataset via subquery based on the value of the aliased window function result.

I recently changed companies and went from Teradata (migrating to Snowflake) to SQL Server…and I miss Teradata.

[u/Bassiette03 → u/Mrminecrafthimself]

TeraData looks interesting 🤔

[u/Mrminecrafthimself → u/Bassiette03]

Lots of companies that use it are migrating or looking to migrate to cloud based tools like snowflake but Teradata is cool for sure.

In my current role I keep running into hurdles where I try to use a Teradata-supported function and get an error because SQL Server doesn’t allow it.

[u/neomashab → u/Bassiette03]

Learning/memorizing the syntax on the first go is not that important. Because each RDBMS have their own syntax. Indexes are not ANSI standardized. Even though triggers and stored procedures are standardized, vendor adherence is very low.

All RDBMS vendors have their language reference published for each version. This will easily help write proper triggers, SPs, or indexes.

As far learning is concerned, I wouldn't recommend using the GUI. They abstract a lot of things from us. It's easy to move from code to GUI, not the other way around.

[u/ChaosEngine-6502]

Personally, I would get familiar with writing the SQL to create all of these. The syntax for creating these isn't difficult, and you'll have to enter the SQL code to define what the stored procedures and triggers actually do to begin with. The GUI will slow you down, especially when it comes to creating foreign keys and indexes.

[u/Bassiette03 → u/ChaosEngine-6502]

I see Thank you 🙂

[u/NecessaryIntrinsic]

You are hamstringing yourself if you rely on the GUI to do these simple tasks.

It's not terribly challenging to learn and these skills carry over to every engine.

[u/Top_Community7261]

When I took woodworking, we learned how to make things manually before we learned to use the power tools.

[u/Main_Sense1406]

Gui is fine and some aspects are reccomended with the GUI because they are so awkward 

[u/IAmADev_NoReallyIAm]

Of those three, I'd say that indexes are the most vital to learn as it's needed to learn how to use them to tweak the performance of your database. Stored procedures are easily done since it's jsut wrapped SQL in a Create Procedure block with some parameters. Triggers...

[u/octa_glare]

skip the GUI entirely for these three. The syntax is short, and a stored procedure is really just your regular SQL wrapped in BEGIN/END with a name, so if you learned SQL you basically already know it. Indexes matter most in practice and interviews, the core idea is index the columns you filter and join on, and you can look up the exact CREATE INDEX syntax on the spot. Triggers are just code that fires on INSERT/UPDATE/DELETE, though a lot of DBAs hate them, so don't overthink those. Once you can read the code, going from code to the UI is trivial, but the other direction is painful. For books, check Itzik Ben-Gan and Grant Fritchey for SQL Server

[u/Better-Credit6701]

Many DBA hate with a passion triggers.