Influence of index rebuild tasks in tempdb on the transaction log?

Both offline and online reorg/rebuild operations consume log space.  This is true for ALL operations that modify a database in any way, even for minimally logged operations such as TRUNCATE TABLE.  This is how SQL Server maintains transactional consistency.  "Consistency" is impossible unless every action is logged so it can be rolled back or forward if necessary.

Index reorganization uses less log space than an index rebuild.  Reorganization defragments the index page-by-page, whereas a rebuild creates an entirely new copy of the index being rebuilt, then drops the old copy of the index.  If an individual index is 1GB, rebuilding it will require at least 1GB, plus space for any rollback to take place that may happen as a result of the rebuild either failing or being cancelled.  Since any rollback operation will also be logged, log space is reserved prior to the start of all transactions to ensure the transaction can be rolled back successfully.  This indicates the maximum potential log space required for an index rebuild would be double the size of the index at the time the rebuild operation begins.  So for a 1GB index, you should ensure you can support at least 2GB of log space in use by the rebuild operation for the duration of the operation.  This is not a guarantee that much space will be used, it is simply the maximum the operation might need.  Any other simultaneous transactions occurring will need additional space.

Any operations that consume tempdb also consume tempdb log space.  If you enable SORT_IN_TEMPDB, you need enough room in tempdb log to support sorting the entire index; this amount depends greatly on the structure of the index, including things like how large the key columns are.

If index reorg/rebuild operations are performed serially, that is one-after-another, then you'll need space in the transaction log for the largest index size x 2.  i.e. if your largest index is 1GB, to be safe you need to ensure 2GB of transaction log is available for your index operation.  If you have simple logging in effect at the time of the reorg/rebuild job, and all operations aren't in a single transaction, transaction log space will be re-used by each individual operation.  If you have full transaction logging enabled, and no transaction log backups occur during the reorg/rebuild job, then the log needs to be size at the total size of all indexes to be rebuilt + the size of the largest index to be rebuilt.  This can be mitigated somewhat by running log backup jobs during the rebuild process.


How Can i Update only one Column of SQL Table via store procedure with passing rest column value null

You can do a COALESCE between the passed in parameters and the existing values.

When the passed in value is NULL, the query will retain the existing values.

This will, however, make it difficult to update to NULL if you do actually require the Nulling of these values.

CREATE PROCEDURE [dbo].[spUpdateUser]
@ID int,
@UserID varchar(50) = null,
@Name varchar(50) = null,
@FatherName varchar(50) = null,
@DOB date = null
AS
BEGIN
update tblUser set
    UserID = COALESCE(@UserID, UserID),
    Name = COALESCE(@Name, Name),
    FatherName = COALESCE(@FatherName, FatherName),
    DOB = COALESCE(@DOB, DOB)
    where ID = @ID
END


Not sure where you guessed at that syntax, or why you are trying to put single quotes around a column definition (which makes it look like a string literal). Here is one way to make sure that the new column has a specific type:

SELECT ...other columns... ,
  [Status] = CONVERT(VARCHAR(100), ReportType)
INTO dbo.temp_AlgorithmLog
FROM dbo.AlgorithmLog_OLD;

The new column will be nullable regardless of the old column's nullability. If the old column is nullable and you don't want the new column to be nullable, first you need to ensure that there are no NULL values, and then you can alter the table after the fact:

ALTER TABLE dbo.temp_AlgorithmLog
  ALTER COLUMN [Status] VARCHAR(100) NOT NULL;


Least Privilege for SQL Agent to run DISKPART

You are getting deep into internals here

  Membership in the local Administrators group, or equivalent, is the
  minimum required to run DiskPart

And this SE thread points to the same.

Now usually you want to avoid giving to much permission to the service accounts and I'm not certain if giving the SQL Server agent permissions to mount/unmount volumes and at the same time delete them would be considered best practices.

You can probably program a system service that can do the diskpart magic and call that but this really ask the question why you would want to to this from SQL Server.


Best way to copy tables without a primary key or unique constraint?

AMtwo's point is without a primary key defined you have no reliable way to consolidate a record between a table on both servers. A primary key is the only way to guarantee row 137 is the same exact row 137 you're comparing it against between server 1 and server 2.
The closest thing you can attempt is use the HASHBYTES() function with a minimal collision hashing algorithm to hash the entire row, and then compare hashes. You can even goes as far as creating a computed column or an indexed view so you can index the hashed value and do quicker comparisons. The downside to this is with no primary key on the table means it's entirely possible two rows have exactly the same values across all columns within the same table and result in a duplicative join when comparing across servers resulting in excessive row comparisons.
The better solution from an accuracy standpoint would be to use a feature like Snapshot Replication on an appropriate schedule (e.g. hourly if that synchronization timeframe is acceptable but you can theoretically schedule it to run as soon as every second - though I wouldn't recommend this frequently). This essentially dumps the table on the target server and re-inserts all the rows from the source server, only for the tables you choose to replicate. You may want to even consider using a hybrid replication solution by implementing Transactional Replication for your tables that do have primary keys which offers near realtime synchronization.


Can i get a SQL Server catch Updates to reflect the responsible objectid with a database trigger?

if you want to know who wrote what and when,
I would create 2 users, one for each of the procedures involved, and then
create procedures with execute as user

add an extra column on your testtable to keep the session_user or current_user so that you know who inserted/updated each record since insert_procedure will be executed as insert_user and update_procedure will be executed as update_user.

this script can help you to find out the original login, and compares different ways of addressing the users.

DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(),
               CHARINDEX('\',
               SUSER_SNAME()) + 1,
               LEN(SUSER_SNAME()))

SELECT   @USER
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        ,USER
        ,SESSION_USER

EXECUTE AS LOGIN='my_user'

--DECLARE @User VARCHAR(20)
SELECT @USER = SUBSTRING(SUSER_SNAME(),
               CHARINDEX('\',
               SUSER_SNAME()) + 1,
               LEN(SUSER_SNAME()))

SELECT   @USER
        , SUSER_SNAME()
        ,SYSTEM_USER
        , USER_NAME()
        , CURRENT_USER
        , ORIGINAL_LOGIN()
        , USER
        ,SESSION_USER

REVERT


These are internal, background processes connecting Oracle RAC Database with Oracle Grid Infrastructure High Availability Services. You can't control them. The status of "INACTIVE" can be misleading; it doesn't mean they don't have a purpose or aren't actually doing anything. Any background or user session will show as "INACTIVE" unless it is actually performing an operation that takes long enough to be recognized from one monitoring cycle to the next, at the exact moment you happen to query gv$session.
What "connection pool" are you trying to grow? These background processes should not count against most application-level pools or limits.


Two facts, two dimensions, one query, one cube, can it be done and how?

In the Dimension Usage tab click the ... In the cell that is Fact Order and Dim Product. Switch that cell to a many-to-many relationship.

Then add a Sum measure on the FirstEverOrder column called First Ever Order Count. Also add a Count measure which is a count of rows called Order Count.

Now create a new calculated measure which is:

IIF([Measures].[Order Count]=0,Null,[Measures].[First Ever Order Count] / [Measures].[Order Count])

Now you should be able to build your report as you have shown. That's the power of many-to-many.


This may not exactly what you want, but it should be close enough.

grant read any table to username;

This provides read privileges on all user tables and views (but no privileges on dictionary tables or DBA_ catalog views).

If you want to this only for specific schemas, you need to grant the specific object privileges.


mysql - insert multiple values sharing same foreign key

insert into provinces (province_name, country_id)
SELECT t1.province_name, t2.country_id
FROM ( SELECT 'Alabama' province_name UNION SELECT 'California' UNION SELECT 'Alaska') t1
JOIN (select country_id from countries where iso_alpha3_code = 'USA') t2;


Mysql load from infile stuck waiting on hard drive

Your bulk insert buffer is 4G. That's great ... FOR MyISAM !!!

InnoDB does not use the bulk insert buffer.

You may need to have sqlalchemy throttle the load data infile calls into multiple transactions.

You may also want to disable innodb_change_buffering, setting it to inserts.
 Unfortunately, you cannot do SET GLOBAL innodb_change_buffering = 'inserts';. If you dom you may need to set it in my.cnf and restart mysql.

UPDATE 2012-07-13 16:53 EDT

I just noticed that you have two values in the my.cnf for innodb_buffer_pool_size. First one is 2385M, and the last one is 14G. If MySQL for Windows accepted 14G and you only have 12G of RAM, your server must be having a good old time swapping.

You can verify what the buffer pool size is with

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

UPDATE 2012-07-13 16:58 EDT

You may also want to check how full the buffer pool is with this

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;


SQL Stored Procedure with Output Parameter

Output parameters in stored procedures are useful for passing a value back to the calling T-SQL, which can then use that value for other things.

Let's say you have a stored procedure that returns a state given the input of a city, with state as the output parameter:

CREATE PROCEDURE [dbo].[GetStateFromCity] (@City NVARCHAR(30), @State NCHAR(2) OUTPUT)
AS
SELECT @State = [State]
FROM [ExampleTable]
WHERE [City] = @City
GO;

You can now use this output parameter to pass in a value elsewhere.

For example:

DECLARE @State int
EXEC [dbo].[GetStateFromCity] @City = 'Calgary', @State OUTPUT;

-- Do something with this value
SELECT @State;

-- Do something else
EXEC [dbo].[GetInsuranceCompanyByState] @State;

To summarise, if you just want to return a value for a client application, you probably don't need an output parameter.

However, if you want to pass values around in T-SQL between stored procedures, they can be very useful.

For what it's worth, I hardly use output parameters.


SQLSTATE[HY000]: General error: 1025 Error on rename of

Without knowing your DDL, I did find this on the docs regarding error code 1025, in one of the user comments:

  To avoid getting this error while trying to drop a foreign key, use the constraint name rather than the column name of the foreign key.

So, instead of dropping the ghost.row_id column, try dropping the foreign key using the FK name.


Is there a way to get index fragmentation without using dm_db_index_physical_stats?

I'll just resume the great suggestions that were made in the comments section :
The short answer is No, you have to use the DMV.

One possible alternative to using the DMV on the whole database would be to do it by table, but I need to do it for the whole database, and I already split index maintenance on multiple days so that it fits as much as possible in the maintenance window.

Another alternative would be to restore the database on another server and gather the index fragmentation over there.

I can also try to see if there's some unused indexes and see if I can remove some. I've found this query very use full, if it can serve someone else.
If you see indexes with no reads and only writes, it means that the index is only used when you insert rows in the table, but never in the SELECT queries. You can then analyse and decide if it's usefull and should be kept.

SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups]
            AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        ddios.[leaf_insert_count] ,
        ddios.[leaf_delete_count] ,
        ddios.[leaf_update_count] ,
        ddios.[nonleaf_insert_count] ,
        ddios.[nonleaf_delete_count] ,
        ddios.[nonleaf_update_count]
FROM    sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id] AND i.[index_id] = ddius.[index_id]
INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id] AND SP.[index_id] = ddius.[index_id]
INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
INNER JOIN sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) AS ddios
  ON ddius.[index_id] = ddios.[index_id]
  AND ddius.[object_id] = ddios.[object_id]
  AND SP.[partition_number] = ddios.[partition_number]
  AND ddius.[database_id] = ddios.[database_id]
WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
      AND ddius.[index_id] > 0
      AND ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
ORDER BY ddius.[user_updates] DESC ,
        su.[name] ,
        o.[name] ,
        i.[name ]


Activity Monitor % Processor Time grayed out on SQL Server

I know it is not a solution but we had a programmed restart and after that everything works fine, it is a production server so we couldn't restart before but that solved it.


WITH RPL (PART, SUBPART, QUANTITY) AS
           (
              SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
                 FROM PARTLIST ROOT, PARTTYPE
                 WHERE ROOT.SUBPART = PARTTYPE.PART
             UNION ALL
              SELECT PARENT.PART, CHILD.SUBPART,
                     PARENT.QUANTITY*CHILD.QUANTITY
                 FROM RPL PARENT, PARTLIST CHILD
                 WHERE PARENT.SUBPART = CHILD.PART
           )
SELECT PART, SUBPART, QUANTITY AS "QTY"
   FROM RPL
   ORDER BY PART, SUBPART
/*
UNION ALL
SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
LEFT JOIN PARTTYPE ON ROOT.SUBPART = PARTTYPE.PART
WHERE PARTTYPE.PART IS NULL
*/
;


You can use FULL JOIN like this:

SELECT
    COALESCE(a1.t, a2.t) as t,
    a1."1489",
    a2."1616"
FROM
(
    select
        date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp) as t,
        COALESCE(count(*), 0) as "1489"
    from actions
    where group_id = 1489
        and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
    group by 1
) as a1
FULL JOIN
(
    select
        date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp) as t,
        COALESCE(count(*), 0) as "1616"
    from actions
    where group_id = 1616
        and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
    group by 1
) as a2 ON (a1.t=a2.t)
ORDER BY 1;

Or just use CASE:

SELECT
    date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp),
    SUM(CASE WHEN group_id=1489 THEN 1 ELSE 0 END) as "1489",
    SUM(CASE WHEN group_id=1616 THEN 1 ELSE 0 END) as "1616"
FROM actions
WHERE
    group_id in (1489, 1616)
    AND TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
GROUP BY 1
ORDER BY 1;


Ever heard of the windows registry? Put something like that into your database. A hierarchy of folders for key/value pairs. Simple to do with some changes. Do NOT serialize anything more complex than primitive types - that will come back to haunt you - or hand crafted XML structures (i.e. classes that output XML that is clear to read).


How to delete rows from the mid of the table without any where condition?

What you are asking for can lead to unpredictable results in certain situations.
What kind of situations?

Running this on a Master and the Slave receives the same SQL. The problem I see is that rows of data in a Master may not be physically written in the same order on the Slave.
Triggers will encounter issue (or really create issues) with this type of DELETE query

I am sure there are other situations, but these came to mind first.
I addressed these before

Feb 22, 2011 : Problem with MySQL subquery
Jul 30, 2012 : Warnings: Why are statements with limit clause unsafe even if there isn't a limit?

SUGGESTION
If you look back at the original question (Problem with MySQL subquery), the OP suggested getting the key for the row you want deleted. Then issue the delete using that key. It's the safest way.
YOUR ACTUAL QUESTION
Doing fancy DELETEs, even if possible, could produce unwantd results on Slave, on a Master, or even a Standalone DB Server when not using WHERE.
In the end, you must use WHERE for the safest and most expected results.


General error 1651 after a mysql migration

I think the SET SESSION query_cache_type = OFF is causing this. In the Query Cache Configuration page it states:

  If you set query_cache_type at server startup (rather than at runtime with a SET statement), only the numeric values are permitted.

And since you are starting the server with cache disabled, there is no need to use that SET anyway.

As to why it throws an error (both with = OFF and = 0), it may have to do with this note in Server System Variables page:

  query_cache_type

  ...

  If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.

My interpretation is that the variable has both Global and Session scope but only if it not set to off at startup.


How to support custom fields for an entity and list them as columns?

You have two options: either you loop results from PHP, or you store all the custom fields and values in one single JSON datatype field (stored as a json object, requires MySQL 5.7) in the users table.


Server Principal SID showing as NULL and name as empty string

I created the exact same audit and couldn't reproduce the problem you describe. When I log in with a login that doesn't actually exist:

And for an intentionally wrong password:

I even tried a name with a huge number of leading spaces and tabs, just to see if the name would get truncated and look blank, but leading whitespace gets trimmed.

Of course I would expect SID to be null in the first case, because what SID would you want to show for a login that doesn't exist (and therefore can't have a SID)? And for the second case, perhaps because a login couldn't authenticate, a match to SID wasn't made?

You'll have to provide more info to determine why server principal name isn't being populated. What is the application name? What does the statement say? What is the client (IP address in the statement)? Can you ask that person what they're doing?


How would you optimize this 2 milion records table or queries?

Please create the following index

create index domains_ix_status_date on domains (domain_whois_status,domain_whois_date);

We're traversing through the index where the rows are already ordered by the way we want them (ORDER BY domain_whois_status), we check the condition domain_whois_date > 0 (still within the index structure) and we retrieve only the relevant rows and output them.


What I don't understand is, doesn't this mean the data in the data
  pages on the disk is stale until a checkpoint?

On Disk - yes.

  Where does SQL Server read the data from when a select query is issued
  between "commit transaction" and the checkpoint flush? Thanks.

Memory. Let me explain a little more...

When a the update statement runs (say update mytable set columnA = 1 where columnA = 2) the change is logged and also made on the data page in memory. You've already said that the logging happens and is hardened (flushed to stable media) but the change is also made on the data page in memory. Thus, when the page is "read" by another transaction, the updated values are there.

There is something else that comes into play which are called latches. Locks keep logical consistency but latches keep physical consistency. The data page is latched, locks are taken, updates happen, logs are written and eventually flushed. The changes are always first made, in memory.

You might be thinking... "hey wait, if the changes are made to the data pages but logging isn't complete, the database is inconsistent!" That's close! The changes made to the data pages aren't reflected on disk yet - so if a crash were to happen, the data would be as it was pre-update which is how it should be. When the checkpoint occurs, WAL (write ahead logging) must still take place, so all LSNs up to the most recent reflected on data pages part of the checkpoint must first be flushed to disk. Thus the consistency is maintained and all is well in the world of SQL Server :)


It is the other way around, if you delete a row in a, the corresponding row in b will be deleted to. If a is a child table of b you should declare the foreign key in a.

Given your updated scenario, I guess an AFTER DELETE trigger for each table a,b,c,d,... is the best choice.

However, I would consider using 1 log-table per table together with an insert, update and delete trigger. Let the triggers do all the work manipulating the log-table, and let the application handle just the tables.


Running queries directly on DB2 databases

There are two major concerns here.  First you are using significant server resource (memory, CPU, disk) on a produciton box which, presumably, is busy.  This will be delaying other work.  In the extreme, your huge query may fully consume some shared resource and cause other work to fail.  Second, your query may be locking data others are trying to use.  There are ways around this which either compromise the integrity of your results or use further server resources.

Mitigations include

cut the job into smaller sub-jobs.  If you're processing data for a year, for example, submit 12 separate monthly jobs sequentially and combine these results afterwards.  Your elapsed time will be longer but your impact will be less.
run during slack times, after-hours or weekends.
copy the data to another server and run the job there.
ensure the SQL is efficient and the DB adequately indexed for this task.

If this work needs to be done, and it needs to be done on this box, then you don't have much choice.  Inform your management of the risks and good luck.


Maximum table capacity in SQL Server 2008

There is no practical limit except disk space. I read the table you linked to entirely and checked it.

If you need to go above 16TB you need multiple files (a simple procedure).


I am getting error while restoring FileGroup on SQl server 2012

The database engine thinks you're trying to perform a piecemeal restore using the UI.  This is the formal name of restoring individual filegroups.  Sadly, it requires you restore the entire database to maintain transactional consistency (which I don't think you want to do in your scenario) and since you're not overwriting the database (as identified by Pinal Dave's answer), you're getting this error.

If you're only trying to restore that one table, you've got a couple of options available, as follows:

You can restore the database as a different name and then copy the data from the restored copy into the working copy.
You can store that table in a different database and if you need to revert that table, restore the database it's contained within.  Of note, you are unable to maintain Referential Integrity between tables stored in different databases without some trickery (e.g. Triggers).  This may not be a problem now, but it could crop up issues in the future, so just be aware of this if you do separate out this table into a different database.


pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

This exact error message is in the FAQ.

You have md5 authentication configured in pg_hba.conf and trust authentication (meaning no authentication whatsoever) in pool_hba.conf.

You need to configure either trust authentication or md5 authentication in both files.


You can use handle.exe from SysInternals to see which process has a handle open to your file. But I wouldn't be too surprised if it indeed is the SQL Server process itself (as others have noted in the comments).

First make sure, that your procedure actually closes the file correctly, when being canceled.

You didn't show the code for the procedure, but it is not too far fetched to assume, that you don't close your file (stream) properly in the face of an exception - which is what your procedure will get from SQL Server when being interrupted by a user (more specifically a ThreadAbortException).

Note though, that even if you do handle this correctly, there are still possibilities (e.g. error handle takes "too long") for this cleanup code to fail or not execute to completion, hence leaving the file open.

You may want to check this blog entry for further information.


Is your Time dimension connected to a source or is it a Server time dimension which has no table for it in the Data source

because usually when you generate a time dimension using BIDs it gives you these two options

and it looks like in your case you dont have a table for it in the datasource, i mean you chose a server time dimension which creates a dimension in Analysis server itself.

so you wont be able to append or add more dates, you will need to recreate it from scratch.

but if you chose the other option "Generate a time table in the data source" This option creates a time dimension table in the underlying data source and populates the same with data based on the inputs provided.

you should have access to be able to create a table in your data source

in this case you can add records to the table and reprocess it.

here is an easy script to create and fill a time dimension table
and later you can generate your time dimension in the cube from this table

http://microsoft-ssis.blogspot.com/2011/01/create-and-fill-time-dimension.html

and here is 2 articles about generating a time dimension and its options

http://www.sqlservergeeks.com/blogs/AmitK/sql-server-bi/383/ssas-considerations-for-time-dimension-part1

http://www.sqlservergeeks.com/blogs/AmitK/sql-server-bi/385/sql-server-ssas-considerations-for-time-dimension-part-2


the Out-of-memory process (oom) killer terminated your mysql process because it was trying to use more memory than was available. Calculating memory usage in MySQL exactly is very difficult, but once you understand how MySQL allocates memory system-wide and to each connection, you can get a pretty good idea of how much memory is likely to be used. Keep in mind that a big challenge here is figuring out how many connections are open simultaneously and how much each one is doing, since the amount of time a connection hangs around waiting to be reused is settable with server system variables as well.

There are a few tools out there that can help you get a good overview of what the server memory allocation should look like and where potential problems may lie in your current configuration. So long as you're not 100% reliant on any one. mysqltuner.pl will give you a great starting point, and possibly get you close enough to resolve your issue completely. Percona's MySQL Configuration Wizard is very good at generating optimal initial configurations. Run the wizard, generate a configuration, install it, and see how mysqltuner.pl disagrees with it.

You will immediately see that you're miles ahead of where you started in terms of a good configuration, and that it's a complex enough topic that there are many different preferences, priorities, levels of expertise, and areas of focus in deciding what a good configuration looks like, but there's also some numbers that are unquestionably better than others.

As others have mentioned, the reality is that to get good performance you will need a server with more RAM, but setting it up will be easier with the Percona wizard as well as mysqltuner.pl.

Finally, here is a great introduction to what MySQL does with memory. It's from some MySQL documentation I saved years ago, but I could not find the original source just now:

    Mysql Server Memory Usage = Sum of Global Buffers +
        (number of Connection * Per thread memory variables).
    Global buffers include:
        key_buffer_size: key_buffer_size is the size of the buffer used
            for index blocks.
        innodb_buffer_pool_size: The size in bytes of the memory buffer
            InnoDB uses to cache data and indexes of its tables.
        innodb_additional_mem_pool_size: The size in bytes of a memory pool
            InnoDB uses to store data dictionary information and other
            internal data structures.
        innodb_log_buffer_size: The size in bytes of the buffer that InnoDB
            uses to write to the log files on disk.
        query_cache_limit: The amount of memory allocated for caching query
            results.
    Each thread for client connection uses:
        thread_stack – The stack size for each thread.
        net_buffer_length – connection buffer
        max_allowed_packet – up to this size net_buffer_length can grow
        read_buffer_size – used for sequential table scan
        rean_rnd_buffer_size – used for random read buffer / sorting
        tmp_table_size – temporary / hash tables in mysql
        sort_buffer_size – for sorting
    Per thread variables include:
        read_buffer_size: Buffer memory used for sequential table scan.
        read_rnd_buffer_size: Memory used for random read buffer / sorting.
        sort_buffer_size: Memory allocated for sorting, Group By, Order By.
        join_buffer_size: The size of the buffer that is used for plain index
            scans, range index scans, and joins that do not use indexes and
            thus perform full table scans.
        thread_stack: The stack size for each thread.
        net_buffer_length: Connection buffer
        max_allowed_packet: Up to this size net_buffer_length can grow.
    Note that,
        If size increases or if table have blob columns, instead of heap
            tables on-disk tables created.
        Memory for variables read_buffer_size, sort_buffer_size,
            read_rnd_buffer_size, tmp_table_size are allocated as & when
            required. They are also de-allocated once the task is
            accomplished.


How to parse query plans to get a count of nested loop operators with unordered prefetch?

This will Work Perfectly®

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
SELECT qp.plan_name,
       qp.query_xml.value('count(//p:RelOp/p:NestedLoops/@WithUnorderedPrefetch)', 'int') AS operator_count
FROM dbo.query_plans AS qp;

If you prefer, you could use

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

...and omit the namespace prefix p: from the XQuery expression.


CopyDatabaseWizard.exe issue on SQL 2014

As weird as it is, it currently appears I have resolved this issue by upgrading Powershell. On one server the powershell version was 3.0 and on the other three it was still 2.0.

You can check your powershell version installed by running:

$PSVersionTable.PSVersion

The upgrade package I used was Windows Management Framework 4.0, which includes Powershell 4.0. PS 4.0 is the last version supported for Server 2008R2.
Here's a download link to WMF 4.0:
http://www.microsoft.com/en-us/download/details.aspx?id=40855

Edit

Credit to Antoine Hernandez; Powershell 5.0 is supported on Windows Server 2008r2.
Download: https://www.microsoft.com/en-us/download/details.aspx?id=50395

Also quick update after a few days: I haven't had any instances of this issue since I updated Powershell to v4.0.


Mysql sequential numbering based on MAX of another column

STEP 01 : Create index on date_submitted

ALTER TABLE lp_pictures ADD INDEX (date_submitted);

STEP 02 : Create separate table to order the id values

CREATE TABLE lp_picture_ordering
(
    id INT NOT NULL AUTO_INCREMENT,
    plate_uid varchar(128) NOT NULL,
    uid varchar(64) NOT NULL,
    PRIMARY KEY (plate_uid,id),
    UNIQUE KEY uid (uid)
) ENGINE=MyISAM;

STEP 03 : Load the table ordering info into the ordering table

INSERT INTO lp_picture_ordering (plate_uid,uid)
SELECT plate_uid,uid FROM lp_pictures ORDER BY date_submitted;

STEP 04 : Perform UPDATE JOIN to assign id values

UPDATE lp_pictures A
INNER JOIN lp_picture_ordering B
USING (uid) SET A.id = B.id;

STEP 05 : Drop the ordering table

DROP TABLE lp_picture_ordering;

CAVEATS

CAVEAT #1

You needed to add the date_submitted index so the retrieval ordered by date would be fast

CAVEAT #2

You will only need to do STEP 01 once

CAVEAT #3

STEP 02 creates the increment values for every plate_uid. This technique is only possible with the MyISAM Storage Engine. In my earlier posts, I have suggested using an auto_increment column in a multiple column PRIMARY KEY:

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Jun 10, 2012 : MySQL get next unique value without auto increment
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin
Aug 28, 2013 : How to go about modeling the main object of relationship?

CAVEAT #4

In STEP 03, the id values are generated by the Storage Engine.

CAVEAT #5

The ordering table includes uid with a UNIQUE KEY so that STEP 04 can do an INNER JOIN

CAVEAT #6

When your done, no need to keep the ordering table. That's why I drop it.

CAVEAT #7

Should you ever need to do this again, just do Steps 2-5

Give it a Try !!!


Is the eager spool operator useful for this delete from a clustered columnstore?

Is there any plausible benefit to the spool iterator in the first plan?

This depends on what you regard as "plausible", but the answer according to the cost model is yes. Of course this is true, because the optimizer always chooses the cheapest plan it finds.

The real question is why the cost model considers the plan with the spool so much cheaper than the plan without. Consider estimated plans created for a fresh table (from your script) before any rows have been added to the delta store:

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE);

The estimated cost for this plan is a huge 771,734 units:

The cost is almost all associated with the Clustered Index Delete, because the deletions are expected to result in a great deal of random I/O. This is just the generic logic that applies to all data modifications. For example, an unordered set of modifications to a b-tree index are assumed to result in largely random I/O, with an associated high I/O cost.

Data-changing plans may feature a Sort to present rows in an order that will promote sequential access, for exactly these cost reasons. The impact is exacerbated in this case because the table is partitioned. Very partitioned, in fact; your script creates 15,000 of them. Random updates to a very partitioned table are costed especially high since the price to switch partitions (rowsets) mid-stream is given a high cost as well.

The last major factor to consider is that the simple update query above (where 'update' means any data-changing operation, including a delete) qualifies for an optimization called "rowset sharing", where the same internal rowset is used for both scanning and updating the table. The execution plan still shows two separate operators, but nevertheless, there is only one rowset used.

I mention this because being able to apply this optimization means the optimizer takes a code path that simply does not consider the potential benefits of explicitly sorting to reduce the cost of random I/O. Where the table is a b-tree, this makes sense, because the structure is inherently ordered, so sharing the rowset provides all the potential benefits automatically.

The important consequence is that the costing logic for the update operator does not consider this ordering benefit (promoting sequential I/O or other optimizations) where the underlying object is column store. This is because column store modifications are not performed in-place; they use a delta store. The cost model is therefore reflecting a difference between shared-rowset updates on b-trees versus columnstores.

Nevertheless, in the special case of a (very!) partitioned columnstore, there might still be a benefit to preserved ordering, in that performing all updates to one partition before moving to the next might still be advantageous from an I/O point of view.

The standard cost logic is reused for column stores here, so a plan that preserves partition ordering (though not order within each partition) is costed lower. We can see this on the test query by using undocumented trace flag 2332 to require sorted input to the update operator. This sets the DMLRequestSort property to true at the update, and forces the optimizer to produce a plan that provides all rows for one partition before moving to the next:

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE, QUERYTRACEON 2332);

The estimated cost for this plan is very much lower, at 52.5174 units:

This reduction in cost is all due to the lower estimated I/O cost at the update. The introduced Spool performs no useful function, except it can guarantee output in partition order, as required by the update with DMLRequestSort = true (the serial scan of a column store index cannot provide this guarantee). The cost of the spool itself is considered to be relatively low, especially compared with the (probably unrealistic) reduction in cost at the update.

The decision about whether to require ordered input to the update operator is made very early on in query optimization. The heuristics used in this decision have never been documented, but can be determined through trial and error. It seems that the size of any delta stores is an input to this decision. Once made, the choice is permanent for the query compilation. No USE PLANhint will succeed: the target of the plan either has ordered input to the update, or it does not.

There is another way to get a low-cost plan for this query without artificially limiting the cardinality estimate. A sufficiently low estimate to avoid the Spool will probably result in DMLRequestSort being false, resulting in a very high estimated plan cost due to the expected random I/O. An alternative is to use trace flag 8649 (parallel plan) in conjunction with 2332 (DMLRequestSort = true):

DELETE Fact.RecordedMetricsDetail
WHERE MeasurementTime < DATEADD(day,-1,GETUTCDATE())
OPTION (RECOMPILE, QUERYTRACEON 2332, QUERYTRACEON 8649);

This results in a plan that uses per-partition batch-mode parallel scan and an order-preserving (merging) Gather Streams exchange:

Depending on the run-time effectiveness of partition ordering on your hardware, this may perform best of the three. That said,large modifications are not a great idea on column store, so the partition-switching idea is almost certainly better. If you can cope with the long compilation times and quirky plan choices often seen with partitioned objects - especially when the number of partitions is large.

Combining many, relatively new, features, especially near their limits, is a great way to obtain poor execution plans. The depth of optimizer support tends to improve over time, but using 15,000 partitions of column store will likely always mean you live in interesting times.


mysql-community-server5.7.18 conflicts file /etc/my.cnf with Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64

try:

Download mysql-community-libs-compat from mysql (https://dev.mysql.com/downloads/mysql/)

rpm -i mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm

Install perl-DBD-MySQL

yum install perl-DBD-MySQL

Install percona-xtrabackup

yum install percona-xtrabackup


Upserting 50K records into big table (app. 350 columns) in postgresql

Some suggestions:

Use prepared statements. That will speed up execution, because the statement doesn't have to be planned every time.

Use savepoints. That way, you can have a single big transaction and still recover from errors:

Set a savepoint every 100 rows or so.

If a row fails, rollback to the savepoint and redo everything up to the failing row, then skip that and start a new savepoint.

If you have many errors, perhaps fewer than 100 rows would be preferable, so that you don't have to redo so much work. But setting a savepoint for every single row will also not be good for performance.

COPY the data to a temporary table and then
INSERT INTO finaltable
SELECT * FROM temptable
ON CONFLICT ...;


unknown variable after upgrading from mySQL 5.7 to 8

Found it. Apparently there was a naughty my.cnf file under /usr/local, where the variable was. Problem fixed by commenting it.


Do frequent DML operations on a table with indexes and huge data affect SELECT query performance?

First a comment on terminology: DML (Data Manipulation Language) includes SELECT. I have a feeling that you when you say DML refer to INSERT, UPDATE and DELETE. I'll be referring to these as "modification statements".
You also say that you run "default isolation level (Read Uncommitted)". That is not correct. The default isolation level is Read Committed, not Read Uncommitted.
Read Committed is indeed blocked by exclusive locks, which are acquired by data modification statements. That is the default behavior of Read Committed, but you can re-configure the behavior by changing the database setting read committed snapshot to ON (RCSI). With RCSI readers are not blocked by modifiers, they'll get the last committed version of the row instead of waiting. (RCSI is by default ON for Azure SQL Database, btw.)
Don't worry about update statistics. It doesn't block readers. That blog post you refer to INDEX REORGANIZE at the same time as UPDATE STATISTICS. UPDATE STATISTICS in itself doesn't block readers. I just verified this by running update statistics on the stats for an index and I simultaneously ran SELECT using that index/stats, several times without any hint of blocking.


Extract week number from character varying column where dates are in float values as well as in datetimestamp as well

You can use a CASE expression with a regex to test if the string is formatted like an ISO date or not:

SELECT closure_date,
       case
         when closure_date ~ '[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9:]{8}'
           then extract(week from closure_date::timestamp)
         else
           extract('week' from timestamp '1899-12-30' + (interval '1 day' * cast(closure_date as double precision)))
        end as closure_week
FROM <table_name>

The ~ operator compares the closure_date with the regular expression to see if it matches that pattern.

Note that if you have different formats, this could still fail and you need to add more checks.

If you have the chance, you should really change that column to be a timestamp rather than keeping this badly formatted string.


Instead of triggering it by ROW you can use FOR EACH STATEMENT.

CREATE TRIGGER trg_upd_group AFTER INSERT ON transactions
    REFERENCING NEW TABLE AS inserted
    FOR EACH STATEMENT
    EXECUTE FUNCTION upd_group();

Quoted from Postgres Docs:

  FOR EACH ROW
  FOR EACH STATEMENT

  This specifies whether the trigger function should be fired once for every row affected by the trigger event, or just once per SQL statement. If neither is specified, FOR EACH STATEMENT is the default. Constraint triggers can only be specified FOR EACH ROW.

The you can define an AFTER INSERT trigger that updates all rows WHERE group_id IS NULL, assigning to them the same group_id.

CREATE FUNCTION upd_group()
RETURNS trigger AS
$upd_group$

DECLARE

    max_group_id_ INTEGER := 0;

BEGIN

    SELECT MAX(group_id) INTO max_group_id_ FROM transactions;
    max_group_id_ := COALESCE(max_group_id_, 0) + 1;

    UPDATE transactions t
    SET    group_id = max_group_id_
    FROM   inserted i
    WHERE  t.id = i.id
    AND    t.group_id IS NULL;

    RETURN NULL;

END;
$upd_group$ language plpgsql;

INSERT INTO transactions (value, note, group_id) VALUES
(12, 'note 12', NULL),
(13, 'note 13', NULL),
(14, 'note 14', NULL),
(15, 'note 15', NULL);

INSERT INTO transactions (value, note, group_id) VALUES
(112, 'note 112', NULL),
(113, 'note 113', NULL),
(114, 'note 114', NULL),
(115, 'note 115', NULL);

SELECT * FROM transactions;

id |   value | note     | group_id
-: | ------: | :------- | -------:
 1 |  12.000 | note 12  |        1
 2 |  13.000 | note 13  |        1
 3 |  14.000 | note 14  |        1
 4 |  15.000 | note 15  |        1
 5 | 112.000 | note 112 |        2
 6 | 113.000 | note 113 |        2
 7 | 114.000 | note 114 |        2
 8 | 115.000 | note 115 |        2

db<>fiddle here


Invalid object name after aliasing a table?

You can't reference an alias from a subquery at the same scope. You can do this with a CTE, though; perhaps you meant:

;WITH Avgs AS
(
  select mID as movieID, avg(stars) as avgStars
  from Rating group by mID
)
SELECT movieID, avgStars FROM Avgs
WHERE avgStars <= ALL (SELECT AvgStars FROM Avgs);

You shouldn't need DISTINCT there, either.


A view can be materialised by creating an index on it. There are several preconditions which must be met. These may be tricky to impose on large, established applications with layered views.


How to get all dates belonging to a timestamp range

If you move the generate_series() to the FROM clause you can use a WHERE condition to limit the rows:

select t.dt::date
from  tsrange('2019-03-04 10:00'::timestamp, '2019-03-06 20:00'::timestamp) r,
      generate_series(lower(r), upper(r), '1 day') as t(dt)
where t.dt < case
                when upper(r) = date_trunc('day', upper(r))
                     then upper(r)::date
                else upper(r)::date + 1
             end;

dt
----------
2019-03-04
2019-03-05
2019-03-06

If the upper(r) is at midnight, then the generated date (t.dt) needs to be smaller than upper(r). If it's not, generated date needs to be smaller than the next day after the upper end of the range.

This query is completely independent of the hour in the actual range.

select t.dt::date
from  tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp) r,
      generate_series(lower(r), upper(r), '1 day') as t(dt)
where t.dt < case
                when upper(r) = date_trunc('day', upper(r))
                     then upper(r)::date
                else upper(r)::date + 1
             end;

dt
----------
2019-03-04
2019-03-05


SQL Server Estimated I/O Cost in real units

The best numbers for IO you can get is to turn on statistics.

SET STATISTICS IO ON

and then run your query via management studio. The set option will work for your current connection, and the information will appear in the messages tab.

Then you'll get information on how many pages will be read based on your query.

Also for ease of parsing complex/large query statistics, I use this handy little webpage: http://www.statisticsparser.com/


Select Into a table on server1 from server2 without using 'Linked Servers'

If your goal is just a one time transfer, the quickest method would be to use SQL Server Import and Export Wizard (Right click on the database -
    Tasks - Export Data)

And here is SQL Server Management Studio Express (64-bit) if you don't have one already.


Query to list "last backup time" listing wrong times ( SQL Server 2008 R2 )

Your problem may be that the backup set was created but the backup did not complete. I use the query below to check my backups

USE MSDB;
GO

SELECT TOP  300
            bckset.database_name AS DatabaseName,
            bckmdiaset.physical_device_name AS BackupLocation,
            CASE
                WHEN bckset.backup_size <= '10485760' THEN
                    CAST(CAST(bckset.backup_size/1024 AS INT) AS VARCHAR(14)) + ' ' + 'KB'
                ELSE
                    CASE
                        WHEN bckset.backup_size <= '1048576000' THEN
                            CAST(CAST(bckset.backup_size/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'MB'
                        ELSE
                            CAST(CAST(bckset.backup_size/1024/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'GB'
                    END
            END backupSize,
            CAST (bckset.backup_start_date AS smalldatetime) AS StartTime,
            CAST (bckset.backup_finish_date AS smalldatetime)FinishTime,
            CASE
                WHEN CAST(DATEDIFF(second, bckset.backup_start_date, bckset.backup_finish_date )AS VARCHAR (4)) <= 60 THEN
                    CAST(DATEDIFF(second, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Seconds'
                ELSE
                    CAST(DATEDIFF(minute, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Minutes'
            END AS TimeTaken,
            CAST(bckset.first_lsn AS VARCHAR(25)) AS FirstLogSequenceNumber,
            CAST(bckset.last_lsn AS VARCHAR(25)) AS LastLogSequenceNumber,
            CASE bckset.[type]
                WHEN 'D' THEN 'Full'
                WHEN 'I' THEN 'Differential database'
                WHEN 'L' THEN 'Transaction Log'
                WHEN 'F' THEN 'File or filegroup'
                WHEN 'G' THEN 'Differential file'
                WHEN 'P' THEN 'Partial'
                WHEN 'Q' THEN 'Differential partial'
            END AS BackupType,
            bckset.server_name As ServerName,
            bckset.recovery_model As RecoveryModel,
            CASE bckset.is_snapshot
                WHEN '0' THEN 'FALSE'
                WHEN '1' THEN 'TRUE'
            END AS IsSnapshot,
            CASE [compatibility_level]
                WHEN 60 THEN 'SQL Server 6.0'
                WHEN 65 THEN 'SQL Server 6.5'
                WHEN 70 THEN 'SQL Server 7.0'
                WHEN 80 THEN 'SQL Server 2000'
                WHEN 90 THEN 'SQL Server 2005'
                WHEN 100 THEN 'SQL Server 2008'
                WHEN 110 THEN 'SQL Server 2012'
                WHEN 120 THEN 'SQL Server 2014'
            END AS CompatibilityLevel

FROM        msdb.dbo.backupset bckset
INNER JOIN  msdb.dbo.backupmediafamily bckmdiaset ON bckset.media_set_id = bckmdiaset.media_set_id

---WHERE bckset.database_name = ''

ORDER BY    backup_start_date DESC, backup_finish_date


Sleep Processes are Running Longer Than Allowed

There are two possibilities the come to mind:

the values of interactive_timeout and wait_timeout were not 600 when those threads first connected, so they are using the values that existed when they first connected, or
the application is changing the timeout values for its sessions after connecting.

If you're running out of available connections, though, why not just raise max_connections?   If the threads are sleeping, you're not talking about a significant resource cost.


Got timeout reading communication packets vs Got error reading communication packets

For my situation, the master met with the error timeout, but the slave run the same sql successfully. The sql is about load data, so the master actually finished reading the whole packet?


Relationship with optional in-between table

I think that the idea of a special “empty” subdimension that is used if there is no real subdimension is the best one. It makes the data model and the queries simple.


I had to use a Script Task at the Control Flow level instead of a Script Component at the Data Flow level. When I switched that around it worked like a charm.                        I also changed the sw.Write(fileText, System.Text.Encoding.ASCII); line to sw.WriteLine(fileText, System.Text.Encoding.ASCII); so it adds the text to the next line.


What is the best to add unique index on nvarchar(max) field on existing table?

varchar(max) columns cannot be indexed, as you have found out.
You will need to index something else.

It could be a shortened version of the data (only you can tell whether this is acceptable or not) or a hashed version of the data. The odds of a hash collision are very low.

USE tempdb;

IF OBJECT_ID('user') IS NOT NULL
    DROP TABLE [user];

CREATE TABLE [user] (
    UFID varchar(max)
)

INSERT INTO [user] VALUES(1),(2),(3),(4),(4),(4),(3),(5);

WITH ranked_users AS (
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY UFID ORDER BY (SELECT NULL))
    FROM [user]
)
update ranked_users
set UFID = UFID + '_dup_removal_' + CAST(RN AS varchar(10))
where RN > 1;

--CREATE UNIQUE INDEX IX_UFID ON [user](UFID) -- FAILS

-- Solution 1: add a computed column with the size trimmed down
ALTER TABLE [user] ADD shortened_ufid AS CAST(UFID AS varchar(900))

CREATE UNIQUE INDEX IX_shortened_UFID ON [user](shortened_ufid)

-- Solution 2: add a computed column with the hashed version of the data
ALTER TABLE [user] ADD hashed_ufid AS CAST(HASHBYTES('SHA1', UFID) AS bigint)

CREATE UNIQUE INDEX IX_hashed_UFID ON [user](hashed_UFID)

SELECT *
FROM [user]
GO

Another option is cleansing the data and then ensure no duplicates can be inserted using a trigger. This is going to be painfully slow, though.

-- solution 3: use a trigger
CREATE TRIGGER TR_no_dupes ON [user]
FOR INSERT, UPDATE
AS
BEGIN

    IF EXISTS (
        SELECT 1
        FROM [user]
        WHERE UFID IN (
            SELECT UFID
            FROM inserted
        )
        GROUP BY UFID
        HAVING COUNT(*) > 1
    )
    ROLLBACK;

END


Сopy rows from one table to another and replace duplicates in the process

merge production as target
using raw as source
on target.refno = source.refno
when matched and souce.date > target.date
     then update set target.date = souce.date, target.value= souce.value
when not matched
     then insert ...

merge


Why do those Update statements fail using a datetime format?

You could deal with the issue by specifying the style and doing an explicit CONVERT() from string to datetime rather than an implicit conversion. https://msdn.microsoft.com/en-us/library/ms187928.aspx

UPDATE dbo.t SET mydatetime = CONVERT('2011-12-25 07:00:00.000', 121)


Plase try to follow this steps:

Launch the Microsoft Management Console. Select File > Add/Remove snap-in:

Add a Certificates snap-in:

Select “Computer account”:

Use the Local Computer:

With the snap-in now configured, right-click on the “Personal” certificate store folder, select All Tasks > Import:

Use the certificate import wizard to select the .PFX file to import.
In either case, follow the import wizard instructions. Enter the password set when the PFX file was created. Ensure that “Include all extended properties” is selected:

Select “Place all certificates in the following store” and choose the “Personal” store:

Once the certificate has been imported, SQL Server must be configured to use it. The SQL Server configuration manager for SQL Server 2008/R2 has an option to set the certificate associated with an instance, by opening the “SQL Server Networking Configuration,” right-clicking on “Protocols for ,” and opening the Certificate tab. However, this method cannot be used to select any certificate other than a machine certificate for servers that belong to a domain.

Find the “Thumbprint” of the certificate, and copy it to the clipboard. In the certificate management console, it can be found by opening the certificate, selecting the “Details” tab, scrolling to the bottom of the field list, and selecting “Thumbprint”:

After locating the thumbprint, copy it out of the certificate viewer, paste it into notepad, remove all of the spaces, and copy the modified thumbprint back to the clipboard. Be careful – it’s easy to make a mistake.

Open regedit
Navigate to: HKLM\Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib
Locate the “Certificate” entry
Assign its value to the thumbprint copied out of the certificate, e.g.:

The instance must be restarted for the changes to take effect.


Many times, when the code is not correctly working iinside package/procedure and is ok in anonymous PL/SQL; it comes from the AUTHID clause restrictions:

http://psoug.org/definition/AUTHID.htm


what is the fast way to prepare test db? slimming mysql db

MySQL doesn't contain any built-in way of marking rows when they're accessed. You could conceivably use triggers to record updates, inserts, and deletes, but not selects.

It's conceivable that somebody could adapt the alpha-quality MySQL proxy server, or the MySQL relay, to build a recording proxy. But that would be a lot of work, and it would yield the queries.


Data migration between databases in the same instance

I'd go straight SSIS myself. Besides having configuration, logging and error handling out of the box, you can get some really nice performance out of it.

You could probably save quite a bit of time by using something like EzAPI to script out the majority of the packages and then tweak the remaining 10%.

I recently used that approach to script out replicating ~150 tables from our Informix instance to SQL Server. Saved me a ton of time by getting the basics set up, all I had to do was fix some data type incompatibility between the systems. I'm behind on blogging about EzAPI but I could put together a sample if you're interested in the approach.

Edit

I put together an demo of replicating tables with SSIS EzAPI. You'd need to modify the values in ReplicateTables and then modify the query in GenerateTableList to ensure it's identifying all the applicable tables.


There is a lot of information here,

http://msdn.microsoft.com/en-us/library/ms151769(v=sql.105).aspx

And here,

http://msdn.microsoft.com/en-us/library/ms152502(v=sql.105).aspx

So the thing to be aware of, because I was using parameterised filters the snapshot folder above doesn't contain any data, only schema.

Also I was not filtering correct using SUSER_SNAME() so the filtering was not using the snapshot at all causing me performance issues.

Once that was sorted I could see in the snapshot folder there subfolders with a snapshot for each user of the system. This data is the data filtered for that particular user.


SQL server replication - Does not ask for the snapshot folder

SQL Server asks for a root snapshot folder path when you Configured Publishing and Distribution.

After configuring publishing and distribution, all subsequent publications created will use that snapshot folder but will have their own sub-folder within that folder.

If you wish to have your snapshot files for a particular publication be put into a different root snapshot folder, specify this by passing in the path to the @alt_snapshot_folder parameter of sp_addpublication or sp_addmergepublication when you create the publication.  Alternatively, this can be done from the Publication Properties dialog after you have created your publication and before generating a snapshot on the Snapshot page, Location of snapshot files section, Put files in the following folder.

If you have anymore questions, please let me know.

I hope this helps.


CASE Statement - 1st case is being ignored

To me it looks much like you're asking only the registered users, as the SELECT acting as the basis for the view is:

SELECT
[...]
 FROM usr_t_user_reg

Thus having "some users who are not registered yet and they are not displayed in the output" is what should be expected.

Having something like this would show the not (yet) registered users:

  SELECT
    u.username "User",
    r.db_instance "Instance",
    NVL(r.db_instance,'not registered yet') "Status"
  FROM
    SYS.dba_users u LEFT OUTER JOIN usr_t_user_reg r ON u.username = r.username;

That will, however, not give those users that are still in the "registered" table but are not anymore DB users, for that you could use the parallel:

  SELECT
    r.username "User",
    r.db_instance "Instance",
    CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE 'OK' END "Status"
  FROM SYS.dba_users u RIGHT OUTER JOIN usr_t_user_reg r ON u.username = r.username;

The SQL above is probably possible to combine nicely, and is likely to be sub-optimal, but it did work on quick testing...

Edit:
Full outer join version combining the two queries above:

  SELECT
    NVL(r.username, u.username) "User",
    r.db_instance "Instance",
    CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE
      CASE NVL(r.db_instance,'X') WHEN 'X' THEN 'not registered' ELSE 'OK' END
    END "Status"
  FROM SYS.dba_users u FULL OUTER JOIN usr_t_user_reg r ON u.username = r.username;

Edit 2:
A version providing also the name of the instance(s) the user is not registered, the name of the instance(s) the non-existent user is registered to, and filtering out all of the cases where status is OK:

SELECT *
FROM (
  WITH t1 AS (
    SELECT
      u.username,
      i.db_instance
    FROM SYS.dba_users u, ( SELECT DISTINCT db_instance FROM usr_t_user_reg ) i
  )
  SELECT
    t1.username "User",
    t1.db_instance "Instance",
    CASE NVL(r.db_instance, 'X') WHEN 'X' THEN 'not registered' ELSE 'OK' END "Status"
  FROM t1 LEFT OUTER JOIN usr_t_user_reg r ON t1.username = r.username AND t1.db_instance = NVL(r.db_instance, t1.db_instance)
  union
  SELECT
    r.username "User",
    r.db_instance "Instance",
    CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE 'OK' END "Status"
  FROM SYS.dba_users u RIGHT OUTER JOIN usr_t_user_reg r ON u.username = r.username
)
WHERE "Status"!='OK';

It starts getting uglier and even less optimal, but again that did work on the test setup.


Installing Netezza 6.0.8 ODBC driver on Windows Server 2008

[Not really an answer as I'm just stating the obvious but I don't have enough rep to comment.]

The four driver files are the ODBC driver, the JDBC driver, and the 32 and 64-bit OLEDB drivers. The ODBC driver is of course the correct one but there's a small check box in one of the installer dialogs which allows you to choose 32-bit and/or 64-bit. Did you select the correct one?

I've just tried this on Windows Server 2012 and Windows 7 and both work exactly the same for me without any extra steps. The driver I have is called nsqlodbc.dll, version 7.0.0.27274, in C:\Windows\System32 (and I also have a 32-bit version in C:\Windows\SysWOW64). I can connect from both the 32-bit and 64-bit ODBC Data Source Administrator tools although I got my password wrong several times so that's certainly worth checking.

Have you also checked that the advanced options are the same, SSL for instance?

Possibly there's a security setting on your Server box that's preventing the connection but on 2012 Server there didn't appear to be anything by default.


Invalid object name 'master.dbo.MSreplication_options' when restoring DB

As far as I can tell, the table master.dbo.MSreplication_options, amongst various other replication-related tables, are supposed to exist on any SQL Server 2008 r2 install whether or not you've set up replication.  Somehow the one on my SQL Server instance got dropped.  I eventually found this handy script on Google (cached!) which 'fixes up' various replication things, including recreating master.dbo.MSreplication_options if it doesn't already exist:

-- ***************************************************************************
-- Copyright (c) 1997 - 2003 Microsoft Corporation.
-- All Rights Reserved
--
-- @File: ReplicationFixup.sql
--
-- Purpose:
--  Procedures/extended stored procedures (XPs)/functions that are owned by replication and are created on master database
--
-- Notes: Created 2001/02/07 10:28 (RMak)
--
-- History:
--
--     @Version: Yukon
--
-- @EndHeader@
--
exec sys.sp_configure 'update',1
reconfigure with override

set ANSI_NULLS off

use master
go

-- Make sure that we remove procedures that got accidentally installed in
-- master by an 80 sp2 QFE
if object_id('dbo.sp_MSreplremoveuncdir', 'P') > 0
    drop procedure dbo.sp_MSreplremoveuncdir

if object_id('dbo.sp_MSdeletefoldercontents', 'P') > 0
    drop procedure dbo.sp_MSdeletefoldercontents

-- drop extended procedures that were created in master

if object_id('xp_mergexpusage', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_mergexpusage'

if object_id('xp_mergelineages', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_mergelineages'

if object_id('xp_mapdown_bitmap', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_mapdown_bitmap'

if object_id('xp_ORbitmap', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_ORbitmap'

if object_id('xp_firstonly_bitmap', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_firstonly_bitmap'

if object_id('xp_varbintohexstr', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_varbintohexstr'

if object_id('xp_intersectbitmaps', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_intersectbitmaps'

if object_id('xp_displayparamstmt', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_displayparamstmt'

if object_id('xp_printstatements', 'local') is not null
    execute sys.sp_dropextendedproc 'xp_printstatements'

if object_id('xp_makecab', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_makecab'

if object_id('xp_unpackcab', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_unpackcab'

if object_id('sp_repldone', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_repldone'

if object_id('sp_repltrans', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_repltrans'

if object_id('sp_replcounters', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replcounters'

if object_id('sp_replhelp', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replhelp'

if object_id('sp_replddlparser', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replddlparser'

if object_id('sp_replcmds', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replcmds'

if object_id('sp_replflush', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replflush'

if object_id('sp_replpostcmd', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replpostcmd'

if object_id('sp_replincrementlsn_internal', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replincrementlsn_internal'

if object_id('sp_replupdateschema', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replupdateschema'

if object_id('sp_replsetoriginator_internal', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replsetoriginator_internal'

if object_id('sp_replsetsyncstatus', 'local') is not null
    exec sys.sp_dropextendedproc 'sp_replsetsyncstatus'

if object_id('sp_replpostsyncstatus_int', 'local') is not null
        exec sys.sp_dropextendedproc 'sp_replpostsyncstatus_int'

if object_id('xp_dsninfo', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_dsninfo'

if object_id('xp_enumdsn', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_enumdsn'

if object_id('xp_oledbinfo', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_oledbinfo'

if object_id('xp_repl_encrypt', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_repl_encrypt'

if object_id('xp_repl_convert_encrypt', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_repl_convert_encrypt'

if object_id('xp_repl_help_connect', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_repl_help_connect'

if object_id('xp_replproberemsrv', 'local') is not null
    exec sys.sp_dropextendedproc 'xp_replproberemsrv'

go

--
-- Create table dbo.MSreplication_options in master if needed
--
if object_id(N'dbo.MSreplication_options', 'local') is null
BEGIN
    -- table does not exist
    raiserror('Creating table MSreplication_options',0,1)

    CREATE TABLE dbo.MSreplication_options
    (
        optname sysname NOT NULL,
        value bit NOT NULL,
        major_version int NOT NULL,
        minor_version int NOT NULL,
        revision int NOT NULL,
        install_failures int NOT NULL
    )

    exec dbo.sp_MS_marksystemobject N'dbo.MSreplication_options'
END
ELSE
BEGIN
    -- table exists
    -- drop index if needed (this index was used in Sphinx)
    if exists (select * from sys.indexes where object_id = object_id(N'dbo.MSreplication_options')
                        and name = N'ucMSreplication_options')
    begin
        drop index dbo.MSreplication_options.ucMSreplication_options
    end
END
GO

IF NOT EXISTS (SELECT * FROM MSreplication_options WHERE optname = 'transactional')
    INSERT INTO MSreplication_options VALUES
        ('transactional',0,0,0,0,0)
IF NOT EXISTS (SELECT * FROM MSreplication_options WHERE optname = 'merge')
    INSERT INTO MSreplication_options VALUES
        ('merge',0,0,0,0,0)
IF NOT EXISTS (SELECT * FROM MSreplication_options WHERE optname = 'security_model')
BEGIN
    DECLARE @major_version  int,
            @minor_version  int,
            @revision       int

    -- @@microsoftversion is set as 0xMMmmRR[RR] wher M=Major, m=minor and R=revision
    --  SELECT @major_version   = CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @@microsoftversion), 1, 1)),
    --      @minor_version  = CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @@microsoftversion), 2, 1)),
    --      @revision       = CONVERT(int, SUBSTRING(CONVERT(varbinary(4), @@microsoftversion), 3, 2))
    SELECT @major_version   = 90,
            @minor_version  = 0,
            @revision       = 0

    INSERT INTO MSreplication_options (optname, value, major_version, minor_version, revision, install_failures)
        VALUES ('security_model', 1, @major_version, @minor_version, @revision, 0)
END

UPDATE MSreplication_options
    SET major_version = 90
GO

-- Startup procs have to be created in master
if object_id('sp_MSrepl_startup', 'local') is not null
    drop procedure sp_MSrepl_startup

raiserror('Creating procedure sp_MSrepl_startup', 0,1)
go

create procedure dbo.sp_MSrepl_startup
as
    exec sys.sp_MSrepl_startup_internal
go

exec master.dbo.sp_MS_marksystemobject sp_MSrepl_startup

-- If a distributor is installed, mark the sp as a startup sp.
if exists (select * FROM master..sysservers WHERE  srvstatus & 8 <> 0)
    exec dbo.sp_procoption 'sp_MSrepl_startup', 'startup', 'true'
go

if object_id('sp_MScleanupmergepublisher', 'local') is not null
    drop procedure sp_MScleanupmergepublisher
go

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

raiserror('Creating procedure sp_MScleanupmergepublisher', 0,1)
go
create procedure dbo.sp_MScleanupmergepublisher
as
    exec sys.sp_MScleanupmergepublisher_internal
go

-- If there are any merge published databases installed on this server,
-- mark sp_MScleanupmergepublisher as a startup proc

if exists (select * from master..sysdatabases where (category & 4) <> 0)
    exec dbo.sp_procoption 'sp_MScleanupmergepublisher', 'startup', 'true'

--
-- Functions that used to be created in master and have now moved to resource
-- use exec to drop, otherwise "drop function" gives syntax error on SQL 7, which did not have UDFs yet
--

if object_id('fn_varbintohexstr', 'local') is not null
     exec('drop function dbo.fn_varbintohexstr')

if object_id('fn_varbintohexsubstring', 'local') is not null
     exec('drop function dbo.fn_varbintohexsubstring')

go

--
-- procedures that used to be created in master and are obsolete now
-- drop the local procedures in master
--
if object_id(N'dbo.sp_addpublisher', 'local') is not null
    drop procedure dbo.sp_addpublisher

if object_id(N'dbo.sp_fetchshowcmdsinput', 'local') is not null
    drop procedure dbo.sp_fetchshowcmdsinput

if object_id(N'dbo.sp_getagentoffloadinfo', 'local') is not null
    drop procedure dbo.sp_getagentoffloadinfo

if object_id(N'dbo.sp_gettypestring', 'local') is not null
    drop procedure dbo.sp_gettypestring

if object_id(N'dbo.sp_helpmergecleanupwait', 'local') is not null
    drop procedure dbo.sp_helpmergecleanupwait

if object_id(N'dbo.sp_helpsubscriptionjobname', 'local') is not null
    drop procedure dbo.sp_helpsubscriptionjobname

if object_id(N'dbo.sp_mergecompletecleanup', 'local') is not null
    drop procedure dbo.sp_mergecompletecleanup

if object_id(N'dbo.sp_mergepreparecleanup', 'local') is not null
    drop procedure dbo.sp_mergepreparecleanup

if object_id(N'dbo.sp_MSaddpubtocontents', 'local') is not null
    drop procedure dbo.sp_MSaddpubtocontents

if object_id(N'dbo.sp_MSareallcolumnscomputed', 'local') is not null
    drop procedure dbo.sp_MSareallcolumnscomputed

if object_id(N'dbo.sp_MSchunkgeneration', 'local') is not null
    drop procedure dbo.sp_MSchunkgeneration

if object_id(N'dbo.sp_MScleanup_metadata', 'local') is not null
    drop procedure dbo.sp_MScleanup_metadata

if object_id(N'dbo.sp_MScleanuptask', 'local') is not null
    drop procedure dbo.sp_MScleanuptask

if object_id(N'dbo.sp_MScompletecleanup', 'local') is not null
    drop procedure dbo.sp_MScompletecleanup

if object_id(N'dbo.sp_MScomputearticlescreationorder', 'local') is not null
    drop procedure dbo.sp_MScomputearticlescreationorder

if object_id(N'dbo.sp_MScomputeunresolvedrefs', 'local') is not null
    drop procedure dbo.sp_MScomputeunresolvedrefs

if object_id(N'dbo.sp_MSdelete_specifiedcontents', 'local') is not null
    drop procedure dbo.sp_MSdelete_specifiedcontents

if object_id(N'dbo.sp_MSdeletecontents', 'local') is not null
    drop procedure dbo.sp_MSdeletecontents

if object_id(N'dbo.sp_MSdeletepushagent', 'local') is not null
    drop procedure dbo.sp_MSdeletepushagent

if object_id(N'dbo.sp_MSenumchanges_direct', 'local') is not null
    drop procedure dbo.sp_MSenumchanges_direct

if object_id(N'dbo.sp_MSenumchanges_pal', 'local') is not null
    drop procedure dbo.sp_MSenumchanges_pal

if object_id(N'dbo.sp_MSenumpartialchanges_direct', 'local') is not null
    drop procedure dbo.sp_MSenumpartialchanges_direct

if object_id(N'dbo.sp_MSenumpartialchanges_pal', 'local') is not null
    drop procedure dbo.sp_MSenumpartialchanges_pal

if object_id(N'dbo.sp_MSexternalfkreferences', 'local') is not null
    drop procedure dbo.sp_MSexternalfkreferences

if object_id(N'dbo.sp_MSget_subtypedatasrc', 'local') is not null
    drop procedure dbo.sp_MSget_subtypedatasrc

if object_id(N'dbo.sp_MSgettypestringudt', 'local') is not null
    drop procedure dbo.sp_MSgettypestringudt

if object_id(N'dbo.sp_MShelpsubscriptionjobname', 'local') is not null
    drop procedure dbo.sp_MShelpsubscriptionjobname

if object_id(N'dbo.sp_MSinsertcontents', 'local') is not null
    drop procedure dbo.sp_MSinsertcontents

if object_id(N'dbo.sp_MSis_col_replicated', 'local') is not null
    drop procedure dbo.sp_MSis_col_replicated

if object_id(N'dbo.sp_MSload_replication_status', 'local') is not null
    drop procedure dbo.sp_MSload_replication_status

if object_id(N'dbo.sp_MSmakedynsnapshotvws_longdef', 'local') is not null
    drop procedure dbo.sp_MSmakedynsnapshotvws_longdef

if object_id(N'dbo.sp_MSpreparecleanup', 'local') is not null
    drop procedure dbo.sp_MSpreparecleanup

if object_id(N'dbo.sp_MSquiescecheck', 'local') is not null
    drop procedure dbo.sp_MSquiescecheck

if object_id(N'dbo.sp_MSquiesceforcleanup', 'local') is not null
    drop procedure dbo.sp_MSquiesceforcleanup

if object_id(N'dbo.sp_MSquiescetriggersoff', 'local') is not null
    drop procedure dbo.sp_MSquiescetriggersoff

if object_id(N'dbo.sp_MSquiescetriggerson', 'local') is not null
    drop procedure dbo.sp_MSquiescetriggerson

if object_id(N'dbo.sp_MSreplcheck_connection', 'local') is not null
    drop procedure dbo.sp_MSreplcheck_connection

if object_id(N'dbo.sp_MSscript_security', 'local') is not null
    drop procedure dbo.sp_MSscript_security

if object_id(N'dbo.sp_MSscript_validate_subscription', 'local') is not null
    drop procedure dbo.sp_MSscript_validate_subscription

if object_id(N'dbo.sp_MSscriptmvastable', 'local') is not null
    drop procedure dbo.sp_MSscriptmvastable

if object_id(N'dbo.sp_MSscriptmvastableidx', 'local') is not null
    drop procedure dbo.sp_MSscriptmvastableidx

if object_id(N'dbo.sp_MSscriptmvastablenci', 'local') is not null
    drop procedure dbo.sp_MSscriptmvastablenci

if object_id(N'dbo.sp_MSscriptmvastablepkc', 'local') is not null
    drop procedure dbo.sp_MSscriptmvastablepkc

if object_id(N'dbo.sp_MSsubst_filter_name', 'local') is not null
    drop procedure dbo.sp_MSsubst_filter_name

if object_id(N'dbo.sp_MSupdate_replication_status', 'local') is not null
    drop procedure dbo.sp_MSupdate_replication_status

if object_id(N'dbo.sp_MSupdatecontents', 'local') is not null
    drop procedure dbo.sp_MSupdatecontents

if object_id(N'dbo.sp_replicationoption', 'local') is not null
    drop procedure dbo.sp_replicationoption

if object_id(N'dbo.sp_replproberemoteserver', 'local') is not null
    drop procedure dbo.sp_replproberemoteserver

if object_id(N'dbo.sp_replsetoriginator_pal', 'local') is not null
    drop procedure dbo.sp_replsetoriginator_pal

if object_id(N'dbo.sp_verify_publication', 'local') is not null
    drop procedure dbo.sp_verify_publication

if object_id(N'dbo.sp_MSarticletextcol', 'local') is not null
    drop procedure dbo.sp_MSarticletextcol

if object_id(N'dbo.sp_MSexists_file', 'local') is not null
    drop procedure dbo.sp_MSexists_file

if object_id(N'dbo.sp_MSfixlineagemismatch', 'local') is not null
    drop procedure dbo.sp_MSfixlineagemismatch

if object_id(N'dbo.sp_MStextcolstatus', 'local') is not null
    drop procedure dbo.sp_MStextcolstatus

if object_id(N'dbo.sp_MSread_resolver_clsid', 'local') is not null
    drop procedure dbo.sp_MSread_resolver_clsid

if object_id(N'dbo.sp_MSsubscriptions', 'local') is not null
    drop procedure dbo.sp_MSsubscriptions

go

After I ran this, I was able to import my backed-up DB on the subscriber without error.


Inventory Database Design? Get historical data from DB for reporting & accounting

Answer originally left in comments by Nick McDermaid

It depends on your requirements. In the current model you can't record the same order, different item taken from a warehouse on a different day - the date is at order level.
You could add a flavour attribute either to the Product table (in which case it gets a new id), or to the LineItem table.
Maybe. If the correct product IN date is in Inventory, and the correct product OUT date is in the purchase order header you can. But this doesn't appear to have the correct level of detail for inventory, more sales... depends.

By correct level of detail, what I mean is: Does PurchaseOrder.Date record when every widget left the warehouse? I don't think so. It most likely records when you a purchase order was raised. That PO might consist of 5 widgets. Three were picked and supplied from warehouse A on 1st May, and two were picked and supplied from warehouse B on 3rd May. I don't think your current set of tables have enough detail to describe when things left the warehouse (unless they all leave the same warehouse immediately that you get the PO, highly unlikely).

You don't have a 'left warehouse' transaction table so you don't know what date items left the warehouse so you can't work back and calculate inventory. warehouse db's usually have a 'warehouse transaction' table that records when items arrived and left the warehouse. You don't have that in your model. PurchaseOrder has a date but it doesn't really record when items left. Inventory has a date but it probably doesn't really record when the item arrived. You also need to periodically record stocktake levels.

To "find out the inventory value in 10th April from the data I have here" you take the current inventory value (today) and reverse all the inventory in transactions back to 10th April (from inventory table I guess), and also reverse all the sale (inventory out) transactions and you'll arrive at where you were on 10th April. As long as you are recording all inventory in and out transactions, and you know what you have today, you can work it out. But how do you know what you have today? Do you do a stocktake every day?

So if you have 10 cogs and 20 widgets today and over the last two weeks you had a total of 6 cogs in and 8 cogs out, you know that you effectively had -2 cogs over that time, so you must've had 10 - (-2) = 12 cogs two weeks ago. It's actually more realistic to calculate a stock balance based off a historical stock take and add forwards. You might have a stocktake every 3 months. From your stocktake in Jan you had 12 cogs, and from Jan to Apr you had 7 cogs in and sold 2 cogs so you know in April you had 12+7 - 2 = 17 cogs.

Notice a theme here? You need to understand your business. You need to analyse it. Back to your actual schema: it's not really stock focused, it's sales focused. You need a dedicated stock transaction table if you want to track stock levels. Lastly, there are many sample stock schemas online. Like this one: https://stackoverflow.com/questions/4380091/best-structure-for-inventory-database


Update one table from another table while sorting that table based on one column

There are two options

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.


Although there are a number of tools out there, I prefer the command line for tasks such as this. Better performance, and just simpler.

Make sure you're logged into the mysql client:

mysql -u <username> -p

The first step is to create a database in mysql that you want the data in:

CREATE DATABASE <database name>;

Then you need to open--or use--that new database, which is empty:

USE <database name>;

All that's left is to do the import:

\. /path/to/file (or if in Windows, \. C:\PATH\TO\FILE)

If all goes well, you'll see a bunch of output about "Query successful, N rows affected" and so on.


How can I tell how many Cores SQL Server is actually using?

Express edition is limited by 1 processor, not 1 core:

  SQL Server supports the specified number of processor sockets
  multiplied by the number of logical CPUs in each socket. For example,
  the following is considered a single processor for purposes of this
  table: A single-core, hyper-threaded processor with 2 logical CPUs per
  socket. A dual-core processor with 2 logical CPUs. A quad-core
  processor with 4 logical CPUs. SQL Server is licensed per processor
  socket, and not per logical CPU basis.

http://msdn.microsoft.com/en-us/library/ms143760(v=sql.105).aspx

And yes, sys.dm_os_schedulers can be used to ensure, that all 4 cores are mapped with schedulers.


How can I tell if my SQL Server DB performance is hardware-limited?

You can use DBCC SQLPERF("waitstats"). This will return the wait times of what tasks your SQL server was waiting on. Detailed explanations of each counter can be found online. You can use this information to find out your bottlenecks.

Also, turn on the client statistics in query analyzer to see the wait times on the client side.

I am assuming you hardware has not changed since your initial test, so since they are constant, I wouldn't doubt them.


Stored Procedure to evaluate column if condition is true execute another stored procedure

how would I go about executing a stored procedure on a table if a condition is met.

if exists (select 1 from Supply where SupplyAlert = 'Y')
begin
exec dbo.sp_SendMail
end
else print 'Nothing to send !'

Remember that CASE is a function and not a control flow statement.


Having the users and lost passwords in the same table implies a 1-1 (or 1-0/1) relationship; 1 user has 1 lost password (or none, if the column is nullable).

If that's the cardinality of the relationship, having them on the same table is correct. The school of thought that refuses nulls (which depending on the day, I adhere to), says that if the relationship is 1-0/1, you should:

users
  usersID primary key

lostPasswords
  usersID primary key references users(users_id)
  -- lost password columns here, non-nullable

The relationships look fine, however, I would suggest you use a diagramming tool which shows which are the columns referenced. Surprisingly, I've only found SchemaSpy doing this: http://schemaspy.sourceforge.net/sample/diagrams/summary/relationships.real.compact.png


Using a SSL-cert from a trusted CA offline to comunicate with SQL Server

A trusted CA does not require online connectivity to validate the certificate. The certificate is signed with a chain of signatures that is rooted in a private key for which the corresponding public key is already present in your system (this is what 'trusted CA' means, after all). You may be thinking at the certificate revocation list, which must be periodically refreshed online, but the system will work with even if the CRL cannot be refreshed.

Within an intranet there are options to use a corporate CA, but it does require a domain and PKI deployed.

Self-signed certificates with SQL Server provide only encryption, no server authentication. See Using Encryption Without Validation. There are no dialogs to popup for the clients, your connection attempt succeed or fails depending on a number of factors (read the article linked).


Postgres looped table (start sequence from 1 and rewrite old rows)

you could just use a monotonic sequence (always counting upwards) and delete records based on their age
you could do table partitioning by date and just drop the old partitions
Postgresql never replaces records, an update always creates a new record and marks the old one as unused. Autovacuum sooner or later will make the space used by the old one available to hold new records.  If you want something like RRDB your should probably be using somethign like RRDB instead of postgresql.


MySQLSyntaxErrorException: You have an error in your SQL syntax;

The word read is a reserved keyword in MySQL.

https://dev.mysql.com/doc/refman/5.5/en/keywords.html

You need to use a more appropriate column name.


was wondering does Adding the same column twice cause an impact on Performance? Meaning does SQL Server add the column twice? Or will it add the column once and use for both columns in the result set?

Sum will be calculated once and result is used for multiple columns referring the same sum

Below is sample setup from my instance ,which will prove the same

select
sum(empid) as empidsum,
sum(empid)*2 as empidsum,
sum(empid)*4 as empid4
 from orders

execution plan:

In the above execution plan,highlighted computed scalar shows below

Scalar Operator([Expr1003]*(2))
Scalar Operator([Expr1003]*(4))

Where Expr1003 is the sum calculated by previous compute scalar

  should I use a CTE with one SUM like the following

CTE is just for readability,using a CTE won't help performance .

There are cases where materialzing CTE will help in improving the query.Look out this answer for more details:

Which are more performant, CTE or temporary tables?


Database Design where there's three parent tables and only 1 child table

Your ResultsForType* and ResultSetFoType* tables are the same, there is no difference between them. Your problem is only about the Type?Tests tables.

Your problem is very common: how to map class inheritance and polimorphism to the ORM level. There are two most common practices to solve it:

What you are currently doing. You could make it redundency-free, on the cost of a much more complicated table structure. In exchange, your database will be pure and perfect, although a little bit overkill.
You can unify the Type?Tests tables, latter I explain, how.
Such a solution, where you get everything without losing anything, does not exist.

The sad truth is that SQL does not know polyporhism, and this is the ultimate cause of your current troubles. But it knows something which can help a lot to achieve some quite similar.

It can nullable values.

In your case, I would simply use instead of the Type?Tests tables this:

+----+------------+-----+-----+------------+------------+
| id | TestTypeId | min | max | daysStored | NoMoreThan |
+----+------------+-----+-----+------------+------------+

The essence is that we use a union of all possible columns in all the used classes, plus yet another column to identify the type.

Use the following constraints:

TestTypeId should be 1, 2 or 3
If TestTypeId=1, daysStored and NoMoreThan should be NULL
If TestTypeId=2, NoMoreThan should be NULL
If TestTypeId=3, daysStored should be NULL

Constraints are very useful - they make things guaranteed.


Two single-quotes together indicate a zero-length string.

Three single-quotes together indicate an escaped single quote; i.e. ''' becomes '

Four single-quotes together indicate an escaped single quote followed by another single quote, which is translated into a quotated single quote, similar to what you desire.  I know, it's confusing.  Try writing nested dynamic SQL, and you'll pretty quickly find strings with 7, 8, or more single quotes in a row.

Consider the output of the following three statements:

SELECT '[' + '' + ']'
SELECT '[' + '''' + ']'
SELECT '[' + '''-''' + ']'

[]

[']

['-']

Typical usage of QUOTENAME() is to protect against SQL Injection when concatenating T-SQL strings, by escaping text that is provided by a user.  For instance:

DECLARE @MyInput VARCHAR(50);
SET @MyInput = 'some table name';
DECLARE @cmd NVARCHAR(1000);
SET @cmd = 'SELECT * FROM ' + QUOTENAME(@MyInput) + ';';
PRINT (@cmd);

will output SELECT * FROM [some table name];.

If you don't use the QUOTENAME() function:

DECLARE @MyInput VARCHAR(50);
SET @MyInput = 'some table name';
DECLARE @cmd NVARCHAR(1000);
SET @cmd = 'SELECT * FROM ' + @MyInput + ';';
PRINT (@cmd);

You get SELECT * FROM some table name; which is not valid T-SQL because of the spaces in some table name.

Here comes little bobby tables:

You can see the effect of quotename in the following code:

IF OBJECT_ID('tempdb..#Students') IS NOT NULL
DROP TABLE #Students;
CREATE TABLE #Students
(
    StudentName VARCHAR(50)
);

INSERT INTO #Students (StudentName) VALUES ('Max');

SELECT *
FROM #Students;

    DECLARE @MyInput VARCHAR(50);
    SET @MyInput = 'Robert'';DROP TABLE #Students--';
    DECLARE @cmd NVARCHAR(1000);
    SET @cmd = 'SELECT * FROM #Students WHERE StudentName = ''' + QUOTENAME(@MyInput, '''') + ''';';
    PRINT (@cmd);
    EXEC (@cmd);

SELECT *
FROM #Students;

GO

    DECLARE @MyInput VARCHAR(50);
    SET @MyInput = 'Robert'';DROP TABLE #Students--';
    DECLARE @cmd NVARCHAR(1000);
    SET @cmd = 'SELECT * FROM #Students WHERE StudentName = ''' + @MyInput + ''';';
    PRINT (@cmd);
    EXEC (@cmd);

SELECT *
FROM #Students;

The output:

With the QUOTENAME() function, we do get an error message, but without the function, we allow our user to drop the #Students table.

Note, I'm using 4 single quotes in my QUOTENAME() function, since that is actually interpreted as one single quote, and used to wrap the text provided to the function, thereby making that text "safe".

Erland Sommarskog has an excellent piece on dynamic SQL at http://www.sommarskog.se/dynamic_sql.html - this is well worth reading (and understanding!) if you are considering doing dynamic SQL.


The -c option of pg_restore does this:

  Clean (drop) database objects before recreating them.

As you seem to only migrate a single table from here to there, you can safely omit -c from your command line.  If you only need the data, you should tell it to pg_dump instead, by using the -a option.  If your table structure is different as well, then you should prepare an ALTER TABLE statement manually to reflect those changes in the target database.  (You could as well use a few tools for the latter, but for only one table the manual solution is usually much faster.)


I think the best way to approach this would be to have tables like so:

table Customer
    ID
    Name
    ...

table CustomerFields
    CustomerID
    FieldID
    FieldName
    ... (could have things like type, required/optional, size, etc.)

table CustomerOrders
    CustomerID
    OrderID
    OrderNumber
    DeliveryDate
    ... (all your generics and mappable fields)

table CustomerOrderFields
    CustomerID
    OrderID
    FieldID
    FieldValue

By having the table CustomerOrders, you satisfy the ability to map common customer order data to common fields (simplifying reporting at the expense of making the import a little more painful since the fields must be mapped), and the CustomerOrderFields gives you the ability to have the custom fields per customer necessary for the un-mappable data.

The custom fields are still reportable, but not as easily as your generics as they'll come to you in multiple rows (instead of multiple columns). There are some ways around all that depending on your report creator (e.g., pivoting the results).

The only other option would be to do something like this (which, personally, I would avoid):

table CustomerFields
    CustomerID
    Field1Name
    Field2Name
    Field3Name
    ...
    Field99Name

Table CustomerOrders
    (all your generics)
    Field1Value
    Field2Value
    ...
    Field99Value

This has the advantage that all your data is in one row, but also has the disadvantage that it isn't immediately apparent from the row what each value means. The first method can always be joined to the field list to give a good definition of the data in each custom field. In addition, what happens if some company requires 100 custom fields? In the above example, you'd be making changes to your data structures and code whereas in the first example, you'd never have the issue -- customers could have infinite custom fields.

I've seen it done both ways, and both ways work. Both ways have their downsides and upsides. The first is far more scalable, but harder to get in to columns (instead of rows). Everything's a trade-off.

Hope that helps some!


Find the IDs and names of the employees whose salaries are at least 10% higher than the average salary of all employees

Using a simple subquery should help with this:

SELECT e.eid, CONCAT(efirstname, ' ', e.elastname) name
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees) * 1.10