Yahoo Web Search

Search results

1. • Execute queries

     • The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views. The data dictionary is used by SQL server to execute queries and is automatically updated whenever objects are added, removed, or changed within the database.
     www.codeproject.com › Articles › 5164696

     What is a SQL Server Data Dictionary? - CodeProject

2. Top results related to what are the function of dictionary in sql

   • What are the pros and cons of performing calculations in sql vs. in your application

     Top Answer

     Answered Sep 22, 2011 · 231 votes

     It depends on a lot of factors - but most crucially:

     • complexity of calculations (prefer doing complex crunching on an app-server, since that scales out; rather than a db server, which scales up)
     • volume of data (if you need to access/aggregate a lot of data, doing it at the db server will save bandwidth, and disk io if the aggregates can be done inside indexes)
     • convenience (sql is not the best language for complex work - especially not great for procedural work, but very good for set-based work; lousy error-handling, though)

     As always, if you do bring the data back to the app-server, minimising the columns and rows will be to your advantage. Making sure the query is tuned and appropriately indexed will help either scenario.

     Re your note:

     and then loop through the records

     Looping through records is almost always the wrong thing to do in sql - writing a set-based operation is preferred.

     As a general rule, I prefer to keep the database's job to a minimum "store this data, fetch this data" - however, there are always examples of scenarios where an elegant query at the server can save a lot of bandwidth.

     Also consider: if this is computationally expensive, can it be cached somewhere?

     If you want an accurate "which is better"; code it both ways and compare it (noting that a first draft of either is likely not 100% tuned). But factor in typical usage to that: if, in reality, it is being called 5 times (separately) at once, then simulate that: don't compare just a single "1 of these vs 1 of those".

     Other Answers

     Answered May 14, 2023 · 104 votes

     As far as PostgreSQL is concerned, you can do much on the server, quite efficiently. An RDBMS naturally excels at sorting, aggregating, casting, and formatting data to begin with. (I would claim that Postgres does a particularly great job.) The RDBMS can work with original data types from original storage. After processing, typically (much) less data has to be transferred. Clients typically need (much) more data than is used / displayed eventually to perform the mentioned operations. They typically talk to the server using the "text" protocol, casting back and forth adds cost. Much work has to be duplicated. It's not a level playing field.

     For procedural needs you can choose from a variety of server-side script languages: tcl, python, perl and many more. Mostly, PL/pgSQL is used, though.

     Let me use a metaphor: if you want to buy a golden necklace in Paris, the goldsmith could sit in Cape Town or Paris, that is a matter of skill and taste. But you would never ship tons of gold ore from South Africa to France for that. The ore is processed at the mining site (or at least in the general area), only the gold gets shipped. The same should be true for apps and databases.

     Worst case scenario would be to repeatedly go to the server for every single row of a larger set. (That would be like shipping one ton of ore a time.)

     Second in line, if you send a cascade of queries, each depending on the one before, while all of it could be done in one query or procedure on the server. (That's like shipping the gold, and each of the jewels with a separate ship, sequentially.)

     Going back and forth between app and server is relatively expensive. For server and client. Try to cut down on that, and you will win - ergo: use server side procedures and / or sophisticated SQL where necessary.

     We just finished a project where we packed almost all complex queries into Postgres functions. The app hands over parameters and gets the datasets it needs. Fast, clean, simple (for the app developer), I/O reduced to a minimum ... a shiny necklace with a low carbon footprint.

     Other Answers

     Answered Sep 22, 2011 · 26 votes

     In this case you are probably slightly better off doing the calculation in SQL as the database engine is likely to have a more efficient decimal arithmetic routines than Java.

     Generally though for row level calculations there is not much difference.

     Where it does make a difference is:

     • Aggregate calculations like SUM(), AVG(),MIN(), MAX() here the database engine will be an order of magnitude faster than a Java implementation.
     • Anywhere the calculation is used to filter rows. Filtering at the DB is much more efficient than reading a row and then discarding it.

     Check all answers on Stack Overflow

     1/5

   • What are the benefits of named instances in SQL Server?

     Top Answer

     Answered Mar 07, 2014 · 2 votes

     I can see two possible benefits, performance and ability to control your configuration.

     LocalDB is limited the same way as other SQL Express instances, CPU and RAM limits are applied per-instance. If your application uses its own instance it will get it all for itself and won't need to share with other applications. Using default instance means your application has to share with other apps using default instance.

     You might also use named instance if you need to make any special instance-wide configuration that might break other applications. Or if you're concerned that some other misbehaving application might make changes to the default instance that break your application, and have your customers blame you. Works both ways :-)

     Check all answers on Stack Overflow

     2/5

   • What are the best examples of natural keys in SQL?

     Top Answer

     Answered Nov 08, 2017 · 5 votes

     Invoice numbers, vehicle registration numbers, scheduled flight codes, login names, email addresses, employee numbers, room numbers, UPC codes. There are also many thousands of industry, public and international standards for everything from currencies, languages, financial instruments, chemical compounds and medical diagnoses. All of these are potentially good candidates for key attributes. Some sensible criteria for choosing and designing keys are: Simplicity, Stability and Familiarity (i.e. familiar within the business or other context in which they are used).

     Some people seem to struggle with the choice of "natural" key attributes because they hypothesize situations where a particular key might not be unique in some given population. This misses the point. The point of a key is to impose a business rule that attributes must and will be unique for the population of data within a particular table at any given point in time. The table always represents data in a particular and hopefully well-understood context (the "business domain" AKA "domain of discourse"). It is the intention/requirement to apply a uniqueness constraint within that domain that matters.

     For example, if my website requires each user to supply a unique email address when they register then email address may be a valid choice of key in the database supporting that website. The fact that there are other populations of people in other domains where email addresses are not required to be unique does not necessarily invalidate that choice of key for my website.

     Check all answers on Stack Overflow

     3/5

   • What are the drawbacks of using linked servers in SQL Server?

     Top Answer

     Answered Jun 21, 2011 · 14 votes

     • Server migrations are more convoluted
     • Security can be tricky to set up for multi-hop
     • Non-SQL Server ones requires a local driver installed (Sybase, DB2 etc)
     • Clusters, off-site DR: registry entries + drivers
     • Non-SQL Server x64 woes. 'Nuff said
     • Non-SQL Server ones don't play well (how many places to enter the password?)
     • Performance (in other answers)

     I've set up linked servers to Access, DB2, Oracle, Sybase and the odd proprietary ODBC driver. I'd prefer SSIS or .net code now...

     Check all answers on Stack Overflow

     4/5

   • What are the different ways of handling 'Enumerations' in SQL server?

     Top Answer

     Answered Jul 29, 2009 · 3 votes

     This might be controversial: my take is don't use enumerations in T-SQL. T-SQL isn't really designed in a way that makes enums useful, the way they are in other languages. To me, in T_SQL, they just add effort and complexity without the benefit seen elsewhere.

     Check all answers on Stack Overflow

     5/5

   Show moreShow less

3. People also ask

   What is SQL Server data dictionary?

   • The SQL Server data dictionary stores information about a database’s definition. The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views. The SQL Server DBMS uses the data dictionary to execute queries. The data dictionary is kept up-to-date as the database objects are changed.

   What is a SQL Server Data Dictionary? - Essential SQL

   www.essentialsql.com/what-is-a-data-dictionary/

   See all results for this question

   Why is a data dictionary important in a DBMS?

   • The data dictionary is an important part of the relational database because it provides additional information about the relationship between several tables in the database. A data dictionary in a DBMS helps users manage data in an orderly and orderly manner, thereby preventing data redundancy.

   What is Data Dictionary? - GeeksforGeeks

   www.geeksforgeeks.org/what-is-data-dictionary/

   See all results for this question

   What is a data dictionary in a database?

   • In a database management system (DBMS), a data dictionary can be defined as a component that stores a collection of names, definitions, and attributes for data elements used in the database. The database stores metadata, that is, information about the database.

   What is Data Dictionary? - GeeksforGeeks

   www.geeksforgeeks.org/what-is-data-dictionary/

   See all results for this question

   Why do you need a data dictionary?

   • Using a data dictionary helps define the naming convention used in the model. There are basically two types of data dictionaries in a database management system: Every relational database has an Integrated Data Dictionary available in the DBMS.

   What is Data Dictionary? - GeeksforGeeks

   www.geeksforgeeks.org/what-is-data-dictionary/

   See all results for this question

4. www.sqlshack.com › sql-server-data-dictionary-wantWhat is a SQL Server Data Dictionary and why would I want to ...

   www.sqlshack.com › sql-server-data-dictionary-want

   • Cached

   Dec 26, 2016 · What exactly is a data dictionary? A simple data dictionary is a repository of information about the database, usually in a database itself, that stores object information and attributes, versioned over time. A data dictionary can contain the following information about a particular object: Datetime the current object was created or changed

5. www.red-gate.com › simple-talk › databasesBuilding a SQL Server data dictionary - Simple Talk

   www.red-gate.com › simple-talk › databases

   • Cached

   Apr 1, 2021 · A data dictionary is a documentation tool that provides metadata, metrics, or details about a database and the data within it. Documenting data is critical to maintaining, sharing, and using it, and therefore this is a tool that can save time, improve code quality, and improve communication.

6. www.geeksforgeeks.org › what-is-data-dictionaryWhat is Data Dictionary? - GeeksforGeeks

   www.geeksforgeeks.org › what-is-data-dictionary

   • Cached

   Apr 24, 2024 · A data dictionary in a SQL database is a data store that contains information about the structure of the database. such as tables, columns, indexes, constraints, and views. A data dictionary is needed to manage data quality, ensure data consistency, and facilitate data sharing.

7. Videos

   • 

     4:39

     youtube.com

     How to generate MySQL data dictionary from SQL script

     May 19, 2021

     7K Views

   • 

     35:56

     youtube.com

     SQL Functions with Examples | SQL Functions | SQL Clause | Intellipaat

     May 28, 2021

     80.5K Views

   • 

     sqlshack.com

     What is a SQL Server Data Dictionary and why would I want to create one?

     Dec 31, 2008

     168 Views

   View all

8. www.essentialsql.com › what-is-a-data-dictionaryWhat is a SQL Server Data Dictionary? - Essential SQL

   www.essentialsql.com › what-is-a-data-dictionary

   • Cached

   Nov 1, 2021 · The SQL Server data dictionary stores information about a database’s definition. The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views. The SQL Server DBMS uses the data dictionary to execute queries. The data dictionary is kept up-to-date as the database objects are changed.

9. axial-sql.com › understanding-the-sql-server-dataUnderstanding the SQL Server Data Dictionary and How to Use It

   axial-sql.com › understanding-the-sql-server-data

   • Cached

   Feb 27, 2022 · What is a SQL Server Data Dictionary? A data dictionary is an essential part of any database system. It contains meta-data, which is data about data. Specifically, in SQL Server, the data dictionary is a read-only set of tables that provides information about the database’s objects such as tables, views, and procedures.

10. www.codeproject.com › Articles › 5164696What is a SQL Server Data Dictionary? - CodeProject

    www.codeproject.com › Articles › 5164696

    • Cached

    Aug 14, 2019 · In SQL Server, the data dictionary is a set of database tables used to store information about a database’s definition.& The dictionary contains information about database objects such as tables, indexes, columns, datatypes, and views.

11. www.mssqltips.com › sqlservertip › 1619Build a SQL Server Data Dictionary with the Extended Properties

    www.mssqltips.com › sqlservertip › 1619

    • Cached

    A data dictionary is a collection of descriptions about the data and objects in a data model for the benefit of programmers and users who need to refer to them when building the application, reports, etc. It is ideal to create a data dictionary while working on the data model.