r/SQLServer • u/Reasonable-Job4205 • 22d ago
Discussion How much prefix adding do you do to your objects in SSMS? Is it still important?
I am naming all my stored procedures as "spActualSPName". I got into this habit because of working with a SQL database that used MS Access as the front end, so it was helpful to know that the thing I was looking at in VBA was an SP. Is prefixing still needed? Do you do this?
18
u/IanYates82 22d ago
No, haven't done it in a long time, and never really did anyway. I then recently worked in a database that had tbl prefixes, which was annoying, but even more tedious was that almost every column had a "fld" prefix... That was pure noise and, even in the old "prefix everything" mentality, gave zero benefit.
13
u/BrupieD 22d ago
I find this kind of Hungarian notation annoying and unhelpful. SQL Server has system stored procedures prefixed with "sp_" and I don't want to mix mine with those. If there are 100 stored procedures that I have to scroll through and look for the characters that follow this prefix it really isn't helping me. I had a colleague who prefixed all his tables with "tbl." It sucked.
7
8
u/alinroc 4 22d ago
Leave Hungarian Notation in the 20th century. SQL Server (and SSMS) keeps objects organized for you automatically.
3
u/kiwi_bob_1234 22d ago
Hmmm - how do you tell the difference between a view and table when writing queries on the fly
4
u/SQLMonger 21d ago
I have used a suffix of _vw for views so that they are differentiated from tables in query code. I find it helpful to be able to tell what is a base table, a view or a table valued function when reading through and working on code in stored procs, views or report code.
I use suffixes on stored procs to classify them. _Ins, _Upd, _Del, _Rpt, etc. that way the procs related to a table or process end up named similarly and group nicely in SSMS.3
u/kiwi_bob_1234 21d ago
This makes perfect sense to me, not sure why the pushback on this thread tbh
1
u/AngryPets 12d ago
Another question would be: why would you want to HARD-CODE differences between tables and views? If an application needs to work against dbo.Employees ... does it matter (to the application) if the object being QUERIED is a table or view?
There CAN be tangible benefits when it comes to abstraction for NOT differentiating views vs tables.The only real reason anyone 'cares' about tables vs views is when they're edition code or trying to troubleshoot things. I get that ... I don't "like" having to see if dbo.Employees being hit by such and such app is hitting a table or a view ... BUT, in SOME cases, I'll take the 'hit' of having to check 2x locations (think of it as USB-A ... 3 tries and you're plugged in ;) ) just to have flexibility for abstractions later on.
1
u/kiwi_bob_1234 12d ago
Because an application that is writing data to a SQL object absolutely needs to know if it's a table or a view (for dml operations)
0
u/AngryPets 12d ago
That's a very common misperception. Obviously, there are ways that trying to run an UPDATE or INSERT against a VIEW can go TERRIBLY-WRONG(TM) ... but that also doesn't HAVE to be the case.
1
u/kiwi_bob_1234 12d ago
What are you trying to say 🤣 talking absolute drivel at this point
1
u/AngryPets 12d ago
Go read the summary of what UPDATE does in T-SQL:
https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver17> Changes existing data in a table or view in SQL Server. For examples, see Examples.
By hard-coding whether something is a table or a view (just so'z you can look it up later and know it's structure), you're POTENTIALLY losing some abstraction capabilities - or (at least) making them lame (if/when you end up repointing tbEmployees at a VIEW because the CLIX on it is busted/wrong and you're working on real-time fixes without taking a maintenance window to properly fix things, etc.)
1
u/kiwi_bob_1234 12d ago
I get the abstraction argument, but vw_ isn't preventing SQL Server from treating a view abstractly it's just making the object's type explicit to developers. In our codebase that's useful because tables and views have materially different performance, dependency n update characteristics.
If we genuinely want to swap a table for a view without changing consumers, that's an architectral decision, not a reason that vw_ is inherently bad.
1
u/jshine13371 6 21d ago
Because SSMS tells you. Also I usually just keep a good sense of the database holistically.
1
u/kiwi_bob_1234 21d ago
What about in other things that reference those objects - git repo, data factory pipelines, power bi data models - also a new starter won't have that holistic view, much easier to tell them views begin with vw_ and tables dont...
1
u/jshine13371 6 21d ago
Git repos store the code as create / alter scripts, so the type of object is on the same line of code as the name. Not sure why one would care to know that fact in this context anyway.
Data factory pipelines aren't something I've had to deal with in this regard. But I assume good ones have a clear UI that differentiates object types and / or you give the objects your own names in the pipeline anyway.
There's almost 0 need to know the type of object it is in Power BI. One doesn't name their Excel files being consumed by Power BI as "Excel_FileName", nor any of the other data source types accordingly.
1
u/kiwi_bob_1234 20d ago
I mean in other git repos that reference SQL objects (eg, data factory, pbir/pbip) often there's no context about the thing from data factorys perspective so the vw_ helps immensely
Power BI - you've obviously not used it very much but let's say something breaks in a report, my first question is going to be which bi table is causing the issue, and in turn, which SQL object is that table pulling from. The vw_ helps me quickly see if it's a likely a small issue (quick fix in the view, refresh the report) or potentially a larger issue (updating a stored proc, or data factory pipeline, potentially a backfill to grab missing data)
Aside from that just generally having context - views take longer to load in power bi as they need to compile server side, tables are faster
Not rocket science really
1
u/jshine13371 6 19d ago edited 19d ago
I mean in other git repos that reference SQL objects
Not seeing how it helps you to know if you're choosing a Table over a View in the repo for an Azure Data Factory project. What does that matter at the point of reading a Git repo?
Power BI - you've obviously not used it very much but let's say something breaks in a report, my first question is going to be which bi table is causing the issue, and in turn, which SQL object is that table pulling from. The vw_ helps me quickly see if it's a likely a small issue (quick fix in the view, refresh the report) or potentially a larger issue (updating a stored proc
No it does not. The type of object that's broken in Power BI has no bearing on the complexity of what needs to be fixed. The why that object is broken is what determines the complexity. Converse example: If a View is broken because an object it depends on multiple layers deep is missing a column that its parent object depends on, even if the View in question doesn't depend on that column, that's much more pain in the ass to debug than a Stored Procedure referencing a Table that breaks because that Table had a column removed, where that column doesn't change anything logically about the procedure.
Aside from that just generally having context - views take longer to load in power bi as they need to compile server side, tables are faster
Also not true. Views are compiled the first time they're executed, which can (and usually does due to testing) happen outside of Power BI. If you mean because of the time to compile an execution plan for the View, that also typically is cached after the first run, so would have no bearing on the load time into Power BI. Tables also have an execution plan (albeit typically much simpler) that needs to be compiled too. All of this is pretty irrelevant to Power BI and the only thing that matters in this regard is the complexity of that execution plan due to the code in the View. That's the only differentiator that could affect load time performance of the View in a negative way. The other main factor is the size of data being loaded of course, which is irrelevant if you're a Table, View, Stored Procedure, Excel file, etc.
Finally, you're not going to be performance tuning your data objects inside of Power BI anyway. The first step would be to go into SSMS so you can work on that data object, which again, is the tool that organizes your objects by type and tells you what type they are via tooltips. So again, knowing the type of object in Powder BI serves me no purpose for this objective.
Not rocket science really
Agreed, it's really not when you understand all of the above. 🤷♂️
1
u/kiwi_bob_1234 19d ago
I think you're arguing against a point I wasn't really making. I'm not saying vw_ always tells us how complex a fix will be, or that views are inherently slower than tables.
I'm giving examples of why knowing the object type is useful context. In our environment specifically, views generally are a simpler layer with fewer moving parts, and we also have plenty of inherited views that perform worse than the equivalent tables. So if I'm triaging an issue, knowing it's a view gives me a useful starting point. It's not saying that's definitely where the problem is.
And separately, when I'm working in with SQL objects outside of ssms (refd in a factory codebase), vw_ lets me immediately see what type of object I'm looking at and search specifically for views. That's useful in a large codebase.
I'm not saying vw_ is essential. I'm saying it has practical value in our particular environment and comes with little cost
0
u/PaulPhxAz 1 21d ago
Is this ever an issue? Is this a vibe code thing?
1
u/kiwi_bob_1234 21d ago
I am regularly searching across our git repo across thousands of tables, view, proc definitions - being able to quickly identify a view (with vw) versus a table, or a proc (with usp) is immensely helpful.
I'm also talking about references from data factory btw, if a pipeline is referencing raw.sales_order is that a table or view?
0
20d ago
[deleted]
1
u/kiwi_bob_1234 20d ago
Because we have views and tables that do similar things/are named similarly but contain different data and I don't want to get them mixed up? Come on ffs
4
u/Hel_OWeen 21d ago
This is true as long as you can use SSMS or SQL Server. If for some reason you look at e.g. plain text files (someone might have dumped something with e.g. PowerShell), it's more convenient to search for "sp_" (or something similar you used consistently).
3
u/Reasonable-Job4205 22d ago
So no benefit at all to prefixing, even when working across different programming languages/environments?
10
u/xobeme 22d ago
I disagree...I believe it has always made reading code written by others easier. I manage a vendor built EMR application - they use all those conventions: tables are tblTableName and ctblCodeTableName; I think it helps a great deal.
2
u/Reasonable-Job4205 22d ago
Yea I think its kinda helpful too, but I actually wanted to check to see what the current trends are to see if I could potentially drop this from my routine and save some time
2
0
u/jshine13371 6 21d ago
SSMS tells you what type of object it is in a tooltip when you hover over it. The prefix naming convention is an unnecessary redundancy.
5
u/alinroc 4 22d ago edited 22d ago
We have strongly-typed languages and development environments that understand the code and can flag errors in real time. Even in less-strongly typed languages, the tools can handle it.
We shouldn't use outdated conventions for System A just because System B doesn't have anything better.
1
u/Reasonable-Job4205 22d ago
Fair enough.
So do you follow any sort of rules at all for naming your objects?
3
u/bobchin_c 22d ago
We name our stored procs as usp_<SProcName>
1
u/l2protoss 22d ago
It’s still crazy to me that naming a sproc with “sp_” has all the weird system stored proc “features” with the master db lookup.
1
u/Think-Trouble623 21d ago
I’ve read that in modern databases it’s basically not an issue. It used to consume extra resources but now it’s negligible. I still follow the pattern of not naming procs with sp_ but it doesn’t impact performance if you do.
3
u/JTBub 22d ago
Prefer everything prefixed except field names. Nothing more fun than getting an error in a ticket theres an error in "CalcSum" and figuring out if it's front end or back end and if it's a table, synonym, function, sp etc. Other people will be called on to fix your code. Be kind to them.
1
u/Forward_Dark_7305 22d ago
IME you’d have logging on backend, and front end wouldn’t display a name straight out of the DB, so this it naming conventions really shouldn’t be a tool for finding where a problem is - that’s what stack traces are for.
3
u/DavidKleeGeek Microsoft MVP 22d ago
I'd say it's more personal preference than anything at this point, as long as it's not specifically prefixed "sp_". SQL Server will assume the procedure is in the master database, and will first look there before looking in your specific database. IMHO it's more preference for you, your development and management style, and any historical precedence set by anyone working on your database(s) before you.
But - the comment below about using schemas - use them for logical grouping. It does make sense!
8
u/rhbcub 22d ago
I don't.
Use schemas for logical separation.
If you can't recognize a stored procedure in context, step away from the keyboard :D
2
0
u/Zzyzxx_ 21d ago
If you need logical separation, use a different database. Schemas are security boundaries, used to apply permissions at a grouped level.
0
u/kiwi_bob_1234 21d ago
What so rather than append three letters to your stored procedures, store them in another database!? That's madness surely
5
u/woo545 22d ago
usp_ for sprocs. vw_ for views ufn_ for functions. Nothing for tables
2
u/RecognitionOwn4214 22d ago
Why usp, though?
1
u/woo545 22d ago
User Stored procedure. Back on SQL 6.5, I believe someone read that there was a penalty for using sp_ , because they were used by system sprocs. I really don't remember the details this far along, but that pattern has been reinforced within the company.
5
u/ITDad 22d ago
I used to use sp_ until I learned it causes it to look to master db first then active db. This could cause it to use the wrong stored procedure if there is one of the same name in master. I find using some prefix such as usp, ufn, and v helps when reading the code to know where the data source is. Tables and columns are not prefixed.
1
2
u/jwk6 22d ago
usp_ for user stored procedures (avoid sp_ because only system sprocs should use it) vw_ for views udf_ for user defined functions pk_ and ix_ for indexes
There are a few others.
Also prefer prefixing with the entity name and following that with the purpose or function. I.e. usp_SalesOrderLineItem_Merge so that object names sort nicely.
Otherwise I despise underscores, and prefer everything else in PascalCase /TitleCase.
I think you do this purely for the clarity and readability of your T-SQL code these days, but I also get that it may not considered "modern" to some people. Or considered Department of Redundancy Department™️ 🤣
But please for the love of E. F. Codd, never tibble your tables!! (with tbl or tbl_ ☠️)
2
u/dadepretto 22d ago
I don’t use Hungarian Notation. I always use schemas, quote all names, and use semicolon at the end of a statement.
execute [App].[Order_Create] …;
select [Id], [CustomerId], [Date]
from [Data].[Order];
declare @stmt = ‘…’;
execute [sys].[sp_executesql] @stmt = @stmt;
2
u/AtmosfericGothic 21d ago
"sp" is used for system stored procedures.
We use "usp_" as user stored procedures, "vw_" for views, and no prefix for tables.
We also use prefix-style naming to group objects inside schemas. For example, in the cube schema:
cube.vw_fact_sale
cube.vw_fact_payment
cube.vw_dim_store
cube.vw_dim_item
And the words in object names should go from more general to more specific.
rep.agent
rep.agent_store
rep.agent_store_calendar
This way related objects are grouped together naturally in the SSMS object list.
I call it poor man's OOP. :)
1
u/dzemperzapedra 16d ago
So many fiercely defended opinions on here, but I'm just glad I found someone else doing the same exact thing as me.
Also, using Azure Data Studio or VS Code, you can group by schema in object explorer - just perfect 👌
2
u/CanProfessional766 21d ago
I don’t bother with sp prefixes. SSMS already tells you it’s a stored procedure, and names like GetCustomerOrders are just easier to read. That said if your team already uses prefixes consistently, I’d stick with the convention
2
u/jay_butler 20d ago
Access naming conventions are awful. I don’t prefix anything. I generally name stored proc in the verb-noun convention. So, things like getCustomer or createOrder.
1
u/luckyscholarynte 21d ago
I stopped prefixing most objects years ago. If the naming is clear enough that I can tell what something does without sp, tbl, etc., the prefix usually just feels like extra noise.
1
u/kapilverma123 21d ago
I still use prefixes to some extent, mainly because they make it easier to identify object types at a glance in a large database. That said, I avoid sp_ specifically for stored procedures since SQL Server reserves that convention for system procedures. In an existing project, though, I’d prioritize consistency over changing everything.
1
u/Riga_M0rt1z 21d ago
for tables, its almost a must, when joins have tables with similar / same names, you want to know where table they come from, so a prefix helps audit your ELT processes
1
u/PaulPhxAz 1 21d ago
No prefix, no need. Domain_TopicOrTable_Action
Like Accounting_Reconciliation_EndOfDay
Never put your verb first, or you'll see everything related to "Get" or "Update" next to each other, which is less helpful than the domain and topic.
1
u/codykonior 18d ago
Indexes I prefix. Procedures and tables, and columns, no.
I have been known to chuck a cheeky "Name" suffix on shit though. User? UserName. Computer? ComputerName! That's just how I roll.
1
u/ithinkilikerunning 21d ago
We prefix stored procedures by what they do. SEL_ for basic stored procedures that return data. DEL_ for deletes. INS_ for inserts. UPD_ for updates.
21
u/oldmanmagic54 22d ago
I prefix Views and Stores Procedures so that when I’m hunting for something by name I at least know which broad category to look in