Tag Info

New answers tagged

1

I won't be able to answer all your questions, but for what it's worth: SQL modules seem to be a part of the SQL/PSM 2008 standard. At least they appear in the draft version, that can be found on the interwebs. Tools will eventually catch up with the server features. Meanwhile, you can use the Oracle-compatible PL/SQL dialect, currently supported by tools, ...


0

Your idea isn't that crazy. Ordering the primary key DOES reduce fragmentation, but this can be achieved with an IDENTITY column. However, ordered keys also has a drawback, namely the one described here: http://kejser.org/clustered-indexes-vs-heaps. Now, I can see how you would benefit from knowing WHEN the key was generated and that you therefore want pack ...


0

My preference is to use an identity column for primary key and if required add a unique index on other fields if you need to enforce uniqueness. In my opinion, a primary keys job is to uniquely identify a single row of data. Integers do this more efficiently without risk of ambiguity. I dislike using varchars as pkeys or in joins as they can be affected by ...


1

Hmm, I agree with you, almost, and not with the folks who suggest using an arbitrary integer to identify your rows. Your primary key should be tid char(2), asof datetime, sequence smallint where you control the sequence. That's a perfectly fine primary key because it identifies the row. The burden is on the surrogate crowd to explain the ...


4

Sure you CAN do it, but why would you? To save a few CAST expressions? That seems a bit weak. The reasons to have auto incrementing surrogate int PK's are many: You pretty much never have to manage them with normal databases. Ever. Unless you are rebuilding tables or inserting data where you have to turn the identity off and on. But operationally it works ...


2

This would most definitely not be preferred to an auto-increment integer field. For starters: Index width would be degraded significantly with your proposal. Just how do you propose to manage all those random 2-digit integers to enforce uniqueness. Have you thought of how much code would have to be written and maintained to implement this scheme. Won't ...


1

Use always the smallest data types that fits the values it will hold. 'Fit' is a flexible term, depending on thing like: Can it hold all current values of the table? Can it hold future values of the table? Is the data in a normal form? Can I create duplicate values? (think string vs. integer id that is a foreign key to an external table) Can I insert ...


0

On one hand, a sensible naming convention can solve part of your problem. However, a more sophisticated solution is to use schemas: they are a good way to organize your stuff and also make you able to set access rights at this level. For example, we usually create a data schema (named after the covered functionality and suffixed with '_data') and one or ...


2

I would definitely err on the side of keeping the tables in the same database. It makes it easier to maintain referential integrity (unless you hard delete records in which case foreign keys won't work for you anyway) and create consistent backups (simply backup one database and you know it is in a consistent state, instead of taking two+ backups that my be ...


-1

I would do this in the same database because the data belongs together. To create a trigger for this use, it is not practical to insert records to another database. Refer to the following answer on how to design the auditing table. http://stackoverflow.com/questions/7975317/audit-tables-each-field-for-table-or-one-table


0

No there are no specific bad things about using temp tables and temp procedures. For the temp tables you'll want to make sure that you've got indexes as needed when querying from them, but that applies to normal tables as well.


1

I doubt that the example you provide is a valid use case for temporary procedures (I don't see any benefit here to using #temp procedures over permanent procedures), but for #temp tables, which have a much wider set of use cases, the only way to fight these arguments with policy-setters is to run the code - using a full load and during typical workload ...


0

You have a complex environment with SSIS and SSAS so you have to be cautious with max server memory setting for SQL Server in your scenario Jonathans blogs would not provide complete help. You should refer to below Microsoft link for determining memory requirement for SSIS http://technet.microsoft.com/library/Cc966529#EEAA You should leave enough memory for ...


1

You should always set your max memory away from default and leave some room for OS (see Jonathan's post of how much Memory to leave based on the amount of RAM installed). Jonathan Kehayias has blogged about : How much memory does my SQL Server actually need? reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB ...



Top 50 recent answers are included