How to Calculate Month over Month User Retention based on already active users (not based on user signup date)?

I am not sure I understood your requires completely, but anyway.

In Postgres I would probably do something like this:

with per_month as (
  select to_char(a1.created_at, 'yyyy-mm') as month,
         array_agg(distinct a1.user_id) as active_users
  from user_activity a1
  where a1.created_at >= timestamp '2018-12-01'
  group by month
), m2 as (
  select month,
         active_users,
         lead(active_users) over (order by month) as january_users,
         lead(active_users,2) over (order by month) as february_users,
         lead(active_users,3) over (order by month) as march_users,
         lead(active_users,4) over (order by month) as april_users
  from per_month
)
select month,
       cardinality(active_users) as num_users,
       (select count(*) from (select unnest(active_users) intersect select unnest(january_users)) t)  as january_retained,
       (select count(*) from (select unnest(active_users) intersect select unnest(february_users)) t)  as febrary_retained,
       (select count(*) from (select unnest(active_users) intersect select unnest(march_users)) t)  as march_retained
from m2;

The first CTE simply aggregates all users per month into an array. The second CTE then simply creates on column per month with the user of that month using a window function.

And the final query then calculates the intersection between the users of the starting month with each of the following months by unnesting the arrays and counting the size of the intersection.

Online example: https://rextester.com/VYNSFX1788 (note that it generates random data, so each time you run it you get a different result)


Why do user defined scalar functions require the schema?

Because that's what Sybase/Microsoft decided when they implemented it. - a-horse-with-no-name

I think when you call any function without schema it only looks for system function or in-built functions, and if you do a built-in function with schema SELECT dbo.getdate() it only looks in user-defined functions. - biju-jose

  The question is why with scalar functions and not the rest?

Probably because when scalar functions were introduced to the product, they could enforce that from the beginning. They couldn't go back and enforce it for other objects because of the amount of existing code that would break. The same reason they've announced deprecation for statements without terminators but in reality that will probably never happen. Remember, too, that the schema prefix wasn't always the schema, it used to indicate the owner.

I was going to offer that it was necessary for the parser to differentiate your intentions in case you create a scalar function that shares a name with a (future) built-in function. Consider SELECT doit() in SQL Server 2017 and then in SQL Server 2021 they add an internal function called doit().

In other cases (table-valued functions), the parser gets more hints about what it is because it is after FROM. But this doesn't hold true anymore, because in the case of STRING_SPLIT() - added in 2016 - if I had a function called string_split() I might be surprised about any behavior change.

You could ask all sorts of "why" questions. Why do some ALTER DATABASE commands have SET <feature> ON and others are SET <feature> = ON? Why do we have both NOWAIT and NO_WAIT? BACKUP TO NUL instead of NULL? All kinds of historical things exist, check-ins with no review, left hand/right hand. The why is usually going to be irrelevant, unknown, or unsatisfactory.

In any case, does "why" really matter? What technical problem does knowing solve? We can't tell you why, you have to ask Microsoft. - aaron-bertrand

Side note: Even though you could omit the schema name for most objects, it is a better practice (when using SQL Server) to add the schema prefix to all objects used. You might see some unexpected or unwanted behaviour when you don't. A blog post about this: Bad habits to kick : avoiding the schema prefix by Aaron Bertrand - randi-vertongen


dynamic sql - assigning variable a value from result of dynamic sql query

You were missing the variable assignment inside the dynamic SQL batch.

DECLARE @sqlcommand NVARCHAR(MAX) = 'SELECT TOP 1 @ID = (ID) --Assign me!
                                     FROM ' + QUOTENAME(@tablename) + '
                                     WHERE LastModified < DATEADD(DAY,' + @NumberOfKeepDays + ', GETDATE())
                                    ORDER BY 1 DESC';

EXECUTE sys.sp_executesql @sqlcommand, N'@ID BIGINT OUTPUT', @ID = @ID OUTPUT;

The example from the sp_executesql doc is pretty clear, though suffers from formatting.

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;

SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber) --Assign me!
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
    @SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 22276;

EXECUTE sp_executesql
    @SQLString
    ,@ParmDefinition
    ,@CustomerID = @IntVariable
    ,@SalesOrderOUT = @SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;

If you want to learn a whole heck of a lot about dynamic SQL, SQL injection (and you should, because what you're executing now is potentially unsafe), head over here


attach database - and rebuild log AT NEW LOCATION

You cannot specify the location for the log file on ATTACH_REBUILD_LOG. Per https://msdn.microsoft.com/en-us/library/ms176061.aspx

  the ATTACH_REBUILD_LOG automatically creates a new, 1 MB log file.
  This file is placed in the default log-file location.

As a workaround what you could do in this instance is change the default location for the log files to a new folder, and attach the data file with the rebuild log. Once that is done set the default location back to it's prior setting. Then you can run and ATLER DATABASE command to set the log file to the path/filename that you desire, set the database offline, copy over the log file to that new location, and set the database online again. After that just clean up the old file and folder.


This fixed it...

run this from cmd line/powershell...

regsvr32.exe "c:\program files (x86)\microsoft sql server\100\dts\binn\dts.dll"

https://connect.microsoft.com/SQLServer/feedback/details/569732/creating-an-instance-of-the-com-component-with-clsid?tduid=(b1c7cdcb47d3ef96443cdf19a57073b3)(256380)(2459594)(TnL5HPStwNw-lo_RDTVjauVtPImia9IDiQ)()


It is OK to have many columns in one table. Indeed, this is the normal situation. It is not OK, however, to mix different types of information in a single table.

Each table models a single class of "thing" in the real world. The rules of normalisation guide a database designer to separate classes from one another. Denormalisation (storing several things in one table) is a legitimate technique but should be used sparingly, with caution, and proper risk mitigation in place.

In your specific case you will have to consider what is your business domain. Are your users interested solely in the balance sheet bottom-line numbers for several corporations over several years (e.g. investment and trading point of view), or do they need the detail of how those numbers are calculated (e.g. an accountancy / book-keeping point of view)? For the former the table you mentions would be OK in my opinion. For the latter you need to model individual transactions, double-entry ledgers etc., and calculate your bottom-line numbers from these as required.


I think you are on the right track. One thing though is that you need to handle the case when there is no match. Assuming someone adds a row like:

(4, 40, 0, 0, 1000000000)

Your IF statement will evaluate to:

IF ( 1000000000 != null ) THEN ...
IF ( null ) THEN ...

i.e. this will be allowed.

If you add a create table statement and some insert statements, together with info on whether they should be allowed or not I'm pretty sure someone can come up with a suggestion.


SQL Server AlwaysOn Availability Group - Creating an Index Online in SSMS

Is the above approach the best way to do this?

Essentially this is the only way to approach this - indexes can only be created by one command. You could execute T-SQL for the CREATE INDEX manually, but the same command is going to be executed on the server regardless (barring any different options you choose, such as online).

As with any production rollout, you should have ideally tested this in a lower environment to get an idea for how long it will take and if it will cause any slow downs in the application - things we can't tell you based on the provided information.

  Will this actually create the index online?

Yes, provided you are using Enterprise edition. Otherwise the index will not be created online. Again, this is something you should have already tested in a lower environment or copy of production.

  While the index is being created, will the cluster remain in a healthy and replicated state

Index creation in general should not impact cluster health, but we also don't know your server specs, how big of an index this is, or a lot of other details that could make this a riskier operation. My advice is  to monitor the index creation if you are concerned at all.

  will the index get created on the secondary automatically

See above Enterprise Edition comment.

  will the incoming reads and writes continue to happen without errors?

If the index is being created ONLINE then the table will not be locked during the process, but still necessitates a very brief schema lock at the first / final stage of creation. Creating an index does have a resource cost though, so you may see slowness in your queries that utilize the targeted table. Which is why you should test this before releasing to production.

There are other index options like MAXDOP which can be used to limit how many resources are utilized by the index creation process.


Stop logging INSERT INTO statements in PostgreSQL (but keep SELECT)

There is no built-in way to log DML statements selectively.

The pgAudit extension does probably what you want, through its pgaudit.log configuration setting:

  Specifies which classes of statements will be logged by session audit
  logging. Possible values are:

READ: SELECT and COPY when the source is a relation or a query.

WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.

FUNCTION: Function calls and DO blocks.

ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

DDL: All DDL that is not included in the ROLE class.

MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.

MISC_SET: Miscellaneous SET commands, e.g. SET ROLE.

ALL: Include all of the above.


Data Relationship between Provinces and Cities

How about this:

province(province_id, province_name, ...)
cities(city_id, city_name, province_id, ...)

This is (one to many) relationship. It is not affected by the fact that more than one city have the same name. Therefore, in cities table, the combination (city_name, province_id) is unique. city_name by itself is not unique.


The standard procedure would be to:

Obtain the page IDs that have to be restored.
Start a page restore with a full database.
Apply the most recent differential backup.
Apply subsequent log backups.
Create new log backup.
Restore the new lob backup.

After the new log backup has been applied, the page restore is completed and the pages are then usable.

Example Restore

RESTORE DATABASE <database> PAGE='1:57, 1:202, 1:916, 1:1016'
   FROM <file_backup_of_file_B>
   WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
   WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
   WITH NORECOVERY;
BACKUP LOG <database> TO <new_log_backup>;
RESTORE LOG <database> FROM <new_log_backup> WITH RECOVERY;
GO

Reference: Restore Pages (SQL Server) (Microsoft Docs)
Reference: RESTORE Statements (Transact-SQL) (Microsoft Docs)

However, you have holes in your TLOG backups, and restoring with above procedure might bring your database back into a state in time that you do not desire.

You are in a complicated situation.

Your database has corrupt pages and your company is constantly adding new data to a database with issues. This could result in a total downtime of the database. Do you want to risk that?
Somebody is going to be held responsible and the more you try to fix it, the more management might be inclined to decide that you might be that person in the end. Do you want to risk that?
You are putting yourself in a difficult situation by taking on a role that you were not employed for. You are trying to achieve something that neither your company DBAs nor your external consultant were capable of. While it may seem to be a noble gesture, you are putting yourself at risk. You might have "implicitly promised" something that you will never be able to fulfil. Do you want to risk that?
When somebody working with the database queries data that is corrupt, they are possibly going to receive an error message. Daily work is already being impacted. The longer you wait with the inevitable the more productivity will be impacted. Do you want to risk that? (This question could also be raised with management)
Your company's backup procedure seems to be faulty (otherwise how would TLOG backups be missing?) and you are still running your production database as if there were no issues. Do you want to risk that?

The best recommendation I can give you is to halt production and call Microsoft!
Or at least call Microsoft and possibly halt production.

While my writing may seem overly cautious and slightly dramatised from your perspective, I can personally relate to an experience as DBA where data was lost in a similar situation. We only lost half a days data, but we had to re-synchronise a lot of data with surrounding systems.

The longer you wait the more expensive recovery could become.

As for the limitation on page restores, here a quote from the official documentation:

  The maximum number of pages that can be restored into any single file in a restore sequence is 1000. However, if you have more than a small number of damaged pages in a file, consider restoring the whole file instead of the pages.

(emphasis mine)

Reference: RESTORE Statements - Arguments (Transact-SQL) (Microsoft Docs)

When all is back to normal, the DBAs and/or external consultants might want to consider implementing a different backup/restore policy/procedure for your database. As it has to be up 7x24 you can't risk having a backup procedure that doesn't provide adequate restore capabilities for any situation.


Looking for a tool to convert between MySql, Sql Server CE, Sql Server and SQLite

You may be able to use Kettle, aka Pentaho Data Integration, to do this.

If not supported natively, someone will have written a plug in for SQL Server CE/SQLLite. Kettle supports any JDBC compliant data source. MySQL and SQL Server are supported of course


Add cumulative sum to time-series query PostgreSQL 9.5

If I get it right you can just add a window function outside of your query like:

SELECT interval_date, campaign_revenue
     , SUM(campaign_revenue) OVER (ORDER BY interval_date)
      + (SELECT SUM(revenue)
         FROM sale_event
         WHERE s.campaignid = $1
           AND s.created < $2
           AND s.event_type = 'session_closed') as total_campaign_revenue
FROM (
    SELECT interval_date
         , coalesce(campaign_revenue,0) AS campaign_revenue
    FROM
        -- generate_series helps fill the empty gaps in the following JOIN
        ...
        interval) results
    ON (results.interval = interval_date)
);

Another option is to apply the window function directly, and use a FILTER clause for campaign_revenue


Need help mariadb optimization for performance configuration

default-storage-engine = MyISAM

Better to be in InnoDB.

You currently have 19GB of InnoDB data, but no setting for innodb_buffer_pool_size?  That VARIABLE should be perhaps 70G for that server.

To dig deeper, let's get a more thorough analysis of variables, plus look for slow queries.  See http://mysql.rjweb.org/doc.php/mysql_analysis


"Keyword not supported: Server" error when using ADO.NET connection string to connect to Azure DB

As marc_s stated in the accepted answer (in the link provided in the question):

  For Entity Framework ... you need to use a special type of connection string which is quite different from the straight ADO.NET connection strings ...

Your provider, as shown in the code in this question, is: System.Data.EntityClient. Yet you are using standard ADO.NET connection string keywords.

You can either:

change the connection string to be similar to what is shown in marc_s's accepted answer,

OR
change the provider name as shown in the #2 answer (by WasiF), which is really just changing EntityClient to be SqlClient


Is Oracle's dbms_utility.get_hash_value algorithm documented do reverse engineered anywhere?

You cannot reverse DBMS_UTILITY.GET_HASH_VALUE. It is simply not possible.

Your problem is different: you have to identify an user thought an compliant LDAP directory (like OpenLDAP). Look for Enterprise Users or LDAP authtication in the Oracle documentation and you will find all you need ...


Always On Primary Replica On Prem, Secondary Replica On Azure

We only want to have 2 replicas, one on-prem and one on azure. Is this possible?

Yes, it's possible. There are extra items you'll want to think about such as having a domain controller (if ad-attached clustering/cluster at all is used) and what type of pipe you'll need, region and latency, load balancer setup, etc., but it's not a hard requirements to have more than two replicas. Most have two replicas locally for HA and the Azure one for DR.

  have read the documentation to use the "Add Azure Replica Wizard" [...]

That wizard no longer works as it was based on the classic Azure model and is no longer available or viable. Disregard using the wizard. You'll want to deploy this via a written script or by hand (Azure web gui) after all of the required infrastructure pieces are decided upon and understood. Obviously setting up a test environment to see how things behave would behoove you.


The primary purpose of MySQL Cluster is not performance -- it's high availability. By making sure every component has a possibility of failover (though not in your case), you ensure that you can remain operating without interruption even through emergencies and crashes. Unless your whole data center goes down, of course.

Though MySQL Cluster also tends to give benefits to performance, because the more storage nodes you have, the more you split up your data. Searching half as much data for a given row is faster. Searching a quarter as much data (if you add two more storage nodes) is even faster.

Though this works best if one of your searches naturally touches data on only one storage node. If you need to run range queries, the query will have to visit many storage nodes, and then gather the results in the MySQL node. This can actually be worse for performance than a conventional, non-cluster architecture.

So it's important to keep in mind that your application and queries have to be designed carefully to take advantage of MySQL Cluster (this also applies to any other sharded architecture). For example, an app that always accesses individual rows, instead of arbitrary sets of rows. If you just port your existing database to MySQL Cluster without this in mind, you can (and likely will) see performance get worse, not better.

One other way MySQL Cluster offers a performance boost: it stores all data in RAM on all the storage nodes. RAM is about 100,000 times faster than disk, so even as you fetch data over a network, it can be a lot faster than accessing a disk in a conventional setup. I worked with a performance architect who said, "remote memory is faster than local disk."


Disable auto-start of postgres server on boot (Mac)

In answer to Prevent PostgreSQL from running at startup on Super User:
ithinkiknowruby wrote:

cd /Library/LaunchDaemons/
sudo rm com.edb.launchd.postgresql-9.0.plist


You can look at some joined system views like dm_exec_query_stats, dm_exec_sql_text and dm_exec_query_plan. The goal is to look what's on cache.

Look for last_execution_time field and search the time you found the problems. You may get an idea of what was going on during that time.


Data File Size Stays Same Whereas Log File Size Is Increasing

Probably because the function FILEPROPERTY reports either whole pages user or even whole extents used (documentation is unclear on which, but that detail doesn't really matter). The point is that storage inside the database file is pre-allocated for data. That means that some storage is pre-allocated and now you have free space that can accommodate some new rows until it has to grow the next time. This pre-allocation inside the database files doesn't really happen in a transaction log file - at least not the level that FILEPROPERTY reports on.


Update row if it has a certain set of values, add new row if it returns to initial value

MySQL is rusty so bear with me

You are on the right path using a staging table to import the CSV file,  but you going to need to break the Inserts and the Update into separate steps

Not stated but i'm going to assume the import may have specific Order show up for each status changed so the order may show up 5 or more times from the Import and that the CSV file is created serially  meaning  PENDING, STARTED,  NOT DONE, (CANCELED, COMPLETE) would appear in that order never in a mixed order.    Cancel Showing before Pending or Start.  If that is the case order the Update may need to fiddled with

Create ImportTable (Serial_ID INT NOT NULL AUTO_INCREMENT,
"columns from the CSV" )

Created an ID field so the order of the CSV is maintained by the import.

Import the CSV. Not sure this is the correct MySQL code to Import and set a column to a different value

LOAD DATA INFILE 'import.csv'
INTO TABLE ImportTable
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@id, "lis of columns ")
SET id =nextval('Serial_ID)  ;

Second step get all the new orders we are going to ignore the status for the first import

Insert into OrderTable
(Select Distinct "columns to insert", 'importing' as OrderStatus
 from ImportTable where Order_id not in
   (Select Order_id from OrderTable )
)

Now have all the orders in the system lets just process the status changes serially
Don't need the CASE statement for  Order_Status unless the CSV order is all random..

DELIMITER $$
CREATE FUNCTION process_import_status() returns bool
BEGIN
DECLARE @(list of vars for COLUMNS to Update)
DECLARE updates_cursor CURSOR FOR
 SELECT "columns to update"
  CASE Order_status
    WHEN 'pending' THEN 1
    WHEN 'started' THEN 2
    WHEN 'not done' THEN 3
    WHEN 'cancelled' THEN 4
    WHEN 'complete' THEN 5
 END as 'sort_order
 FROM ImportTable order by sort_order, Serial_ID, date;

updates_loop  Loop:
  FETCH  updates_cursor INTO @(list of vars for COLUMNS to Update);
  update OrderTable SET "list of columns"= @(LIST of COLUMNS)
     where Order_id = @order_id ;
END LOOP  updates_loop;
END $$$
DELIMITER ;

This should get you close to what you need to do..


Many queries are slower on new hardware and newest version of PostgreSQL

You have changed too many things at once.  First run 9.2 on your new hardware and check the performance. Then upgrade to 9.6 once you get that sorted out.
How did you move the data?  Logically, by using pg_dump and then a restore, or physically by using pg_basebackup (or a cold copy) and then pg_upgrade?

Update finalal Set jsondone=1 Where id=1
The old server runs this at an average of 0.2 ms over 72 iterations. The new server is 1.43ms.

72 iterations of the same id=? value, or consecutive ones, or random ones?  And all in one transaction, or each a separate one?

The new server is showing half the cost (but more startup costs for some reason) so I assume it would be faster, especially on faster hardware.

Those costs are not general time estimates, they are internal accounting used to make reasonable planner choices.  Things for which there are no choices (there is only way to update a row) are not costed at all, but obviously they take actual time to do.  And even with that in mind, you have to use great caution comparing them across versions.  They just aren't meant for that purpose.

I did notice that shared_blks_read and shared_blks_dirtied were about twice as high on the new server. shared_blks_hit were only slightly higher. Maybe a clue in that , but I do not really know that this means.

I think what this means is that your data in the new server is packed more tightly than it was in the old server, probably because you used pg_dump to get the data over rather than a physical copy.  The default fillfactor for tables is 100%, so the rows are packed into a page as many as will fit.
So on the old server when you do an update, there is room to put the new version of the updated row in the same page as the old version.  On the new server, there is no room on the page, so it has to seek out a different page to put the new version of the row on (and then both pages get dirtied).  Which also means it has to update the indexes, so they know where to find the new version.


Creating a unique constraint on a PostGIS 'raster' type column

A unique constraint creates a unique index to implement the constraint. The only index type in PostgreSQL that supports unique indexes is the default b-tree index type; you can't make a unique GIN or GiST index, e.g.:

regress=> create unique index indexname on test USING GiST(id) ;
ERROR:  access method "gist" does not support unique indexes

Data types must specifically support different kinds of indexing, and this data type doesn't seem to support b-tree indexes. So you can't make a unique constraint on this type.

Details:

There are a few index types used by PostgreSQL. Types must provide an operator class for each index type they support. For your purposes there are really two groups of interest:

b-tree - the default, what you get when you don't say the index type. Supports equality, less-than, and greater-than operations. Supports creation of unique indexes.
GiST and GIN - popular flexible index types, used heavily by PostGIS, fulltext search, etc. Does not support creation of unique indexes. Supports more kinds of comparisons than b-tree.

Most data types support b-tree indexing, which is the default, by providing a b-tree operator class.

Some data types, especially those used in GIS, don't provide a b-tree opclass, either because nobody wanted one, or because it doesn't make much sense.

This type seems to be one of them. Nobody's added b-tree support for the raster type, so you can't create a b-tree index on it. As only b-trees are supported for unique indexes, and a unique constraint creates a unique index to implement the constraint, that means you can't make a unique constraint on a raster type.

You might find that a newer PostgreSQL/PostGIS version has the desired opclass. Or might not. I haven't checked. Either way, you're out of luck in 9.1.

Lets try creating an operator class for b-tree support of the raster type. The docs on operator classes and families shows the strategy numbers for the b-tree index strategy, showing that we need support for =, <, <=, > and >= for raster. Do we have those?

regress=# select oprname from pg_operator where oprname IN ('=', '<=', '<', '>', '>=') and oprleft = 'raster'::regtype and oprright = 'raster'::regtype;
 oprname
---------
 =
(1 row)

Nope. So we can't create a b-tree operator class for raster without first implementing ordering operators for it and making sure that those operators follow the required rules about transitivity, commutivity, etc.

That's not overly surprisingly, since it's not clear what exactly raster < raster would actually mean. It does, however, mean you can't enable unique index support easily for raster.

Another possibility would be to hash the input with md5 or similar, then create a unique index on the hash. This solves the index operator type issues and the problems with maximum b-tree entry sizes.

Creating indexes on hashed values is well documented elsewhere.


When does sp_executesql refresh the query plan?

The line from MSDN is talking about using EXEC(), like this:

SET @sql = 'SELECT foo FROM dbo.bar WHERE x = ''' + @x + ''';';
EXEC(@sql);

In my testing, modern versions of SQL Server are still able to reuse a plan like this, but there may be other variables (such as version, or for example if you add conditional WHERE clauses based on the presence of certain parameters - in which case that will generate a different plan).

If you use sp_executesql then the parameter values can still cause parameter sniffing issues (just like with normal SQL), but this has nothing to do with whether SQL Server can re-use the plan. This plan will get used over and over again, just as if you hadn't used sp_executesql at all, unless variables that would cause a direct query to get recompiled, in which case this one will get recompiled too (essentially, SQL Server doesn't store anything with the plan that says "this was executed from sp_executesql, but this one wasn't):

SET @sql = N'SELECT foo FROM dbo.bar WHERE x = @x;';
EXEC sys.sp_executesql @sql, N'@x varchar(32)', @x;

As a bonus, this has built-in protection against dynamic SQL, and avoids you having to worry about doubling up single quotes due to string delimiters. I blogged about some of this here and please read up on SQL injection here and here.

If you are having issues with plan re-use and/or parameter sniffing, some things you should look into are OPTION (RECOMPILE), OPTIMIZE FOR, optimize for ad hoc workloads and simple/forced parameterization. I addressed a few similar questions in response to a recent webcast here, it may be worth a skim:

Follow-up on Summer Performance Palooza 2013

The gist is: don't be afraid to use sp_executesql, but only use it when you need it, and only spend energy over-optimizing it when you have an actual performance issue. The example above is a terrible one because there's no reason to use dynamic SQL here - I've written this answer assuming you have a legitimate use case.


Which column to trust about the event order in a profiler result?

Taken from Microsoft's SQL Server PSS Blog:

  The Event Sequence is the order the trace event was internally created in the server which can be different from the physical file order due to various timing and design issues.   The Event Sequence gives you a more accurate representation of the event order.  When looking at trace data from SQL Profiler or one loaded in the database you should order by the Event Sequence.


Using CoreData on a client-side application

It depends, as XML is not an RDBMS.

SQLite allows the querying of relations. XML (and JSON) allow for the navigation of hierarchies. Completely different ontological mappings of reality.

If you need an RDBMS backend, SQLite is for you. If you need record storage without any sophisticated query requirements, XML may work, depending on your language.


Your requirement of having a database accessible from many remote locations is not uncommon.

If I were to do this, I would build a Windows Communication Framework (WCF) service that provides access to the database indirectly.  The WCF Service typically resides on a web server, where it operates as a proxy for the SQL Server.  The WCF service could provide security for the database by various authentication mechanisms; that is beyond the scope of this answer.

For information on WCF, check these documents:

http://msdn.microsoft.com/en-us/library/ms731082%28v=vs.110%29.aspx

http://www.codeproject.com/Articles/42643/Creating-and-Consuming-Your-First-WCF-Service

http://code.msdn.microsoft.com/Create-a-WCF-Service-c3486993


Delete unused indexes from Microsoft Dynamics CRM Application

I  don't know if this is the right place to ask a question about
  Microsoft CRM but, is it safe to drop those indexes with 0 seeks,
  lookups and scans with tons of updates and rows?

That will depend on multiple factors only you will know. (Both SQL Server & Microsoft
Dynamics CRM related)

SQL Server

When was the server last restarted?

Make sure this index usage data has enough history to go on as it will reset after each instance restart.

Also, since you are using sql server 2012, the data could have been reset after an index rebuild if your version is below SP2+CU12 or SP3+CU3. (Which hopefully is not the case)

More on that in this blogpost by Kendra Little

If either the data is reset or the restart was recent, consider logging the information to a table and reporting on these results instead.

You  could opt to do this regardless.

Are there rarely used queries that need these indexes?

This is harder to track and is also more unlikely, but could still happen.

For more concerns in general to dropping indexes, read this and this or many more  (online) sources.

Microsoft Dynamics CRM

Is it an out of the box or user created index?

According to this post there are indexes you can and can't remove.

  ... Be careful not to create too many indexes, which can affect the insert
  and update performance. Balance indexing needs according to business
  requirements, indexes that have a lot of writes and no reads (or very
  few) are not efficient and are considered a burden to the system
  (redundant indexes).  So it is a good idea when working with missing
  indexes to give it some time (depending on your operations cycle maybe
  a month) and off set them against a redundant indexes query.  However,
  please remember removing or editing OOB indexes is NOT supported.

This simply means that you cannot drop the indexes that are included by MS Dynamics CRM (Out of the box).

You can drop your own / user created indexes after veryfing these are, in fact, user created indexes.

As always, document any changes you make to these user created indexes and monitor the impact.


How do I modify the user interface language in SSMS?

To change the language in Microsoft SQL Server Management Studio 2014:

> Tools > Options... > Environment > International Settings > Language


Linked Server to SQL Server 2000 server from SQL Server 2012

Be careful when using SQLNCLI10 as described in connect item

A workaround can be found here

You have to first test the application that is using such SP's from 2012 to 2000. It also depends on where both server's are located and how much data you are pulling out using Linked Servers.

Is there a reason that the other referenced databases cannot be moved to 2012 ?


What exactly happens during Database Mirroring? What about during a fail over?

When you do a failover, a role reversal happens i.e. your primary becomes secondary and your secondary becomes primary.

ALTER DATABASE [yourDatabase]
SET PARTNER FAILOVER
GO

Doing so, only the role reversal happens (this is what happens when you initiate a failover using above command) and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN. Refer to my answer : If I fail over one database, do the others that share the same mirror endpoint fail over as well? for more details.

You can have your application use Failover Partner=your_mirror_serve_name, so that your application is redirected to replica database when a failover happens.

Refer to : connectionstring in Database Mirror setup


By default, MySQL allows up to 150 connections, and each connection is handled by a separate thread. Reduce max_connections to, say, 50 and you'll see the number of threads go down.
Some memory is allocated per thread, other in a pool shared between all the threads. The defaults are generally sensible. If you are not using MyISAM tables, you can reduce key_buffer_size to 1M. Adjust innodb_buffer_pool_size based on the size of your InnoDB data. As a rule of thumb, at the very least your primary keys should fit into the buffer pool, but normally you would want more buffer pool than that.


Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted?

Bulk Insert operations in the manner you are describing are not supported for encrypted column via SSMS.

Please refer to this (https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/28/encrypting-existing-data-with-always-encrypted/) article to migrate your existing data to Always Encrypted

Also, please note that doing bulk inserts through a C# (.NET 4.6.1+ client) app is supported.

You can do this in c# using SqlBulkCopy specifically using SqlBulkCopy.WriteToServer(IDataReader) Method. I am assuming you are trying to load data from csv file to a table with encrypted column (say encryptedTable). I would do the following

Create a new table (say unencryptedTable, for security purposes, you might consider creating this table in a local sql server instance) with the same schema without any column encryption.
Load the csv data into unencryptedTable, using the method that you described in the question
Do select * from unencryptedTable to load the data in a SqlDataReader then use SqlBulkCopy to load it to the encryptedTable using SqlBulkCopy.WriteToServer(IDataReader) Method

If you have additional questions regarding this, please post questions in the comment section and I will try my best to address them :)


I have an interesting idea for you.

Create the following indexes

ALTER TABLE operations
    ADD INDEX `date_zoneId_statusId_ndx`    (`date`,`zoneId`,`statusId`)
   ,ADD INDEX `date_machineId_statusId_ndx` (`date`,`machineId`,`statusId`)
   ,ADD INDEX `date_shiftId_statusId_ndx`   (`date`,`shiftId`,`statusId`)
   ,ADD INDEX `zoneId_statusId_date_ndx`    (`zoneId`,`statusId`,`date`)
   ,ADD INDEX `machineId_statusId_date_ndx` (`machineId`,`statusId`,`date`)
   ,ADD INDEX `shiftId_statusId_date_ndx`   (`shiftId`,`statusId`,`date`)
;

You could then evaluate your queries by running:

EXPLAIN SELECT zoneId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX')
GROUP BY `zoneId`,`statusId`
ORDER BY NULL\G

EXPLAIN SELECT machineId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX') GROUP BY `machineId`,`statusId`
ORDER BY NULL\G

EXPLAIN SELECT shiftId, statusId, COUNT(*) as intervals
FROM `operations`
WHERE (`date` > 'XXXX-XX-XX' AND `date` <= 'XXXX-XX-XX')
GROUP BY `shiftId`,`statusId`
ORDER BY NULL\G

The output from each EXPLAIN will plainly tell you which index.

It will also show the least number of steps the Query Optimizer will take.

My guess is that the indexes with the date column last will be selected. An exception may be a small date range which may favor the indexes with the date column first. In that instance, you would want to keep all 6 indexes.

The indexes with the date column last would be used by the GROUP BY so that no temp table sorting would be necessary.

WARNING ON HAVE MANY INDEXES : Bulk INSERT performance would suffer due to managing multiple covering indexes, but SELECT performance would improve greatly. Dropping unnecessary indexes becomes a necessity.


How can I determine the edition an Oracle session is using?

Query V$SESSION and include the column SESSION_EDITION_ID which contains the value you are looking for.

The link http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223 defines all the column meanings for that dynamic view; I would probably do a query like

SELECT SID, USERNAME, SCHEMANAME, OSUSER,
   PROCESS, MACHINE, TERMINAL, PROGRAM,
   SESSION_EDITION_ID
from V$SESSION
order by SESSION_EDITION_ID;


You effectively have a 2-column ordering.  So, when "remembering where you left off", you must include both columns.  Then, the > becomes more complex.  I discuss that in a couple of places:
Should I store data pre-ordered rather than ordering on the fly?
http://mysql.rjweb.org/doc.php/deletebig#iterating_through_a_compound_key
General discussion of avoiding OFFSET for pagination:  http://mysql.rjweb.org/doc.php/pagination
Granted, the use of FIELD() adds a wrinkle.  But not a big wrinkle.  Simply use the function call instead of a column name in a few places.


How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline?

One good way to pull off something of that nature is to set up Master-Master Replication or Circular Replication. This is not to be confused with MultiMaster Replciation.

Setting up Circular Replication is actaully very easy if you have setup  Master-Slave Replication. Here is what you need to do in order to configure it.

For this example, we will assume Master-Slave Replication is active but you will experience a bit of downtime (1-2 minutes):

Step 1) Add this line to /etc/my.cnf on the Master.

log-slave-updates

Step 2) Add these line to /etc/my.cnf on the Slave:

log-bin=mysql-bin (or have whatever the master has for this)
log-slave-updates

WARNING : Here is the brief moment of downtime !!!

Step 3) On the Slave, service mysql restart

This will activate binary logs on the Slave

Step 4) On the Master, service mysql stop

Step 5) Use rsync to copy the /var/lib/mysql folder of the Slave to the Master.

WARNING : Here is the longer moment of downtime !!!

Step 6) On the Slave, service mysql stop

Step 7) On the Slave, find out the last binary log

Step 8) On the Slave, find out the filesize of the last binary log

Step 9) Use rsync to copy the /var/lib/mysql folder of the Slave to the Master. This should be a faster copy.

Step 10) On the Master, edit
Line 2 of master.info with the last binary log of the Slave.
Line 3 of master.info with the filesize of the last binary log of the Slave.
Line 4 of master.info with the IP of the Slave.
Line 5 is the userid of replication user (DO NOT TOUCH)
Line 6 is the password of replication user (DO NOT TOUCH)

Step 11) Delete all binary logs and binary log index file of the Master.

Step 12) On the Slave, service mysql start, wait 15 seconds

Step 13) On the Master, service mysql start

Step 14) On the Master, run STOP SLAVE; SHOW MASTER STATUS;

Step 15) On the Slave, run CHANGE MASTER TO MASTER_HOST='IP of Slave',MASTER_USER='userid of replication user from Step10',MASTER_PASSWORD='password of replication user from Step10',MASTER_LOG_FILE='binary log from Step14',MASTER_LOG_POS=LogPos from Step14.

Step 16) On the Slave, run START SLAVE;

Step 17) On the Master, run START SLAVE;

I performed steps similar to this for another StackExchange question I answered.

Give it a Try !!!


What is the proper way of designing database with dynamic fields in MySQL + PHP?

Its possible to use type JSON

https://dev.mysql.com/doc/refman/8.0/en/json.html

this negates the need for a table of custom fields, however if you need to do join/look ups on this data, it will be difficult.


How do I reclaim reserved space in the sysjobstepslogs table

You could try just rebuilding the table:

ALTER TABLE dbo.sysjobstepslogs REBUILD;

If you're on Enterprise (or Evaluation or Developer) you can do:

ALTER TABLE dbo.sysjobstepslogs REBUILD WITH (ONLINE = ON);

The table only has a single clustered index (PK_SomeAwfulGUID-basedName) and this rebuild operation should deallocate all of the pages that might still be associated with the table.


Do you have to run nodetool repair on every node?

In looking through the documentation, I am having a hard time finding a reference for this, but the short answer is "yes" you need to run nodetool repair on each node in your cluster.  The closest I can find is the documentation on repairing nodes which suggests that you should not run repair on more than one node in your cluster at a time.

You can also run repair with the -pr flag, which limits the repair operation to the first token range that current node is responsible for.  This cuts down on the duplication of work when run on the remaining nodes.


Impersonate user or login mapped to certificate

Logins and Users created from Certificates and Asymmetric Keys cannot be impersonated. They are merely proxies for a set of permissions that will be added to any module that is signed with the same Certificate or Asymmetric Key.

Also, it doesn't really make much sense to impersonate these Logins and Users because:

these proxy permissions are added to the current security context (of an executing module that has been signed) instead of replacing the security context, which is what Impersonation does. It is an entirely different approach, not just a different mechanism to essentially the the same thing.
module signing is not meant for ad hoc queries. Part of what makes module signing a more secure approach than Impersonation is that the person granting the elevated permissions (by signing the module to associate it with an additional set of permissions) knows what the code is doing, and that once the code is given the elevated permissions, it cannot be altered to do something else, such as something that the person doing the module signing wouldn't approve of. This is handled by signatures being dropped when any change is made to a signed module, and the fact that the text of the module (including the optional EXECUTE AS clause of the CREATE statement) is used to create the signature. If the text changes, then the signature won't match.

In the end, using Logins and/or Users created from Asymmetric Keys and Certificates replace the need for Impersonation.

Still, if looking for a more official indicator, the MSDN page for CREATE USER states:

  Users that cannot authenticate These users cannot login to SQL Server or SQL Database.

  User without a login. Cannot login but can be granted permissions. CREATE USER CustomApp WITHOUT LOGIN;
  User based on a certificate. Cannot login but can be granted permissions and can sign modules. CREATE USER TestProcess FOR CERTIFICATE CarnationProduction50;
  User based on an asymmetric key. Cannot login but can be granted permissions and can sign modules. CREATE User TestProcess FROM ASYMMETRIC KEY PacificSales09;

However, not being able to login/authenticate does not mean "cannot impersonate", given that Users without a login are in that list, and you can do EXECUTE AS USER='{user_without_login}';.

The MSDN page for CREATE LOGIN states:

  Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server.

While the second sentence repeats what the CREATE USER documentation says, the first sentence is a bit more specific: they are only for code signing.

Finally, the statement of restrictions that you found in the documentation for both EXECUTE AS and the EXECUTE AS clause are correct, even though they are worded in a confusing manner. They state (bold emphasis added):

  name must be a singleton account, and cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

That quote was taken from the EXECUTE AS documentation, and the EXECUTE AS Clause documentation (nearly the same wording) is quoted in the question. While both of those pages do use the terms "certificate" and "key", they are really meaning "Certificate-based Login/User" and "Asymmetric Key-based Login/User" respectively. I am assuming this based on the fact that the list of principal types given are all found in sys.server_principals (for Logins) and sys.database_principals (for Users), and neither Certificates nor Asymmetric Keys would be listed in either of those system catalog views.


Is Reorganizing Indexes required after ShrinkDatabase or can I just reogranize index with fragmentation more than 10%?

I would recommend you to remove -- shrink database, as it will cause more fragmentation. Basically you are doubling your work by performing Shrink database.

Read this : http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Also, I would suggest to use Ola's maintenance scripts which are "the best" out for SQL Server community http://ola.hallengren.com/

You should never reorganize or rebuild all your indexes as its a costly operation and should be done during maintenance window, rather depending on the level of fragmentation
e.g.
less than 10% ==> dont do anything
10 - 30 % ==> do a reorganize
30% an above ==> rebuild


How to sort categorical values by an arbitrary order?

This can be done using a CASE statement in the ORDER BY clause:

WITH
Animals (name, category) AS (
 SELECT 'fluffer', 'CAT' FROM DUAL UNION ALL
 SELECT 'tooter', 'ELEPHANT' FROM DUAL UNION ALL
 SELECT 'tooter_jr', 'ELEPHANT' FROM DUAL UNION ALL
 SELECT 'woofer', 'DOG' FROM DUAL
)
SELECT
  name, category
FROM Animals
ORDER BY
  CASE category
  WHEN 'CAT' THEN 1
  WHEN 'ELEPHANT' THEN 2
  WHEN 'DOG' THEN 3
  END
;

Please comment, if and as this requires adjustment / further detail.


Convert SQL Server query to MS Access syntax

The only issue here is that MS Access has no idea what a CASE expression is. Instead, you need to use IIF, e.g.:

IIF(Credit-Debit > 0, Credit-Debit, NULL) AS Credit,
IIF(Credit-Debit < 0, Credit-Debit, NULL) AS Debit,

BTW IIF is now supported in SQL Server 2012.


Take a look at my answer here - you could take a similar query and use MySQL events (see here) to add rows to an aggregation table on an hourly basis - say at 5 minutes past the hour, perform a query for the previous hour's clicks and hits - that way you'd only be querying an hour at a time and I presume that the performance would be OK for a small subset of your results. Since it's logging you're doing, any logged data isn't going to change, so querying 1 hour at a time makes sense - i.e. (pseudocode)

SELECT HOUR<my_time_field> AS Hour, <my_aggregates>
FROM <my_table>
WHERE MyTime
BETWEEN (Now - 65mins) AND (Now-5mins);
GROUP BY Hour

Something like that might be a solution to your issue?


About single threaded versus multithreaded databases performance

If there is one thing I can say about MySQL is that InnoDB, its transactional (ACID-compliant) storage engine, is indeed multithreaded. However, it is as multithreaded as YOU CONFIGURE IT !!! Even right "out of the box," InnoDB performs great in a single CPU environment given its default settings. To take advantage of InnoDB multithreading capabilities, you must remember to activate a lot of options.

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. UPDATE : As I learned firsthand from the Percona NYC Conference, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.

innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.

innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.

innodb_read_ahead_threshold allows linear readings of the set number of extents (64 pages [page = 16K]) before switching to asynchronous reading.

Time would escape me if I named more options. You can read about them in MySQL's Documentation.

Most people are unaware of these features and a quite satisfied with InnoDB just doing ACID-compliant transactions. If you tweak any of these options, you do so at your own peril.

I have played with MySQL 5.5 Multiple Buffer Pool Instances (162GB in 9 buffer pools instances) and have attempted to have data auto-partitioned in memory this way. Some experts say that this should give you 50% performance improvement. What I got was a ton of thread locking that actually made InnoDB crawl. I switched to 1 buffer (162GB) and all was well again in the world. I guess you need Percona experts at your disposal to set this. I'll be at the Percona MySQL Conference in New York tomorrow and will ask about this if opportunity affords itself.

In conclusion, InnoDB behaves well now in a multi CPU server given its default settings for multithreaded operations. Tweaking them takes great care, great patience, great documentation, and great coffee (or Red Bull, Jolt, etc.).

Good morning, good evening, and good night !!!

UPDATE 2011-05-27 20:11

Came back from Percona MySQL Conference in New York on Thursday. What a conference. Learned a great deal, but I got an answer I will look into concerning InnoDB. I was informed by Ronald Bradford that setting innodb_thread_concurrency to 0 will let InnoDB decide the best course of action internally with thread concurrency. I will experiment with this further in MySQL 5.5.

UPDATE 2011-06-01 11:20

As far as one long query goes, InnoDB is ACID-compliant and operates very well using MultiVersion Concurrency Control. Transactions should be able carry isolation levels (repeatable reads by default) that prevents blocking others from accessing data.

As for multi core systems, InnoDB has come a long way. In the past, InnoDB could not perform well in a multicore environment. I remember having to run multiple mysql instances on a single server to get the multiple cores to distribute the multiple mysqld processes across the CPUs. This is no longer necessary, thanks to Percona, and later MySQL (eh, Oracle, saying that still makes me gag), as they have developed InnoDB into a more mature storage engine that can access the cores with simplicity without much tuning. The current instance of InnoDB today can operate well in a single core server.


MySQL views and table locking (5.1 MyISAM)

Keep in mind that SELECTs and UPDATEs are mutually exclusive for MyISAM. That's is to say...

UPDATEs, DELETEs, and INSERTs require a full table lock. SELECTs are blocked.
SELECTs prevent subsequent UPDATEs, DELETEs, and INSERTs from acquiring a full table lock.

Using either SELECT or UPDATE automatically mitigate MyISAM locking behavior, anyway. So, the answer to your actual question would be yes since we are dealing with MyISAM.
I would recommend you use LOCK TABLES on the view. Why ?
According to the MySQL Documentation

For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically. If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly, as described in Section 13.3.5.2, “LOCK TABLES and Triggers”.

Based on this, you should explicitly run LOCK TABLES on the view. The locks are performed on all the underlying tables then. That way, all the tables are locked as a group.
SUGGESTIONS

Make the all the underlying tables InnoDB if you want and if you can.
INSERTs to the underlying MyISAM tables can still happen if concurrent_insert is set to 2.


Why is this query on JSON values in SQL Server using an index scan instead of an index seek?

The issue is that JSON_VALUE returns an NVARCHAR and you sent in an INTEGER. This causes a Convert_Implicit.
If you pass in a VARCHAR or NVARCHAR param/value into the WHERE condition, it does a seek and drops the rows read.


Is it acceptable to have a surrogate/primary key of one table be the primary/foreign key of another table?

If you are using composite primary keys, your foreign key references would also need to be composite keys.  If the primary key of building is (buildingId, buildingFullName) then buildingRoom would need to have both a buildingId and a buildingFullName that would be the foreign key to building.  Similarly, if (buildingId, buildingRoom, roomId) is the primary key of buildingRoom, you would need all three of those columns in buildingRoomAsset in order to create the foreign key.  Defining the foreign keys this way doesn't make sense.

If you are adding a surrogate key to the table, you would almost always want to define that column and that column alone as the primary key of the table.  You can add additional unique constraints to ensure that things like the buildingFullName are unique within the table.  Adding the buildingFullName to the primary key not only forces the buildingFullName to be pulled through to all the child tables, making it much more difficult to update the data, it means that you can have many buildings with the same name which seems unlikely to be what you're after.

Additionally, I would strongly suspect that you want an asset table that stores information about assets and then a roomAsset table that maps a roomId to one or more assetId values.  That will make life much easier when an asset gets moved from one room to another among other use cases.

If it were me

building
------------------
+ buildingId <<SK>> <<PK>>
+ buildingFullName

room
------------------
+ roomId <<SK>> <<PK>>
+ buildingId <<FK>>
+ roomNumber

asset
------------------
+ assetId <<SK>> <<PK>>
+ assetName

roomAsset
------------------
+ roomAssetId <<SK>> <<PK>>
+ roomId <<FK>>
+ assetId <<FK>>


Linking tables to extract value based on closest previous date

You can use a (CROSS) APPLY operator for this purpose. But please have a look at data you have supplied, there is some incoherence in these dates.

CREATE TABLE TOC (Location VARCHAR(10), StatDate DATETIME, TOC INT);
INSERT INTO TOC VALUES
('WC1',  '19000901', 5600),
('WC1',  '20101001', 5605),
('WC2',  '19800802', 4300),
('WC2',  '20050905', 4280),
('WC2',  '20111006', 4285);
GO

CREATE TABLE WLEVELS (Location VARCHAR(10), MeasurementDate DATETIME, DepthToWater DECIMAL(5,1));
INSERT INTO WLEVELS VALUES
('WC1', '20060726', 5.5),
('WC1', '20080930', 5),
('WC1', '20101005', 6),
('WC2', '20100730', 8),
('WC2', '20151103', 8.5);
GO

SELECT
    Location,
    MeasurementDate,
    DepthToWater,
    TOC,
    (TOC - DepthToWater) AS Elevation
FROM
    WLEVELS
CROSS APPLY (SELECT TOP 1
                 TOC
             FROM
                 TOC
             WHERE
                 TOC.Location = WLEVELS.Location
                 AND StatDate <= MeasurementDate
             ORDER BY
                 Location, StatDate DESC) TC;
GO

Location | MeasurementDate     | DepthToWater |  TOC | Elevation
:------- | :------------------ | :----------- | ---: | :--------
WC1      | 26/07/2006 00:00:00 | 5.5          | 5600 | 5594.5
WC1      | 30/09/2008 00:00:00 | 5.0          | 5600 | 5595.0
WC1      | 05/10/2010 00:00:00 | 6.0          | 5605 | 5599.0
WC2      | 30/07/2010 00:00:00 | 8.0          | 4280 | 4272.0
WC2      | 03/11/2015 00:00:00 | 8.5          | 4285 | 4276.5

db<>fiddle here


I think this is the  original idea.

First thing to notice is that the PK on the LineItem table has three attributes {CustomerID, CustomerOrderNo, OdrerItemNo}, as opposed to just two in your example.

Second thing to note is the confusion resulting from the use of the generic id name for an attribute.

The CustomerOrderNo should ideally be (1,2,3..) for each customer and OrderItemNo (1,2,3 ...) for each order.

Well, this is nice if possible, but requires a query looking for the previous max value, like

select max(CustomerOrderNo)
from Order
where CustomerID = specific_customer ;

which is often not preferred in high-transaction-volume environments, so it is common to see these replaced by an auto-increment, essentially serving the same purpose. It is true that this auto-incremet is now unique, hence it can be used as a KEY -- but you may choose to look at it as a necessary compromise for the OrderItemNo.

So, with some renaming CustomerOrderNo -> OrderNo and OrderItemNo -> ItemNo you may arrive to this model

So now if you look at the Order the following are unique

{OrderNo}             -- PK
{CustomerID, OrderNo} -- superkey,  AK on the diagram.

Note that {CustomerID, OrderNo} is propagated to the LineItem to serve as a FK.

If you squint a bit, this is close to your example, but with PKs {ItemNo} and {OrderNo} only -- as opposed to two column PKs from your example.

Now the question is, why not simplify to something like this?

Which is fine, but introduces PATH DEPENDENCE -- you can not join LineItem with Customer directly, must use Order in the join.

I prefer the first case when possible -- you choose your favourite. And obviously, there is no need for direct FK from LineItem to Customer in these three cases.


An entitiy with at least one weak entity

As was mentioned in the comments, there could be name duplicates. It's better to have an id column in each table.

Second, adding NOT NULL constraint on the foreign key will enforce the rule that each child can have only one parent

Create Table Parent(
  id int NOT NULL UNIQUE,
  parentName Varchar(255),
  -- Should a parent have an entity named child? -- No.
  -- But a parent can have more than one child
  PRIMARY KEY(id)
);

Create Table Child(
 id int NOT NULL UNIQUE,
 parent_id int NOT NULL, -- NOT NULL - each child has a parent
 name varchar(255),
 PRIMARY KEY(id),
 FOREIGN KEY (parent_id) References Parent(id)
);

There's only one parent_id field, so each child has no more than one parent

Because each child can have only one parent (one-to-many) relationship, the two-table solution works.

When you have many-to-many relationship, you need a relationship table that contains foreign keys to both tables that represent entities in this relationship.

You might also want to add another UNIQUE to (parent_id, name) combination, to prevent one parent have multiple children with the same name. (Though, I have heard that some celebrity named all his sons "George")

You can't really enforce that each parent has at least one child, because then you wouldn't be able to add new parents to your database. Current process is:

insert a parent
insert that parent's children

You can't insert children first, because the constraints will prevent the insert of a child without a parent already existing.


Domain group hierarchical group membership and SQL Logins mapped to AD groups

This answer may not be of any real value since now the question becomes WHY is there a lag...

but the AD changes were done 3 days ago and in my particular case, it took 3 days before SQL Server started recognizing the new members/groups added.

As of last night, when i executed EXEC xp_logininfo 'DOMAIN\User1','all' it returned nothing.. today (4th day since change) it shows the permission path as 'DOMAIN\GroupA'.


MySQL insert with value derived from primary id

Plan A:  Use a TRIGGER to compute the value for folder.

Plan B:  Have folder be a virtual generated column.


How to UPDATE data without locking every record the search encounters?

UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3  = 3;

Needs this composite index:
INDEX(col1, col2, col3)  -- in any order

So, if you start it with col1, col2 (in either order), that index will also work nicely for
UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999;

If, by "We have indexes on all the columns" you mean single-column indexes, then those are wasted and in the way.  MySQL will use only one index.  It will pick among (col1), (col2), (col3) looking for the most selective.  Then it will scan all the rows with, say col1 = 1.  This is likely to be a lot more rows than you need to UPDATE.
With the composite index I recommend, it will go straight to the row(s) that match all of the WHERE clause.  This will be many fewer rows, thereby decreasing the likelihood of a deadlock.  Also, it will be much faster, which also decreases the chance of a deadlock.
Regardless, your could should check for errors and re-run any transaction that hits a deadlock.


DBCC FREESYSTEMCACHE(‘SQL Plans’) deletes all ad-hoc and prepared plans, not just single-used ones

Try this:

DECLARE @plan_handle varbinary(64)

DECLARE db_cursor CURSOR FOR
SELECT plan_handle
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @plan_handle

WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC FREEPROCCACHE (@plan_handle);
    FETCH NEXT FROM db_cursor INTO @plan_handle
END

CLOSE db_cursor
DEALLOCATE db_cursor


Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

I have very bad news for you.
You should not have deleted the ibdata1 file. Here is why:
ibdata1 contains four type of information:

table metadata
MVCC data
data pages (with innodb_file_per_table enabled)
index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.
I also have very good news for you along with some bad news.
It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).
Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.
To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.
I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.
UPDATE 2011-10-17 06:19 EDT
Here is your original innodb configuration from your question:
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

Please notice that innodb_log_file_size is there twice. Look carefully...
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;. Mystery solved !!!
UPDATE 2011-10-17 11:07 EDT
The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:
110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).
For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.


Monitoring free space in SQL Server data files

Not sure why you want to use performance counters for this when you can get it from a simple query. And in fact while you can get this information about log files from performance counters (Log File(s) Size (KB) / Log File(s) Used Size (KB)), there is no such counter for how much space is used in a data file.

;WITH f AS
(
  SELECT name, size = size/128.0 FROM sys.database_files
),
s AS
(
  SELECT name, size, free = size-CONVERT(INT,FILEPROPERTY(name,'SpaceUsed'))/128.0
  FROM f
)
SELECT name, size, free, percent_free = free * 100.0 / size
FROM s;


Error when using views on a linked server

This is a bug, though one that only arises in very specific circumstances.

SQL Server uses a component called Distributed Query (DQ) to build T-SQL commands to run against the linked server. The optimizer transforms a CASE expression including a sub-select to a form that works perfectly well on local objects, but which is not translated correctly to remote query form (in this particular circumstance).

The bug results in a remote node being generated that requires the result of local computations. An internal query tree is created that expects these computed values as parameters, but no parameters are generated. This bug only occurs if the sub-selects are not correlated to the outer query (as is the case here), because a simplification that is only safe for a local query is applied.

DQ generates the remote query with parameters (using standard ? syntax), but no parameters are passed, causing the "statement cannot be prepared" error (a prepared statement is the technical name for a parameterized statement).

If subquery contained an outer reference (correlation) instead of literal values, the local-only simplification would not be applied, and the query tree would be correct.

Reproduction

Create a linked server SQL2008 to another instance of SQL Server 2008, which contains the AdventureWorks sample database. Create the following views on the local instance:

CREATE VIEW dbo.Product AS
SELECT *
FROM SQL2008.AdventureWorks.Production.Product AS P;
GO
CREATE VIEW dbo.TransactionHistory AS
SELECT *
FROM SQL2008.AdventureWorks.Production.TransactionHistory AS TH;

Generate an estimated execution plan for the following query (running it will just produce the error message, of course):

SELECT
    P.ProductID
FROM dbo.Product AS P
WHERE P.ProductID IN
(
    SELECT -- Comment this
        CASE
            WHEN EXISTS
                (
                    SELECT *
                    FROM dbo.TransactionHistory AS TH
                    WHERE TH.ProductID = 1 -- Not correlated
                )
            THEN
                (
                    SELECT TOP (1)
                        TH.TransactionID
                    FROM dbo.TransactionHistory AS TH
                    WHERE
                         -- Not correlated
                        TH.ProductID = 1
                    ORDER BY
                        TH.TransactionID DESC
                )
            ELSE 0
        END
);

The estimated query plan below highlights the problem operator:

The Remote Query property of that operator is:

SELECT "Tbl1001"."ProductID" "Col1019"
FROM "AdventureWorks"."Production"."Product" "Tbl1001"
WHERE CASE WHEN ? THEN ? ELSE (0) END = "Tbl1001"."ProductID"

Notice the ? parameter markers. The unresolved parameters generate the second error message when the remote server tries to compile WHEN ? THEN ?.

If you comment out the marked SELECT, the transformation in question is not applied, and the erroneous remote query is not generated.

Equally, replacing either of the TH.ProductID = 1 instances with a correlation, for example TH.ProductID = P.ProductID also generates correct remote queries.

This bug reproduces in all current versions of SQL Server, up to and including SQL Server 2014 RTM CU4.


SQL Server - How to protect against SQL Injection when dealing with a Dynamic WHERE clause passed to a Stored Procedure

You simply don't write it in such a way that a user can pass in a structured WHERE clause. This is a recipe for disaster, and I bet at least half of the companies who have been exploited by SQL injection thought they were protected by checking the input for keywords, stripping out semi-colons and comments, etc. They weren't, and there will always be ways around whatever protection you try to manually write.

Don't be lazy. Write it so that the user picks the possible columns and operations from drop-downs, and only enters the parameter values into free text. Then you construct the dynamic SQL with the columns you know exist and with strongly-typed parameters that can only be treated as such rather than just appending your query with whatever someone typed into a form field on a web page and blindly executing it.

Surely there aren't 8,000 columns in this table, and the number of permutations of clauses that a user can add are not exhaustive. You could always limit it logically to 5 or 10 clauses so that you don't have GB-long WHERE clauses.

Some constructive ideas for dealing with dynamic SQL and flexible WHERE clauses:

Protecting Yourself from SQL Injection - Part 1
Protecting Yourself from SQL Injection - Part 2

I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it.

Other answers where I talk about the kitchen sink procedure:

Tsql Query speed slow due to Or within where clause, causing index scan instead of seek
Plan cache memory: parameterized SQL vs stored procedures
SQL Server procedure optimisation
Strange looking where clause in sql server
Stored procedure with recompile


How do I determine if FORCE LOGGING is on for the entire database?

SELECT force_logging FROM v$database;

The SELECT_CATALOG_ROLE gives you permission to run this statement.


Unable to insert into table after creating an After insert trigger

Instead of saying marketplace_saleperpartner., say NEW. (or OLD., if appropriate).


You just need to wrap the query in quotes:

echo "Select count(*) FROM all_tables where owner='schema_name';" | sqlplus USER/PASS@localhost:1521


A large difference in time makes me think there is significantly different IO happening for these two queries.
The second query qualifies for trivial optimization. Likely it is performing a table scan, on the heap or a clustered index.
The first query projects only a single column. If there is an index with that column as a leading key the optimizer is likely to choose to read from that index as doing so will require fewer IOs.
So how do fewer IOs result in longer elapsed times? Let's suppose the base table's pages are sequentially ordered on-disk and can be efficiently scanned, perhaps with pre-fetching. This can be fast. Further, let's suppose the index is badly fragmented on-disk so each new page read requires an on-disk seek. This will be slow.
In summary, statement 1 uses an index scan on a badly fragmented index suffering slow seek times while statement 2 uses efficient sequential disk scan.
To be sure look at the two query plans. If I'm correct the respective objects read will be obvious in each plan. The amount of fragmentation can be found from the documentation. That link also gives the solution. Note that fragmentation need not be a problem, depending on the hardware and workload.
Fragmentation can occur due to rows being added in the middle of the key range. This is a natural part of application usage. It can be mitigated by an appropriate fill factor. It can also happen when database files are shrunk. Don't do that.


Temporal tables are new and in all editions. They add a start and end time to rows and keeps a history of datachanges with some DDL to pull back changes.

It may be possible to stick into existing applications but has some limitations (eg no cascade deletes) and so is much more likely to be put in during design time so that developers don't need to roll their own anymore.


The application is not installed - Visual Studio 2013

apparently this is because I need to download and install

Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013

http://www.microsoft.com/en-us/download/confirmation.aspx?id=42313

I will get this done and if it all works I will accept this answer.


How do you read MySQL InnoDB Monitor output?

Please look at this phrase from your question

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2712 n bits 264 index `PRIMARY` of table `bt/user_account` trx id 0 984899905 lock mode S locks rec but not gap waiting
Record lock, heap no 126

It says index PRIMARY has a shared lock (lock mode S) in Transaction 1

Now, check out this phrase from your question

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 2712 n bits 264 index `PRIMARY` of table `bt/user_account` trx id 0 984900638 lock_mode X locks rec but not gap
Record lock, heap no 72
Record lock, heap no 107
Record lock, heap no 126

It says index PRIMARY has an exclusive lock (lock mode X) in Transaction 2. Three locks of this type are being held.

I have answered questions like this in the past, even as recent as 4 days ago

Trouble deciphering a deadlock in an innodb status log (Jun 6, 2011)
Will these two queries result in a deadlock if executed in sequence? (Jun 8, 2011)
Reasons for occasionally slow queries? (Jun 14, 2011)
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE? (Aug 8, 2011)
Concurrently update with limit mysql (Oct 28, 2011)

Here is the bottom line: The PRIMARY KEY, contained in the clustered index (internally called gen_clust_index), can experience intermittent locks when bulk inserting or performing multiple single INSERTs in a transaction. The application should be designed to perform a manual COMMIT after each INSERT. If this is not permissible due to the nature of the application, redesign the app to bulk insert into temp tables before populating the wager table.

As a sidenote, the reason these things do not run in serial fashion is due to the default transaction isolation as well as your app's design when it comes to INSERTs.

Here are the four(4) possible modes:

READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ (default)
SERIALIZABLE

There are probably deeper explanations that the things I have already stated. If there is anyone out there with any further explanation or information I've missed, please chime in.


Build a three table join with a recusive table in the middle?

You can do the recursion in a CTE from the top down carrying MarkupGroupID with you.

with C as
(
  select P.PartGroupID,
         P.ParentID,
         P.MarkupGroupID
  from PartGroup as P
  where P.ParentID is null
  union all
  select P.PartGroupID,
         P.ParentID,
         coalesce(P.MarkupGroupID, C.MarkupGroupID)
  from PartGroup as P
    inner join C
      on P.ParentID = C.PartGroupID
)
select P.PartID,
       P.Part,
       MG.MarkupFactor
from Parts as P
  inner join C
    on P.PartGroupID = C.PartGroupID
  inner join MarkupGroup as MG
    on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID

SQL Fiddle

The recursive CTE will create a derived table that looks like this.

PartGroupID ParentID    MarkupGroupID
----------- ----------- -------------
1           NULL        NULL
2           NULL        1
3           NULL        2
15          3           2
16          3           2
17          3           2
4           1           3
5           1           4
8           5           4
13          8           4
14          8           4
6           4           3
7           4           3
11          7           3
12          7           3
9           6           3
10          6           3

If you need parts with partgroup not connected to a markup you can use a outer join against MarkupGroup in your main query.

with C as
(
  select ...
)
select P.PartID,
       P.Part,
       MG.MarkupFactor
from Parts as P
  inner join C
    on P.PartGroupID = C.PartGroupID
  left outer join MarkupGroup as MG
    on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID


First, let me start with a warning. Be very careful when you start messing with the data on the slave. It can really ruin the integrity of the replication setup.

That being said, if you are absolutely certain you don't want the slave to execute that particular statement, you can issue the following commands on the slave:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;

This will skip the next statement in the replication log and move on. Here's the documentation for reference.


Running a query on an improperly indexed, 600gb table

You have a big problem for one reason: BTree indexes (which are the only format available for InnoDB) are highly inefficient for filtering on more than 1 range.

There is one thing that you must understand: in general, using more than 1 index per table access is not possible/efficient (there are some cases where union_merge is faster, but that is an exception)- in general, for AND conditions you want a single index over several fields.

So, no problem then, isn't it? We just index (LOCATION_TIMESTAMP, LATITUDE, LONGITUDE) right? Sorry, no. As you are using a range for the conditions on all the columns, only the first column of the index will be used. The reason is easy to see, but difficult to explain without drawing a tree, so I hope you can trust me on this and I can later explain you why.

There is an exception to this, which is a new feature found in 5.6 called Index condition pushdown (that doesn't solve the problem completely, but makes the 3-column index more useful than a 1-column one).

So what are the options?

Convert some of the conditions to the ref type, that means, convert them into equality checks. That is something that can be done with the timestamps. It may sound a bit weird, but indexing (LOCATION_TIMESTAMP, LATITUDE) and having a query like this:

WHERE LOCATION_TIMESTAMP IN ('2014-03-20', '2014-03-21', ..., '2014-07-20') AND
LATITUDE BETWEEN 46.86275036 AND 47.80149551;

may actually be much faster. Of course, this depends on the selectivity of the clauses, etc.

Can we do the same for LONGITUDE. In same cases, yes, you could create an additional field called LATITUDE_INT with floor(LATITUDE), and do something like:

WHERE LOCATION_TIMESTAMP IN ('2014-03-20', '2014-03-21', ..., '2014-07-20') AND
LATITUDE_INT IN (46, 47) AND
LATITUDE BETWEEN 46.86275036 AND 47.80149551 AND
LONGITUDE BETWEEN -122.44599707 AND -116.94458691;

The 3 columns of the indes will be used in this case. But to be frank, that starts to become a bit crazy.
Use the geospatial extensions. MySQL has support for spatial types like POINT, though precisely for geographical applications, and with its own indexes (R-trees), which can index in several dimensions at te same time -so they speed up multirange searches- and have its own, optimised set of bounding box functions (MBRContains() is what you want).

This is great, isn't it? Bad news: it is only available for MyISAM in 5.5 and 5.6, and it will only be available for InnoDB in 5.7. The change is also not very transparent (not as easy as just add an index).

So, the fact that you have an unindexed table is possible due that your options are very narrow, specially for 5.5. If you are a bit flexible about the table structure (partitioning, as you suggested) or performing other tricks, there are ways to walkaround it, but it needs some care.

Some people end up combining MySQL with external tools precisely for this reason, and we will not have a real solution until 5.7.

Finally, there is the problem of the large table. This can become a problem. InnoDB is only efficient when most of its non-leaf parts of the primary key fits into memory. As ypercube says, a 3.5TB table on a 4GB server is something scary. Inserting to that table must be slow. You will need more ram and thinking about partitioning it, for example, as you said, by state (if that is ok for your queries) of by timestamp and get advantage of the manual or automatic prunning.


How to change the database mode to STANDBY?

I Thought that I can change the DatabaseLS_Main from Standby mode to bring it online, take the snapshot and bring it back to STANDBY mode...is it right to do ?

What you are telling is not possible using logshipping.
There are limitations of database snapshots:

In a log shipping configuration, database snapshots can be created only on the primary database, not on a secondary database. If you switch roles between the primary server instance and a secondary server instance, you must drop all the database snapshots before you can set the primary database up as a secondary database.

There are some options:

If your database is not that big, you can do a snapshot replication, if you really want a snapshot of your database as opposed to doing a backup/restore.
If you want a near-to-realtime data on some secondary server, then transactional replication would be a better option.
Alternatively, you can use database mirroring. It allows database snapshot on the mirrored server but the database must be in the synchronized mirroring state.
Now that SQL Server 2012 and 2014 are out with AlwaysON, you can consider that as well if you want a read-only copy of your database.

Note that database snapshots as well as AlwaysON are Enterprise features :-)


Is there a behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)?

Yes this is just improved documentation.

FAST n has always been the n version of FASTFIRSTROW i.e. a request to the optimiser to choose a plan that will return n rows more quickly if a plan can be identified to do so.

It is not equivalent in anyway to requesting the TOP n rows, which is in effect what you would be asking for if the expectation was for query execution to terminate after n had been delivered.


Noting that you didn't ask whether it was a good idea, but only whether it is possible :) I would venture to say it seems like it should be possible, since Drizzle speaks the MySQL protocol over TCP -- as evidenced by the fact that you can use the "mysql" CLI client and other MySQL client libraries to connect to a drizzle server.

The "federated" storage engine in MySQL allows your "local" server to connect to a "remote" MySQL (Drizzle?) server using the standard MySQL C-client library (aha!), and remotely access one or more tables on the remote server as if they were tables on the local server... the "local" server essentially spoofs a client connection to the remote server... one connection per table, from what I've observed.

CREATE TABLE (
-- column definitions on the local server
-- should be identical to the remote server
-- although the engine is more forgiving than
-- the documentation will lead you to believe
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://user:pass@remote_server:port/schema/table';

-- this is one of two ways to configure the actual connection

I believe this will work because the remote server doesn't know anything unusual is going on -- it thinks it's being accessed by a client, issuing ordinary INSERT/DELETE/SELECT/UPDATE statements, and queries on the local server interact with the federated tables just as they would with any other table.

If the "local" server were MySQL with your stored procedures, and federated tables configured to connect to the "remote" server -- the Drizzle server -- where your data lives, this should accomplish what you want, if I understand the question correctly.  I use "local" and "remote" to describe their roles, of course, not their placement.  For this application, they would most likely be on the same machine, or a very fast LAN.

Your application would connect to the MySQL server to call the stored procedures, which would then manipulate data on the Drizzle server via the federated connections.

How efficient will it be?  Not nearly as efficient as having the stored procedures and data on the same DBMS.

Is it a good idea?  I'm not too sure about that.  But in principle, it's probably worth a shot.

The federated engine has gotten some bad press, but at least in MySQL 5.5, I find the criticism to be unmerited.  I have numerous federated connections from 5.5 → 5.5 and 5.5 → 5.1 in my production systems.

There are a number of caveats that go along with the federated engine, such as no support for transactions, and queries involving federated tables aren't cached on the local server (there's no way to invalidate them, after all).  They don't directly support foreign keys, but the foreign keys are still enforced by the remote server, which will throw an error that propagates back to you locally if you violate them (though it may not be the same MySQL error or SQLSTATE).

You also want be sure you define at least the same indexes on the "local" server as exist on the "remote" server -- and sometimes it may even make sense to declare indexes on columns that aren't really indexed on the far-end -- because if the optimizer decides a full table scan is needed, it actually requests the entire contents of the remote table over the wire from the far-end... usually not what you want.  The indexes don't actually "exist" locally or take up any space on the local server -- but the query sent to the far end includes a WHERE clause to get only the appropriate rows, only if the optimizer realizes this is a possibility.  (This fact does not seem quite as strange when you remember that federated is implemented as a storage engine and the core of MySQL server interacts with it as though it were indeed a storage engine.)

This document is several years old but provides some very good background on the federated storage engine.

Final note: yes, the federated engine is one of the things that got stripped out of Drizzle, but that's fine -- we only need it on the MySQL side for this application.


On a 12c R2 RAC DB, Oracle instance information on crsctl is shown incorrectly

With a valid support contract, you can check this:
Database Instance Resources Sequence in "crsctl stat res -t" Output Mismatches With Output of "olsnodes -n" (Doc ID 1580894.1)

  The node ordering is not guaranteed to be same as instance number
  ordering. CRSCTL output in sorted order is not guaranteed.

  ...

  This is expected behavior.


Is it possible to use Native Clients from previous releases to connect SQL Server 2012?

Yes, although it's possible that some new functionality might not be supported by the older client. We have a number of programs using the 2008 Native Client to connect to 2012, and haven't observed any problems.


MongoDB setting permission for functions

Finally I got the answer. For executing user defined javascript functions in MongoDB first of all need to call db.loadServerScripts().

Every database in Mongo has a collection named system.js, which is in systems collections group.We can define our roles and permissions for collections and define privilage for system.js collection too.

If any function uses collections that we don't have permission to use, function will not execute and return authorization error, if collection have those privilages, funcion execute successfully


Calculation is not correct during in the left join table

"Explode-Implode" syndrome causes the inflated SUM.

The JOINs are done.  This gives an intermediate table with lots of rows.
SUM() and other aggregates are performed.
The GROUP BY shrinks it back down to a small number of rows.

Where practical, this is a convenient solution; note that there is no JOIN or GROUP BY:

SELECT
        ( SELECT SUM(...) ... ) AS sum_1,
        ( SELECT SUM(...) ... ) AS sum_2
    FROM ...

But that won't work here.  Instead, let's compute the aggregates in "derived" subqueries:

SELECT COALESCE(uk.qty, 0) AS QTY_sold_in_uk,
       COALESCE(de.qty, 0) AS QTY_sold_in_de,
       ...
    FROM  tbl_inventory AS ti
    LEFT JOIN ( SELECT sku, SUM(qty) AS qty ) AS uk  USING(sku)
    LEFT JOIN ( SELECT sku, SUM(qty) AS qty ) AS de  USING(sku)

This way, the aggregation will be confined to the the one table.  Also, the LEFT allows for an item not to be sold in some country, then the COALESCE deals with the NULL.  Again, no GROUP BY.

This pattern begs for at least 5.6 because the derived tables have no index.  However 5.6 will dynamically build one.

Final form

SELECT
    ti.sku,
    ti.quantity,
    -- UK
    COALESCE(suo_uk.sku, 'Not Sold') AS Unit_sold_in_uk,
    COALESCE(suo_uk.units_ordered, 0) AS QTY_in_uk,
    -- De
    COALESCE(suo_de.sku, 'Not Sold') AS Unit_sold_in_de,
    COALESCE(suo_de.units_ordered, 0) AS QTY_in_de,
    -- Fr
    COALESCE(suo_fr.sku, 'Not Sold') AS Unit_sold_in_fr,
    COALESCE(suo_fr.units_ordered, 0) AS QTY_in_fr,
    -- ES
    COALESCE(suo_es.sku, 'Not Sold') AS Unit_sold_in_es,
    COALESCE(suo_es.units_ordered, 0) AS QTY_in_es,
    -- It
    COALESCE(suo_it.sku, 'Not Sold') AS Unit_sold_in_it,
    COALESCE(suo_it.units_ordered, 0) AS QTY_in_it
FROM
    tbl_inventory AS ti
    LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
                     FROM tbl_sku_units_order
                     GROUP BY sku ) AS suo_uk
                             ON suo_uk.sku = ti.sku
    LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
                     FROM tbl_sku_units_order_de
                     GROUP BY sku ) AS suo_de
                             ON suo_de.sku = ti.sku
    LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
                     FROM tbl_sku_units_order_es
                     GROUP BY sku ) AS suo_es
                             ON suo_es.sku = ti.sku
    LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
                     FROM tbl_sku_units_order_fr
                     GROUP BY sku ) AS suo_fr
                             ON suo_fr.sku = ti.sku
    LEFT JOIN ( SELECT sku, sum(units_ordered) as units_ordered
                     FROM tbl_sku_units_order_it
                     GROUP BY sku ) AS suo_it
                             ON suo_it.sku = ti.sku
    GROUP BY ti.sku


avoiding unique key collisions across tables

You should separate the table on year to get faster insert's, updates or deletes because we expect lots lots lots lots lots lots off records in this table..

**But... your problem is precisely the reason why separating tables on meta data (year in your question) is an SQL anti pattern, you could not have giving us an better use case*

If you want an better solution you should read about mysql partitioning see http://dev.mysql.com/doc/refman/5.5/en/partitioning.html


SQL Agent is not considered to be "enterprise class" as a job scheduler. It lacks a lot of functionality that you end up having to build yourself. One such example would be dependencies. As a result you are forced to put most of this logic into SSIS packages. Not necessarily a bad thing...just a pain to build and manage yourself.

So, that's my answer, build out an SSIS package and use some t-sql commands to call SQL Agent jobs as desired. Make sure you have your requirements and scenarios clearly defined. You may not want to have multiple jobs hitting the same tables at the same time, for example.

HTH


How to sort records before inter-row calculation?

I have managed to successfully use variables and sorting to solve your problem. This is my test set-up:

CREATE TABLE atable
    (`dt` datetime, `obj` varchar(8), `comp` varchar(11))
;

INSERT INTO atable
    (`dt`, `obj`, `comp`)
VALUES
    ('2016-09-13 06:00:01', 'object_a', 'component_1'),
    ('2016-09-13 06:00:02', 'object_b', 'component_1'),
    ('2016-09-13 06:00:04', 'object_b', 'component_2'),
    ('2016-09-13 06:00:08', 'object_a', 'component_1'),
    ('2016-09-13 06:00:16', 'object_b', 'component_1'),
    ('2016-09-13 06:00:32', 'object_b', 'component_2')
;

And this is the query:

SELECT
  dt,
  obj,
  comp,
  diff
FROM
  (SELECT @obj := '', @comp := '', @dt := CAST('1970-01-01' AS datetime)) AS var,
  (
    SELECT
      TIMESTAMPDIFF(SECOND, IF(obj = @obj AND comp = @comp, @dt, NULL), dt) AS diff,
      @dt := dt AS dt,
      @obj := obj AS obj,
      @comp := comp AS comp
    FROM
      atable
    ORDER BY
      obj,
      comp,
      dt
  ) AS sub
;

As this SQL Fiddle demo will show, the query returns the following output:

dt                   obj       comp         diff
-------------------  --------  -----------  ----
2016-09-13 06:00:01  object_a  component_1  NULL
2016-09-13 06:00:08  object_a  component_1  7
2016-09-13 06:00:02  object_b  component_1  NULL
2016-09-13 06:00:16  object_b  component_1  14
2016-09-13 06:00:04  object_b  component_2  NULL
2016-09-13 06:00:32  object_b  component_2  28


Nightly Excel Spreadheet import into mssql database

I think SSIS is really what you want to use here.    To just do a very basic import of Excel into SQL Server this is a great link to use - Simple Talk 10 Simple Steps

From there, you will then need to use a combination of Conditional Splits as well as MultiCast in order to move the data to different fields/locations depending on your various rules.    Lastly for your PMP count, it seems like using the RowCount task should cover what you are looking to do with that.

There are dozens of ways to do these things even if you decide to go about this using SSIS.    You can load your data into Staging tables so that everything is an insert and then you run Execute SQL Tasks to do all of the individual updates.   Or you could use that MultiCast/Conditional Split approach and then use SQL Server Destinations or you could use OLE DB Commands in order to get the information either loaded or update individual records based on the data that you have available to you within the data flow.   There are certainly performance issues to deal with when using those OLE DB Commands, since they are working each thing row by row, but they are fairly clear to understand within the Data flow task.

You will have to work through what works best for you.   I have tried to include links of what I feel are good examples to show you how to do these kinds of things (Excel import, Multicast, Conditional Split, RowCount and OLE DB Command), so hopefully between that this will get you where you need to go.


See @DanGuzman comment above. It did the trick. I have to remove the quotes and N from the EXEC statement for my variable. Credit goes to Dan Guzman.

EXEC msdb.dbo.sp_add_jobserver @job_id=N'jb3510a9-d75f-4906-b9e3-en74b947d7ev', @server_name = @TargetServer