yes, that's true
got good result by reconstructing database, eliminating unnecessary open connections, creating missing index, converting scan to seek, minimizing scan re-looping, distinct elimination / avoidant etc.
I have 5 tables in my db, I used one form to saved into 4 of them at once. I want the 5th one to be used as a master where I can store the foreign key and be used to display to a table in the frontend.
How do I make all of them add one ID?
How can I display a table row as a column in the frontend?
But typically an identity column. The table would have something like the following which is for SQL Server. Other databases all have something similar. Mongodb uses a 'object id' (typically.)
something_id INT IDENTITY(1,1) PRIMARY KEY,
That is how you create the column. But populating it in the other tables is more complicated.
1. You must first insert into the main table to create the id.
2. You must then get that id. That almost always involves using a specific form of the insert (first part) which returns the id at the same time as the insert occurs.
3. You then use that return value to create (insert) into the other tables. Obviously you will need to add a column to hold that new value.
The only possible answer to that question is "it depends". There is no single "best" option; that's why there are different options available. Each option has both benefits and drawbacks, depending on your specific requirements.
For example, if you use SQL Authentication, then you have to store the SQL credentials somewhere on each client machine that needs to access the database. That runs the risk that a technically-minded user could find the credentials and connect directly to your database, bypassing the restrictions implemented in your code. You would have to deliberately design your database to restrict what the SQL user could do in order to mitigate this.
On the other hand, if you're using a web application / api to access your database, then that will typically run as a highly restricted local user on the web server. Setting that up to use Windows authentication is more effort than using SQL authentication. And since all requests would effectively be running as the same local user, you wouldn't be able to use the authenticated user to restrict access to the data.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
There is no such thing as "the best option". There is only what is the most appropriate given the requirements for the application, the application type, and the environment it's running in, which we know nothing about.
There are far more options than just the two you listed.
I am going to guess you have an application. Users (plural) use the application not the database.
The application, not the users, use the database. So there is only one user which is only visible to the application. The reason for this is because attempting to manage users both at the database and application level rapidly becomes a problem and provides no benefit.
The application itself, should provide a mechanism to validate each user. Then you can do things like log actions, in the application, for each user in the database (a table for that.) The application will use the database to implement this but it does not have anything to do with database users.
I have order header and detail tables with UPC codes and the order numbers they belong to. There can be many different UPC codes for each order. The Order IDs are in the header table and the UPC codes are in the detail table.
I must find UPC codes that have been used for more than one order, in other words, where the same UPC code exists for two or more distinct orders.
I have tried:
SELECT OD.UPC, OH.OrderId, COUNT(DISTINCT OH.OrderId) [COUNT]
FROM OrderDetail OD
LEFT JOIN OrderHeader OH ON OH.OrderHeaderId = OD.OrderHeaderId
GROUP BY OD.UPC, OH.OrderId
HAVING COUNT(DISTINCT OH.OrderId) > 1
But this query returns nothing and I'm not sure if it's because there are no duplicate orders or because the query is wrong.
Any help will be tremendously appreciated. Thank you.
The difficult we do right away...
...the impossible takes slightly longer.
Why don't they only provide a snapshot of the website theme to prevent any illegal copy?