CLR and where to store config values I am looking to replace some hard-coded values in CLR code (SQL 2012), I came across the use of config files here - https://stackoverflow.com/questions/16177769/assembly-configuration-clr-stored-procedure.  A couple responses said it is better to save key/value pairs in tables as opposed to config files, one reason being that it wasn't supported in SQL 2014+.

Our developer says it works fine in SQL 2014, so I'm not concerned with that, but I do think storing the values in a table on the local database would be a better idea.

Can anyone else confirm that config files are or are not supported in SQL 2014+?
Is there any documentation on best practices for storing key/value pairs in a table, or any reason not to?

I am pretty sure that the sqlservr.exe.config config file works in SQL Server 2014, but will take me a few minutes to confirm. I am also pretty sure that it is not "officially" supported, and so not "recommended" to use, but on occasion I find it handy to use and have done so.

Whether or not the location of the data should be in a config file or table depends largely on what the overall needs are. How often will the values change? You say "hard-coded", but lookup values can change per each release, sometimes. Do you want to have to run an UPDATE statement, or find a way to include maintaining this config file in your build process (you need to find some way to ensure that it is always there).

Also, will the values ever be needed outside of the .NET / SQLCLR code? Having them in a table makes them more available to all existing queries.

Also, will there ever be a need for separation between databases for these values? With a config file it is shared across all App Domains for that Instance. A config table can just as easily be placed in a common Database (assuming multiple databases might want the values) or placed into each Database. You can have separation of values within the config file, but that requires having different names for the keys, and that makes it harder to standardize within the .NET code.

If it makes sense enough to be read-only and common / shared, then why not start out trying the config file approach, and if you find any reason that it doesn't meet your needs, just move it over to being stored in a table. After all, neither option is going to require that many hours of work, and the time difference between them is negligible, so it won't cost much if you do need to switch to the other approach.

NOTES:

In the StackOverflow question linked in this question, the answer from @David states that this config file is no longer working as of SQL Server 2014. I have just tested and confirmed that it does indeed work on SQL Server 2014 and even on 2016!
In the StackOverflow question linked in this question, the answer from @Remus states that storing in the DB is preferred due to the config file being disconnected from SQL Server and possibly not on related servers, or possibly out-of-synch with them. This point should not be ignored, and those concerns should help the team look at the big picture of how the application lives and hence come to a more informed decision. However, there are certainly scenarios that do not have such concerns and which allow for the build process to ensure that this config file is in place in each environment, just as that build process does the same checks for config files on web servers, etc.
In the blog referenced in @Remus's answer in the linked question, the author mentions an "issue" with getting errors attempting to read AppSettings, and a work-around is noted to call ConfigurationManager.ConnectionStrings.Count;. That is correct. However, you don't need to make that call each time; it only needs to be done once for the life of the App Domain. So, it works just fine to place it in a static class constructor as follows:

static _your_class_name_here_()
{
    int _BugBeGone = ConfigurationManager.ConnectionStrings.Count;
}

And no, you do not need to have a <connectionStrings> section in your config file for the above to work.
That same blog post also mentions that the config file is read only once, when the App Domain is created and that you will need to restart the instance -- or possibly unload the App Domain -- in order to have any changes made to the config file after the App Domain is created become visible to your code. The first part is correct: the config file is loaded once and cached for the life of the App Domain. However, you do not need to restart the Instance in order to refresh the config values, nor even unload the app domain. You just need to call:

ConfigurationManager.RefreshSection("appSettings");

But don't do that for every call, unless the function or stored procedure is meant to run occasionally. If this is something that will run at least once per second, or more often, then create a separate function or stored procedure to do just that refresh and execute it when necessary.


Optimising a query using hint locks I have a stored procedure that is being called very frequently.
For some database servers it is as often as 20 times per second.
Unfortunately because different clients call this procedure, I can't (without significant rearchitecture of the application) chunk the queries into table updates.

Recently the database performance has fell through the floor, a very simple update takes 30-90 seconds instead of milliseconds.

By far the (looking at resource waits on the servers), Locks contribute the vast vast majority of the waits (its 150x the time of the second contendor).

The stored procedure does some validation then either inserts or updates an existing record (for now all of them are updates).  If the validation fails it RAISERROR and the errors is passed all the way back to the client (the main obstacle to why it can't be chunked easily).

The stored procedure is

UPDATE dbo.TblHeartbeat
SET
    LastCheckedIn = GETUTCDATE(),
    NumberOfCheckIns = NumberOfCheckIns + 1,
    LastCheckInVersion = @Version,
    IPAddress = @IPAddress
WHERE
    MachineID = @MachineKey

I suspect that changing this to a WITH(ROWLOCK) will resolve the issue.
There doesn't seem to be a lot of science around locks though.
Opinions vary from, SQL knows what it's doing don't force it to do something, to it will ignore hints and you should actually remove the page lock from the table to force it to use row locks.

I am looking for,

What is the correct approach here.
What science can I place behind this change
How do I calculate / appreciate the impact of this change.
Will SQL ignore hints?

Before using hints, I would check the indexing on the table you are updating; it's probable you want a clustered index on MachineID; without it, an update may be forced to lock the entire table to update one row, which may be what you are seeing. Also examine any triggers on the table, make sure they are optimized.

Post your table schema, or execution plan for the update if you can.


What is the most effective way to cluster MySQL for my requirements? Basically I have 3 edge servers and 3 application servers. The edge servers are running nginx and load balance between the application servers. The application servers are running Virtualmin GPL with PHP applications deployed. All are running CentOS 5.7

At the moment MySQL is running on one of the edge servers, and is the single point of failure - I'm looking to cluster it between the application servers, but I'm just not sure what the best thing would be to do. I was thinking of running MySQL load balancers on 2 of the edge servers, then the MySQL instances on all 3 of the application servers and the MySQL cluster management server on one of the edge servers.

Does anyone have any other ideas? The problem with the aforementioned setup is the apps will be connecting to the edge servers for MySQL which could affect performance.

As I'm the developer of most of the apps I can rewrite the database abstraction in such a way it could switch load balancers (like round-robin DNS) if one was unavailable. I have some Wordpress stuff that isn't mission critical, but my site powercuts.info is the biggest and gets the most traffic, I want to make sure it performs well.

Thank you

It depends on how you use your database. If it is read-heavy but with few writes (like a blog or a news paper) you could have one mysql for writes and two for reads. You would set up so the write server is a replication master and the two for reads are slaves.

All application servers needs to know about both the write server and one of the read servers, that way when you balance the loads to application servers you automatically balance the reads between the mysql servers. It's also easy to add another mysql+application server once the need is bigger.

If you on the other hand have a write heavy site (I can't even find an example) you need to do some research on sharding. It's normally not recommended unless you really need it.


Difference between clustered index and partitioning column As I understand partitioning, the column that you partition on is how things are divided into partitions.

But does the partitioning column have anything to do with order on the disk? (Aside from which partition the data goes into.)

Say I have a table that looks like this:

CREATE TABLE [dbo].[Something](
    [SomethingId] [bigint] IDENTITY(1,1) NOT NULL,
    [OtherThingId] [bigint] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedWhen] [datetime] NOT NULL,
    [CreatedWhere] [varchar](255) NOT NULL,
     CONSTRAINT [PK_Something] PRIMARY KEY CLUSTERED
    (
        [SomethingId] DESC
        [OtherThingId] DESC,
    )
) ON OtherThingIdPartitionScheme(OtherThingId)

The partition that a new row will be stored on is decided by OtherThingId.

But I setup the clustered index to be first SomethingId, then by OtherThingId.

Does that mean that each partition will be ordered by SomethingId, then by OtherThingId?
(Even though it is partitioned only by the secondary value of OtherThingId.)

(I am setting up a lot of partitioning right now and I want to be sure I fully understand it.)

But does the partitioning column have anything to do with order on the disk?

From Clustered Index Structures :

  Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. By default, a clustered index has a single partition. When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

From Table and Index Organization:

When a table or index uses multiple partitions, the data is partitioned horizontally so that groups of rows are mapped into individual partitions, based on a specified column. The partitions can be put on one or more filegroups in the database. The table or index is treated as a single logical entity when queries or updates are performed on the data.

The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows. The page collections for the B-tree are anchored by page pointers in the sys.system_internals_allocation_units system view.

If you want to really dig more into how data is layed out then refer to : Inside The Storage Engine: sp_AllocationMetadata

As a side note : SQL Server 2016 CTP2 has TRUNCATE TABLE ... [ WITH ( PARTITIONS ( {  |  }

For automating partitioning switching check out - SQL Server Partition Management utility


Should I pause Mirroring and stop SQL Server services before running windows updates? I am running Windows update on my SQL Servers (which are in a Principal-Mirror partnership, in High Safety mode).

I am starting with updating the mirror and I was wondering, to increase safety, should I pause mirroring and stop SQL Server Services and then run Windows Update?  Or is this totally unnecessary?

Suggest you to PAUSE mirroring and then patch the windows.

Ideally, I would follow below approach :

If you have a witness configured, turned it OFF as during the patching, if sql server service is restarted or the server reboots, then a witness will initiate a failover.

ALTER DATABASE [db_name] SET WITNESS OFF

Always patch the current mirrored server, so if things go wrong, then you atleast have your principal ON.

Reboot the mirror server (if necessary)

Now failover to the newly patched mirror server. Run below T-SQL on the current Principal server ALTER DATABASE [your database] SET PARTNER FAILOVER
Once the failover is completed, patch the current mirror (which was originally primcipal)

Reboot if necessary

Now failback to the original principal server.
Add back the witness (if removed from step 1)

ALTER DATABASE [db_name] SET WITNESS = 'TCP://[FQDN]:[port_number]'
As a saftey measure, run DBCC CHECKDB on your databases.

From BOL :


How to Keep Audit Data and Effectively Query Large Tables Thanks for reading this.  I have two types of data - demographic data and transactional data.  The demographic data is only being stored when it changes. I’m trying to keep an audit trail for the demographic data at the same time. The transactional data is being aggregated.  Both tables are keyed on Account Number and Date.   A greatly simplified example might make it clearer:

Acct#, Name, Phone#, EffectiveDt

123, John Smith, 123-132-1232, 1/1/2019

321, Mary Smith, 123-232-3121, 3/1/2019

123, John Smith, 432-123-4322, 5/1/2019. — two records for John Smith, the second containing the most recent data

Acct#, MonthEnding, Sales

123, 1/31/2019,5000

123, 2/28/2019,2000

123, 3/31/2019, 500

321, 3/31/2019,2008

123, 4/30/2019, 5009

321, 4/30/2019,2002

123, 5/31/2019, 5095

321, 5/31/2019,2000

So I’m achieving the audit history by keeping two records for John Smith in the demographics file but the effective date will be different than the effective date in my transaction file.

I am concerned about the design because I would have to select for max(effectivedt) and account number as part of getting both Demographic and transactional data.   The demographics file is really a set of five tables and with hundreds of thousands of records.    I’m concerned that my queries down the road might be too slow with this approach but there are a lot of aspects to this strategy that really appeal to me.  The primary one being that I want to avoid a separate set of audit tables to manage.

I did see this question but I wanted to check with others about my specific situation as I’m trying to avoid a separate set of tables if possible and I had hoped that others might have ideas or experience with this problem:  Data Change Audit Plan

Any thoughts or input would be greatly appreciated.

Add a column "current" to your existing table make it true or false.  If current it is true.

Additionally

Consider adding a second date column "End effective", when John Smith has multiple old records, you are going to want to know the date span each was correct for.


Should I be concered by large SERIAL values? I have a Django application that uses PostgreSQL to analyze data from tweets. The data set increases by thousands of records with each request. I am using the database primarily as a cache, so I had planned to delete all records every 24 hours to permit new requests without needlessly increasing the size of the database.

Django uses the SERIAL type for storing the ids; a new item is given the next highest value of the last item that was created, rather than the first available number. I don't use the ids for anything outside of the ORM. My concern is that I will eventually run out of key space on my 32 bit VM. What does PostgreSQL when the next value is too large for SERIAL? Does it give error? Does it roll back to one?

TLDR: If you're going to have more than 231 possible values change the column type to BIGSERIAL.

Explanation:

The SERIAL type is a signed 32-bit integer and is designed for values less than 231 (NOTE: that's 231, not 232 as they're signed integers). Here's the snippet from the PostgreSQL docs:

  The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work the same way, except that they create a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table. The type names smallserial and serial2 also work the same way, except that they create a smallint column.

The behavior of how it handles overflowing past 231 isn't mentioned in the docs but it clearly states that you shouldn't use it if you expect to have values greater than that.

SERIAL columns are sequence numbers. The server keeps track of the previous value (the prior max) and each time a value is requested the value is incremented. This incrementation can happen regardless of whether the id is actually permanently saved. If the transaction that fetched the id is rolled back then the id will be "lost" and your sequence will have holes in it (eg. gaps of unused ids). There's nothing wrong with that and if you're simply using them as unique ids then having gaps should cause no issue.

Sequences are implemented like this to be efficient. The server only needs to keep track of one value (the prior max) to uniquely generating ids, it can cache them across multiple connections to speed up sequence generation, and by allowing gaps it never needs to transactionally lock the sequence object when it's generating sequences. This makes them very efficient and concurrent.

Just switch your database table to use BIGSERIAL.


Parent-Child Tree Hierarchical ORDER I have to following data in SQL Server 2008 R2. SQLFiddle

Schema:

CREATE TABLE [dbo].[ICFilters](
   [ICFilterID] [int] IDENTITY(1,1) NOT NULL,
   [ParentID] [int] NOT NULL DEFAULT 0,
   [FilterDesc] [varchar](50) NOT NULL,
   [Active] [tinyint] NOT NULL DEFAULT 1,
 CONSTRAINT [PK_ICFilters] PRIMARY KEY CLUSTERED
 ( [ICFilterID] ASC ) WITH
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON
 ) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[ICFilters] (ParentID,FilterDesc,Active)
Values
(0,'Product Type',1),
(1,'ProdSubType_1',1),
(1,'ProdSubType_2',1),
(1,'ProdSubType_3',1),
(1,'ProdSubType_4',1),
(2,'PST_1.1',1),
(2,'PST_1.2',1),
(2,'PST_1.3',1),
(2,'PST_1.4',1),
(2,'PST_1.5',1),
(2,'PST_1.6',1),
(2,'PST_1.7',0),
(3,'PST_2.1',1),
(3,'PST_2.2',0),
(3,'PST_2.3',1),
(3,'PST_2.4',1),
(14,'PST_2.2.1',1),
(14,'PST_2.2.2',1),
(14,'PST_2.2.3',1),
(3,'PST_2.8',1)

Table:

| ICFILTERID | PARENTID |    FILTERDESC | ACTIVE |
--------------------------------------------------
|          1 |        0 |  Product Type |      1 |
|          2 |        1 | ProdSubType_1 |      1 |
|          3 |        1 | ProdSubType_2 |      1 |
|          4 |        1 | ProdSubType_3 |      1 |
|          5 |        1 | ProdSubType_4 |      1 |
|          6 |        2 |       PST_1.1 |      1 |
|          7 |        2 |       PST_1.2 |      1 |
|          8 |        2 |       PST_1.3 |      1 |
|          9 |        2 |       PST_1.4 |      1 |
|         10 |        2 |       PST_1.5 |      1 |
|         11 |        2 |       PST_1.6 |      1 |
|         12 |        2 |       PST_1.7 |      0 |
|         13 |        3 |       PST_2.1 |      1 |
|         14 |        3 |       PST_2.2 |      0 |
|         15 |        3 |       PST_2.3 |      1 |
|         16 |        3 |       PST_2.4 |      1 |
|         17 |       14 |     PST_2.2.1 |      1 |
|         18 |       14 |     PST_2.2.2 |      1 |
|         19 |       14 |     PST_2.2.3 |      1 |
|         20 |        3 |       PST_2.8 |      1 |

Every row has the ID of its parent and the root's parentid = 0. The FilterDescs are just sample descriptions so I can't try to parse those for ordering.

The Question

Is it possible to select all the rows in a tree-like manner? If so, how?
When I say 'tree-like', I mean recursively select the parent followed by all of its children, then all the children of each one of those and so on. A Depth first tree traversal.

My Friends and I have tried but we have fallen short of working solutions but will keep trying. I am fairly new to sql so maybe this can be done easily and i'm just making things harder than necessary.

Example(desired) output:

| ICFILTERID | PARENTID |    FILTERDESC | ACTIVE |
--------------------------------------------------
|          1 |        0 |  Product Type |      1 |
|          2 |        1 | ProdSubType_1 |      1 |
|          6 |        2 |       PST_1.1 |      1 |
|          7 |        2 |       PST_1.2 |      1 |
|          8 |        2 |       PST_1.3 |      1 |
|          9 |        2 |       PST_1.4 |      1 |
|         10 |        2 |       PST_1.5 |      1 |
|         11 |        2 |       PST_1.6 |      1 |
|         12 |        2 |       PST_1.7 |      0 |
|          3 |        1 | ProdSubType_2 |      1 |
|         13 |        3 |       PST_2.1 |      1 |
|         14 |        3 |       PST_2.2 |      0 |
|         17 |       14 |     PST_2.2.1 |      1 |
|         18 |       14 |     PST_2.2.2 |      1 |
|         19 |       14 |     PST_2.2.3 |      1 |
|         15 |        3 |       PST_2.3 |      1 |
|         16 |        3 |       PST_2.4 |      1 |
|         20 |        3 |       PST_2.8 |      1 |
|          4 |        1 | ProdSubType_3 |      1 |
|          5 |        1 | ProdSubType_4 |      1 |

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT
    [ICFilterID],
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT
    i.[ICFilterID],
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT
  [ICFilterID],
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];


How to validate mongodb backup and restore I am using mongodump to create a backup of the db based on some query params and mongorestore to restore the same in another mongodb in a diff environment.

How can I validate whether the dump created fetched all the records and also whether the restore of dump to the target db is done properly?

You should try this:

https://studio3t.com/

You can use "data synchronization" And if I remember correctly, you can validate data between 2 DB.

You can use it non-commercial use for free. Just download, and at the first start you can select between commercial and non-commercial use.

Also this: https://robomongo.org/


Are user defined types (UDTs) available in an Azure SQL database managed instance? Is it possible to create user defined types and user defined table types in an Azure SQL database managed instance?

User-defined types are supported in Azure SQL Database except those based on a CLR assembly, which is not supported.


Several questions about sp_BlitzIndex When sp_BlitzIndex does index suggestions, the question in my head is as below:

Yes the suggested index would bring a lot of speed benefit when doing select statements. But there are also another costs for these indexes which are delete, insert, update queries

Do sp_BlitzIndex script take into consideration delete, insert, update costs as well? or only it does evaluate how much speed benefit would come with select statements?

Moreover, how can i list bad indexes via sp_BlitzIndex? and how are they determined to be bad?

Thank you very much for answers

and welcome to Stack Exchange. Going forward, try to break questions out individually. Here's how to write a great question.

  Do sp_blitz script take into consideration delete, insert, update costs as well?

sp_BlitzIndex has several sections based on SQL Server's diagnostic management views.

The missing indexes are based on sys.dm_db_missing_index_details, which don't take into account delete/update/inserts.
The existing indexes list is based on sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats, which do take into account deletes/updates/inserts.

  Moreover, how can i list bad indexes via sp_blitz? and how are they determined to be bad?

sp_BlitzIndex doesn't call indexes bad. It treats indexes a lot like human behaviors - for example, hoarding isn't necessarily bad in small amounts, or agoraphobia, or kleptomania, or being a furry. All kinds of behaviors are okay in small doses. It's up to you, not the script, to determine if an index is bad.


Is there a way to delete duplicate from a cell value (value of a given row and column) using Postgresql In my Postgres db, I have a table with some columns where one column B has values as follows:

A      B
a1     b0, b1, b1
a2     b2, b3
a3     b4, b4, b5, b5

My expected output should be like this:

A      B
a1     b0, b1
a2     b2, b3
a3     b4, b5

Is there a simple UPDATE postgresql to do this task?

Assuming b is an array:

UPDATE mytab
SET b = (SELECT array_agg(DISTINCT x)
         FROM unnest(b) AS x);


MultiSubnet Failover with RegisterAllProvidersIP 1 I am using a 2 node SQL AG (2014 EE) (each on a different subnet) and the Listener is configured with 2 Static IPs. The client application uses SQL Native Client 11.4.
My understanding is that this driver is legacy and hence should not support failover with RegisterAllProvidersIP as 1 and default HostRecordTTL( 20 minutes).
Although I am unable to failover when RegsiterAllProvidersIP is 0 and TTL is 20 minutes (I am able to when TTL is lowered to say, 2 minutes), the application successfully fails over with RegisterAllProvidersIP set as 1.
According to the blog here, https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/
It is recommended to set RegisterAllProvidersIP to 0 and HostRecordTTL to 2 minutes to achieve failover, but in my case I found that it is not needed as I am able to failover with RegisterAllProvidersIP set as 1.
Am I completely missing the point of recommended settings from MS blog or my multi subnet setup has any issues?

We are using the OLEDB flavor of SQLNCLI11 which does not support
  multisubnetfailover out of the box.

Microsoft has deprecated the native client, however, they have released a new OLE DB driver which supports all the native client features plus adds MultiSubnetFailover and TLS1.2 support. See the announcement here. It may be worth looking at upgrading OLE DB to take advantage.

  My understanding is that this driver is legacy and hence should not
  support failover with RegisterAllProvidersIP as 1 and default
  HostRecordTTL( 20 minutes). Although I am unable to failover when
  RegsiterAllProvidersIP is 0 and TTL is 20 minutes (I am able to when
  TTL is lowered to say, 2 minutes), the application successfully fails
  over with RegisterAllProvidersIP set as 1.

With the RegisterAllProvidersIP = 0 and TTL = 20 combo, you would need to wait up to twenty minutes post-failover for the new listener IP to be registered in DNS and picked up by your clients. It is still technically 'working' it's just very slow.

  According to the blog here,
  https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/
  It is recommended to set RegisterAllProvidersIP to 0 and HostRecordTTL
  to 2 minutes to achieve failover, but in my case I found that it is
  not needed as I am able to failover with RegisterAllProvidersIP set as
  1. Am I completely missing the point of recommended settings from MS blog or my multi subnet setup has any issues?

How are you testing to validate that failover works?
What is your connection timeout?

With RegisterAllProvidersIP set to 1, the native client will try and connect to each IP address returned by DNS serially. If the first IP returned by DNS is the correct one it connects, if not it takes ~20 seconds to timeout, then waits ~20 seconds then tries the next IP.

What this means is if your connection timeout is greater than ~40 seconds, its quite possible that you're seeing the behaviour in question but a high connection timeout value is masking it.

Either way, the true fix here is to start using MultiSubnetFailover to make the best use of the rapid failover capabilities provided by RegisterAllProvidersIP=1. If you don't want upgrade the OLE DB driver, you should set a low TTL for the listener DNS entry and set RegisterAllProvidersIP=0. It may work for you now with it set to 1, but conditions in your environment could change and cause this to suddenly stop working.


SQL Server 2008 R2 Resource Managment I have a server running Windows Server 2008 R2 Enterprise 64Bit, with 256 GB RAM.

I am running SQL Server 2008 R2. With normal usage, SQL Server is using about 232 GB of RAM.
Occasionally it will creep up approaching 100% RAM usage; this has been causing a lot of problems

I know that I can set maximum server memory usage. I don’t know what to set the usage to. I have been going over the SQL Server documentation and TechNet forums and have not been able to find a best practice or even a consensus as to the correct settings.

I guess what I am looking for is a SQL Server 2008 R2 resource management best practice guide would someone be able to point me in the right direction, or offer some advice. Thanks in advance.

" Occasionally it will creep up approaching 100% RAM usage; this has
  been causing a lot of problems"

That's often a red herring, as SQL Server will use all of RAM (but will give it back under OS memory pressure).

Rule of thumb leave 2GB or 5-10% of total physical memory free (Max Memory setting) whichever is larger.

Glenn Berry has a good resource here:

Suggested Max Memory Settings for SQL Server 2005/2008

Also:

A Sys Admins Guide to Microsoft SQL Server Memory
Wow… An online calculator to misconfigure your SQL Server memory!
How much memory does my SQL Server actually need?
Troubleshooting SQL Server: A Guide for the Accidental DBA
Server Memory Options


mysql dump a list of query to file and restore to another database I have 2 databases. The first one is a production have some data deleted. The 2nd one is a backup so it has everything. I traced the log so I got every records are deleted in production database by the list like this

SELECT * FROM `journal_details` WHERE `journal_details`.`journal_id` = 28679;
SELECT * FROM `journals` WHERE `journals`.`id` = 28679;
SELECT * FROM `journal_details` WHERE `journal_details`.`journal_id` = 29262;
SELECT * FROM `journals` WHERE `journals`.`id` = 29262;

I want to migrate the result of queries above to the production database. How to do it the easy way? I can't restore the whole database due to it will overwrite some other records :(

PS: I tried mysqldump like this but got errors :(

mysqldump -u dk2303  -h 127.0.0.1 -p  rm_production --default-character-set=utf8 --result-file=/home/dk/dump.sql --where="SELECT * FROM journal_details WHERE journal_details.journal_id = 28679; "
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `attachments` WHERE SELECT * FROM journal_details WHERE journal_details.journal_id = 28679; ': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM journal_details WHERE journal_details.journal_id = 28679' at line 1 (1064)

I think if you're going to use a WHERE clause, you need to specify your table in the mysqldump command. You also just need to include the WHERE clause and not the corresponding SELECT - as per follows:

mysqldump -t -u dk2303 -p my_database journal_details  --where="journal_id = 28679"

journal_details is your table.
--h 127.0.0.1 is the default anyway so not necessary
--default-character-set=utf8 (keep this - may be default also?)
--result-file=/home/dk/dump.sql (you can keep this also, otherwise just use the > redirect operator)

See here for more details.


Is there anyway to tell when the last query was made against a database? Is there anyway to tell when the last query was made against a SQL 2008 R2 database without downloading a third-party tool ?

You can use the following SQL command to get the last commands that executed on MyDB (as example).

SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('MyDB') --note you need to change MyDB to your database name
ORDER BY deqs.last_execution_time DESC

Usfull links:

https://stackoverflow.com/questions/13638435/last-executed-queries-for-a-specific-database

http://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/

http://blog.sqlauthority.com/2009/07/19/sql-server-get-last-running-query-based-on-spid/


Color formatting Pie Charts in SSRS 2008 R2 I have a pie chart with Categories(Male, Female) with Percentages. If I want to color format Male and Female portion of the chart, what is the approach?

Open the Properties pane.
On the design surface, click the chart. The properties for the chart object are displayed in the Properties pane.
The object name (Chart1 by default) appears in the drop-down list at the top of the Properties pane.
In the Chart section, for the Palette property, select a new palette from the drop-down list.


Configuring SQL Server management data warehouse (MDW) for data collection I am configuring a data warehouse on an SQL Server to collect data. However, the process is not finishing and it stays as per below for hours until it is manually stopped:

Have you ever encountered this? I searched online but found no similar issues and from YouTube videos it seems that the process finishes quite quickly.

Furthermore, once this is done, can you collect data from a VM that is on a different domain from the VM where the management data warehouse DB is (no trusting exists between the two domains).

As mentioned by you, that the repository server from where you running the MDW is in different domain than the target computer and no trust exist, probably the reason you might not see the job running it's step for connecting. Try catching some error if possible from error logs or event viewer.

Try using repository server running on same domain or create a trust between 2 and give it a shot.


Best way to identify orphan data? I have a few dozen tables, that have data in them, both for the core set of data, and then the company specific data.

I can never delete the core set of data, that is global for all companies.

But i want to be able to help identify orphan data.

Data that is inactive, but hasn't been deleted.

I do not need a query, I can figure that out myself.

Just some ideas on best practices, to help identify then get rid of orphan data, which hopefully will trim the database size down.

Thanks

I would suggest duplicating the table structure in a different schema (backup) and move the data from the one table to the other. That way it can be managed effectively. But if you know what's orphaned, then why would you keep it? What purpose will it serve?


Postgres support for UUID My understanding is that Postgres supports UUID data type out-of-the-box. I'm running Postgres on Amazon RDS with Engine version 10.6 and am scratching my head as to why the following commands are not behaving as expected:

CREATE TABLE IF NOT EXISTS "Tenants"
(
    "id" UUID NOT NULL ,
    "domain" VARCHAR(255) NOT NULL UNIQUE,
    "name" VARCHAR(255) NOT NULL,
    "schema" VARCHAR(255) NOT NULL UNIQUE,
    "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
    "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id")
);

The critical part here is that I'm creating "id" UUID NOT NULL. So far, so good. Or so I thought.

Next, I'm trying to add an entry:

INSERT INTO "Tenants" ("id","domain","name","schema","createdAt","updatedAt")
VALUES
(
    '3b6b220b-2690-4279-8343-cbe6167f7596',
    'test1.stage-domain.io',
    'Test1','test1',
    '2019-02-25 14:29:33.475 +00:00',
    '2019-02-25 14:29:33.475 +00:00')
RETURNING *;

and surprisingly the following error shows up:

  SequelizeDatabaseError: invalid input syntax for integer:
  "3b6b220b-2690-4279-8343-cbe6167f7596

Sure enough, if I go to PGAdmin, I'm seeing that the column has not been set to UUID, rather it has been set to an integer:

The dropdown shown in this picture does not even contain a UUID type.

The weirdest part is that I created a different Postgresql server on RDS last night and everything actually worked just fine in that instance.

My question: How can I get my database to recognize the UUID type when creating the Tenants table?

just typecast id value like this

postgres@localhost:test> create table "Tenants"
 (
     "id" UUID NOT NULL ,
     "domain" VARCHAR(255) NOT NULL UNIQUE,
     "name" VARCHAR(255) NOT NULL,
     "schema" VARCHAR(255) NOT NULL UNIQUE,
     "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
     "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id")
 );
CREATE TABLE
Time: 0.048s

postgres@localhost:test> INSERT INTO "Tenants" ("id","domain","name","schema","createdAt","updatedAt")
 VALUES ('3b6b220b-2690-4279-8343-cbe6167f7596'::UUID, 'test1.stage-domain.io', 'Test1','test1', '2019-02-25 14:29:33.475 +00:00','2019-02-25 14:29:33.475 +00:00') ;
INSERT 0 1
Time: 0.004s

hope this helps


Grant non Sysadmin the ability to change max memory setting SQL Server I've received a request to create a stored procedure that would allow a user (with access to the DB server) to change SQL Server's max server memory (MB). This wasn't the hard part.

The other requirement is that this user should be able to change this only parameter max server memory (MB) and nothing else. What I'd like to know is, if this is possible.

I do realize that, to be able executesp_configure and runRECONFIGURE, the minimum privilege required is ALTER SETTINGS. And providing ALTER SETTINGS would defeat the purpose of the stored procedure (which would be designed to run just exec sys.sp_configure 'max server memory (MB)', @TheReqMemVal) along with a minimum and maximum allowed value for max server memory (MB).

After reading some of the comments, I'd like to add that if responses were to incorporate possible security pitfalls, it would be greatly appreciated.

You should use signed procedure in this case.

The greate article Signing Procedures with Certificates by Erland Sommarskog explains it well, and here are the steps to perform:

Create a certificate in the master database.
Create a login for that certificate.
Grant that login ALTER SETTINGS.
Export the certificate to file.
Switch to the application database.
Import the certificate from the file.
Delete the file from disk.
Create a user for the certificate.
Sign the stored procedure with the certificate, each time you have
changed the procedure.


How does ON DELETE CASCADE relate to JSON? For relational databases, there is the notion of ON DELETE CASCADE. When working with JSON, how do these two concepts relate? Is ON DELETE CASCADE supported for JSON?

Can you give an example of a JSON-DB which supports ON DELETE CASCADE or allows some work around for that?

You're confused here,

JSON is a data-interchange format, in PostgreSQL we provide json and jsonb as formats with advantages like validation and indexing that help you work with this format.
ON DELETE CASCADE is a clause that tells a referring table what to do when the table that it refers to lose the row it's referencing.

You can mix the two concepts, but you should never do this

CREATE TABLE tfoo ( qux jsonb PRIMARY KEY );
CREATE TABLE tbar ( qux jsonb REFERENCES tfoo );
CREATE TABLE tbaz ( qux jsonb REFERENCES tfoo ON DELETE CASCADE );

Now we can insert some test data in tfoo,

INSERT INTO tfoo (qux) VALUES ( '{"id":42}' );
INSERT INTO tfoo (qux) VALUES ( '{"id":7}' );

And you'll get a reject if you try to insert into tbar or tbaz anything but the above,

-- Rejection
INSERT INTO tbar (qux) VALUES ( '{"id":2}' );
ERROR:  insert or update on table "tbar" violates foreign key constraint "tbar_qux_fkey"
DETAIL:  Key (qux)=({"id": 2}) is not present in table "tfoo".

This is a really bad idea though for many reasons. We can see here that these work though,

INSERT INTO tbar (qux) VALUES ( '{"id":42}' );   -- ON DELETE REJECT
INSERT INTO tbaz (qux) VALUES ( '{"id":7}' );    -- ON DELETE CASCADE

And moreover, we can see the effects -- {"id":7} is in tbaz which has the ON DELETE CASCADE

DELETE FROM tfoo WHERE qux = '{"id":7}';

And, this fails as {"id":42} is in tbar and it doesn't know what to do if you remove that entry from tfoo so it rejects the operation.

DELETE FROM tfoo WHERE qux = '{"id":42}';
ERROR:  update or delete on table "tfoo" violates foreign key constraint "tbar_qux_fkey" on table "tbar"
DETAIL:  Key (qux)=({"id": 42}) is still referenced from table "tbar".

Clarification

So you can see you can have ON DELETE CASCADE on any type that can be put on a UNIQUE index (PRIMARY KEY is a special UNIQUE NOT NULL index). This means, you can mix these two concepts, as we did above. But you should never do that, and I've never seen it done in practice. Generally, you put FOREIGN KEYS on int or text,

CREATE TABLE tfoo (
  id_foo int     PRIMARY KEY
    GENERATED BY DEFAULT AS IDENTITY,

  qux    jsonb
);
CREATE TABLE tbar ( id_foo REFERENCES tfoo, qux jsonb );
CREATE TABLE tbaz ( id_foo REFERENCES tfoo ON DELETE CASCADE, qux jsonb );


What is best way to move large number of existing indexes into new file-group - DB Size: 700GB I have requirement to move all the indexes into different storage space (file-group) within existing SQL Server 2014 Database (Current Size: ~700GB)

I'm planning for approach where i can drop existing indexes and recreate them on new file-group. I prepared a script to do the same activity (re-creating indexes one by one) with cursor, but after executing the script, my database is very slow, and it's running for more than 20 hours.

Any advise/recommendations to accomplish this activity in production database where it's got to manage more than 1,000 users requests.

The answer is: you cannot run heavy scripts and not slow down the DB server.

I quess all you have to do is to pick kind of light-workload-hours and REBUILD indexes one by one with your own hands monitoring blocking and overall server load.

General advice would be to use

ALTER INDEX <IndexName> REBUILD;

instead of recreating them.

Also consider ALTER INDEX options here such as:

ONLINE
RESUMABLE
MAX_DURATION
MAXDOP
WAIT_AT_LOW_PRIORITY

Have a look at these useful links:

one
two

Take care about free space when rebuilding online, as stated here.


SQL Server Query to EXCLUDE Friday and Saturday of the week & 4PM to 8PM all the days of the week I have a requirement to write a SQL Server Query that will fetch all the records of the table excluding Friday and Saturday of all the weeks and data between 4PM to 8PM of all the days of the week

Ex:

If I have the following records in my table

Name ProcessStartTime Status
Name1 2017-04-01 17:00:02.000 SUCCESS(Saturday)
Name2 2017-04-02 21:00:02.000 SUCCESS(Sunday)
Name3 2017-04-03 01:00:02.000 SUCCESS(Monday)
Name4 2017-04-04 05:00:02.000 SUCCESS(Tuesday)
Name5 2017-04-05 18:00:02.000 SUCCESS(Wednesday)
Name6 2017-04-05 15:00:02.000 SUCCESS(Wednesday)
Name7 2017-04-06 23:00:02.000 SUCCESS(Thursday)
Name8 2017-04-07 15:00:02.000 SUCCESS(Friday)

If I execute the Query I should get the Following records only,

Name2 2017-04-02 21:00:02.000 SUCCESS(Sunday)
Name3 2017-04-03 01:00:02.000 SUCCESS(Monday)
Name4 2017-04-04 05:00:02.000 SUCCESS(Tuesday)
Name6 2017-04-05 15:00:02.000 SUCCESS(Wednesday)
Name6 2017-04-06 23:00:02.000 SUCCESS(Thursday)

And the remaining data should not fetched because, those records are either Friday or Saturday as well as date time between 4PM to 8PM(Not only Friday and Saturday but for all the days of the week).

I have my query here below,

SELECT *
  FROM [TABLE]
  WHERE
    DATEPART(hh,ProcessStartTime) >= 00 and DATEPART(hh,ProcessStartTime) <= 16
    AND ((DATEPART(dw, ProcessStartTime) + @@DATEFIRST) % 7) NOT IN (6,0)

In this query I'm able to get the records not in Friday and Saturday but not able to neglect between 16 to 20 hours. I have written in a such way that query will fetch records between 00 to 16. but I need another condition to select between 20 to 23 hours. If I do that condition, I'm getting
0 records.

This is the further modified query that showing 0 records,

SELECT *
  FROM [TABLE]
  WHERE
    DATEPART(hh,ProcessStartTime) >= 00 and DATEPART(hh,ProcessStartTime) <= 16
    AND DATEPART(hh,ProcessStartTime) > 20 AND DATEPART(hh,ProcessStartTime) <= 23
    AND ((DATEPART(dw, ProcessStartTime) + @@DATEFIRST) % 7) NOT IN (6,0)

Please help me on this. If needs more information let me know.

Assuming that DATEFIRST = 1 (Monday), you can use datepart(dayofweek) to avoid Friday and Saturday and for the time part, I've edited my previous answer due I think you said 8PM, I supposed 08:00:01 must be included in the final result.

declare @events table (Name varchar(20), ProcessStartTime datetime, Status varchar(20));
insert into @events values
('Name1', '2017-04-01 17:00:02', 'SUCCESS'), -- (Saturday)
('Name2', '2017-04-02 21:00:02', 'SUCCESS'), -- (Sunday)
('Name3', '2017-04-03 01:00:02', 'SUCCESS'), -- (Monday)
('Name4', '2017-04-04 05:00:02', 'SUCCESS'), -- (Tuesday)
('Name5', '2017-04-05 18:00:02', 'SUCCESS'), -- (Wednesday)
('Name6', '2017-04-05 15:00:02', 'SUCCESS'), -- (Wednesday)
('Name7', '2017-04-06 23:00:02', 'SUCCESS'), -- (Thursday)
('Name8', '2017-04-07 15:00:02', 'SUCCESS'); -- (Friday)

SET DATEFIRST 1

SELECT Name, ProcessStartTime, Status
FROM   @events
WHERE  datepart(DW, ProcessStartTime) NOT IN (5,6)
AND    (datepart(hh, ProcessStartTime) < 16
       OR
       ProcessStartTime > DATEADD(dd, DATEDIFF(dd, 0, ProcessStartTime), '08:00:00'))

GO

Name  | ProcessStartTime    | Status
:---- | :------------------ | :------
Name2 | 02/04/2017 21:00:02 | SUCCESS
Name3 | 03/04/2017 01:00:02 | SUCCESS
Name4 | 04/04/2017 05:00:02 | SUCCESS
Name5 | 05/04/2017 18:00:02 | SUCCESS
Name6 | 05/04/2017 15:00:02 | SUCCESS
Name7 | 06/04/2017 23:00:02 | SUCCESS

dbfiddle here


Performance impact of sp_configure blocked process threshold change I've been tasked with identifying any blocking happening on a production server. My plan to achieve this is to use

EXECUTE sp_configure 'blocked process threshold', 5

In combination with a server side trace to collect a Blocked Process Report.

I understand that this activity will incur a performance hit, I'd like to find some way of quantifying what this will be.

Minimal hit. The cost of evaluating the blocked process list is already there since this is how deadlocks are detected. The cost of generating and writing an event in the server side log is quite low and any server IO should be able to handle an extra write into LOG/*.trc every 5 seconds (At worst). But 5 is a quite low setting, you may be flooded by reports. Is your server server blocking so well behaved as to consider any 5 second block an incident to investigate?

Make sure you monitor the generated traces and make sure you don't cause an self-induced outage due to disk space exhaustion...


Move a huge databases from one SQL Server to another I have customers databases located on my SQL Server 2008 machine. The size of these databases are around 300 GB. I would like to move them all to another SQL Server 2008.

Is there a way to shrink them before moving them? And what is the best way to move them easily?

Will I have to create same users on the new one?

Please try to provide me details as possible.

If you are using Enterprise edition, then you can use backup compression in sql server 2008.

In sql server 2008 R2 and up, even compression is offered in standard edition.

To move database, you can do a backup and then restore on the destination server.

Alternatively, you can just script out the schema and then move data using BCP OUT and BCP IN. You can even zip those files and move them across the destination server.

  Will I have to create same users on the new one?

Yes, you have to run sp_help_revlogin. on source server and then copy the output on the destination server. This will create the same logins with the SID's from original server. So when you restore your database, you wont have to sync up your users.

One more thing to add :

Make sure that Instant File Initialization is enabled on the server. That will dramatically reduce the restore time.


Is this a sane model for a network provisioning system? I'm sketching out a model for a network setup, where I have the following conditions:

There are multiple nodes which contains a number of switches each.
Each switch has multiple services, which may or may not have a rate limiting.
Each service is identified by a VLAN ID (VID), which is different on each switch per node.
VID cannot be re-used for other services across switches, but it is permitted to re-use the VID for the same service on switches on a different node.
The rate limiting for each service may be overridden by the profile assigned to the CPE.
A CPE can have any number of ports, each with its own service, and possibly rate-limit. VID and default rate-limit is determined by service, as noted above.

Example on the service-switch-VID restrictions: The "Internet" service has VID 1 on switch 1, and VID 11 on switch 2 for node #1, and #2. But VID 21 and 31 for switch 1 and 2 on node #3

Please let me know if you need any additional information. Almost 3am here, so I might have forgotten something.

Update: Fixed the list, and updated the model to reflect the changes I've highlighted in my first comment. Also noticed a missing FK relation, between cpe and cpe_port`, which I've added.

Understanding of the specifications and proposed data model
We have mantained very productive interactions via comments, I have read your question carefully and examinined your diagram closely, so I have prepared the following assertions that I consider relevant in order to describe my current understanding of your scenario and, therefore, share my personal approach to such:

A Node lodges one-to-many Switches.
A Switch forwards data to one-to-many Services (employing one particular VLAN in each data forward).
A Switch provides one-to-many Services to one-to-many CPEs (employing one specific VLAN in each provisioning). Or, put another way, a CPE is provided with one-to-many Services by one-to-many Switches (using one specific VLAN in each provisioning).
A CPE is classified by one SpeedProfile.
A CPE is set with one Port in oder to receive one specific  Service.
A CPE is set with one Port in oder to receive data from one specific Switch.
A Customer (either one Organization or one Person) receives one-to-many Services via one CPE.

From such collection of assertions, I have derived (and uploaded as a .PDF document to Dropbox) the following…

Network Provisioning Preliminary Data Model.

Note: It is necessary to point out that some of these assertions (or parts of specific ones) have been identified as inaccurate or erroneous, as mentioned below in the section entitled Subsequent conclusions.
As you can see in said IDEF1X data model, I consider that CPEService is the “core” entity, so to speak, since this is where I think that the relationships between CPEs and Services, Switches or VLANs take effect.
It is very important to mention that, if I understand your specifications well, there is a “three way” relationship in which the entities Service, Switch and VLAN are involved. That is why I have modelled (a) the association between Switch and VLAN in a specific many-to-many relationship, (b) the association between Service and VLAN in another particular many-to-many relationship, and (c) another many-to-many relationship called SwitchVLANService, where the “connection” between these three entities actually takes place.
I have used natural PRIMARY KEYS as much as has been possible, in order to capture the meaning of every prime attribute and make it more evident not only in its original entities, but also when it migrates to related ones. If you feel comfortable with my proposed model, it will be more easy for you to determine the stability of each one of such KEYS, and decide if you keep them or replace them with different ones, since you are, of course, involved in the actual context where the database is going to be used.
Subsequent conclusions
By virtue of comparing our models, discussing the important subjects and clarifiying the pending points via comments, we have have been able to confirm most of the things that you have already defined in your model:

The “three way” relationship between Service, Switch and VLAN that I proposed in my preliminary is totally unnecessary, since what it is actually relevant in your context is stroring only what Service is broadcast by a particular VLAN. In this manner, a Service is related to a Switch by means of its association with a VLAN.
The Service.RateLimit, the SpeedProfiles defined for a specific VLAN and the CPEPort.RateLimit are all necessary, and you will manually decide which speed is set for a particular set of entities, since you have determined an overriding order for this aspectt.
CPEPorts are not directly related to Switches, but CPEPorts have a direct relationship with VLANs, since you have to set a specific CPEPort to receive a given Service that is supplied by a particular VLAN. In this way, as you have pointed out via comments, it is necessary to ponder the possiblity of dropping the Service - CPEport relationship that you have presented in your diagram.
The stability of the used PRIMARY KEYS has been identified.

This Network Provisioning Data model (also uploaded as a .PDF document to Dropbox), which is based on yours and also on the series of deliberations that we have kept over the last weeks, depicts these conclusions.
The Party entity
I have included the Party[1] entity in the data model since I think that it can be very useful to provide a means to represent both Organizations and Persons as a single entity, while also keeping their particular attributes in two corresponding separate entities. This scenario is an occurrence of what is commonly referred as supertype-subtype cluster (an exclusive one, in this case) in which Party has been defined as the subertype, and Organization and Person as its subtypes. You may find this answer of help as an introductory resource about this kind of structures.
Thus, your following comment provides a good oportunity for describing its usage:

I quite like the Party table, though I don't think MySQL supports a compound FK in a single column? I'll, most likely, use a single table for both types of customers. Might also use two child tables, to contain the different info for each type of customer. With a 1-0 relation to the customer table.

These are very relevant points that you have brought to the discussion since, in accordance to the structure depicted in the new data model, it is not necessary to use a compound FOREIGN KEY in a single column, because Organization.OrganizationId and Person.PersonId are PRIMARY KEYS that, at the same time, serve as FOREIGN KEYS that draw their values from a corresponding row contained in Party.PartyId. This is an example of what Dr. E. F. Codd defined as Primary Keys on a Common Domain[2].
This way, as you have already skillfully determined, you can use a single table for storing the data pertaining to the role played by a particular Party (Organization or Person) as a Customer, so I have included the latter entity in the model. For instance, suppose that the MaximumOverdue for customers will be arrranged on an individual basis, so this datum should be stored in the Customer table. It is also worth mentioning that the Customer.CustomerId PRIMARY KEY column expresses the role name assingned to Party.PartyId in this specific entity and, as such, CustomerId always takes its values from the last-mentioned column.
Implementation
The follwing are some important aspects that must be taken into account when implementing exclusive supertype-subtypes relationships:

Each supertype row must always have one corresponding subtype row, that is, there should not be a single supertype row without one subtype counterpart stored in the database. This means that, in this particular scenario, you should make sure that there is not a single Party row without an Organization (or Person) row.
Every supertype occurrence must only be complemented by the correct subtype
instance, i.e., the value contained in the subtype discriminator attribute (which is Party.PartyTypeCode[3] in this case) has to be correct for the respective subtype row, and there should be no chance for a supertype to be supplemented by one row of the wrong subtype. In other words, you should create a method in order to assure that, when you have a Party that contains a PartyTypeCode value that indicates that such Party is an Organization, it is only complemented by a row contained in the Organization table, and never by a row contained in the Person table.

In order to cover these aspects in your database, I would highly recommend you inserting every supertype-subtype relationship instance inside a transaction.
Notes
1. A party is a person or group of persons (or people) that compose a single entity in a legal sense, so this term is useful to represent either an Organization or a Person in this particular business domain.
2. Codd, E. F. (Jan. 1990). Introduction to Version 2 of the Relational Model. In The Relational Model for Database Management: Version 2 (pp. 25-26). Boston, MA, USA: Addison-Wesley
3. Since a party has only two possible subtypes, you can remove the PartyType entity and the subsequent Party.PartyTypeCode column, and then handle the Party subtype discriminator by means of a BIT column, which you could possibly denote as IsOrganization or IsPerson.


Check for duplicates in column and determine which row to keep I've found myself running in to this on multiple occasions now where I got a query and would need too further refine it. Where one column might contain duplicates and I need to evaluate which row to keep and which rows needs to be discarded.

So far I've had trouble to apply what I've found on my queries as most examples contains selections from one or two tables where my queries are three or more.

I've tried using group by and having count as these examples show but I just
get a lot of aggregate errors.

I think what I'd like to do would be to use the table created from my query and then query that one if it's possible and basically cull rows which contains duplicates in one of the columns depending on some criteria. For example if one row contains a datetime remove all except the newest.

SELECT
    stamps.stampsnr AS 'stampsnr' ,
    stamps.time AS 'time' ,
    stamps.amount AS 'amount' ,
    products.productnamn AS 'productnamn' ,
    products.Artikelnummer AS 'Artikelnummer' ,
    FContainer.id AS 'FlaggId' ,
    FContainer.FlaggId AS 'Flagga' ,
    Tempo.tempo AS 'nuTempoNr' ,
    Tempo.amounttempo AS 'amountTempon'
FROM
    stamps
INNER JOIN Tempo
ON  stamps.temponr = Tempo.temponr
INNER JOIN products
ON  Tempo.productnr = products.productnr
INNER JOIN FContainer
ON  stamps.ID = FContainer.id
WHERE
    ( stamps.time > '" & dtmYesterday & "' + ' 06:00:00' )
    AND ( stamps.time < '" & dtmNow & "' + ' 06:00:00' )
    AND FContainer.flaggid = 5
ORDER BY
    time;

Here is an example query. Now on the table created I'd like to check the column renamed to flaggID for duplicates if any found remove every row except the newest and the time is stored in stamps.time.

As @sabinbio commented, you need to add a ROW_NUMBER:

SELECT *
FROM
 (
   SELECT
      stamps.stampsnr AS 'stampsnr'
     ,stamps.time AS 'time'
     ,stamps.amount AS 'amount'
     ,products.productnamn AS 'productnamn'
     ,products.Artikelnummer AS 'Artikelnummer'
     ,FContainer.id AS 'FlaggId'
     ,FContainer.FlaggId AS 'Flagga'
     ,Tempo.tempo AS 'nuTempoNr'
     ,Tempo.amounttempo AS 'amountTempon'
     ,ROW_NUMBER()
      OVER (PARTITION BY FContainer.FlaggId  -- for each FlaggId
            ORDER BY stamps.time DESC) AS rn -- assign #1 to latest time
   FROM stamps
   INNER JOIN Tempo
     ON stamps.temponr = Tempo.temponr
   INNER JOIN products
     ON Tempo.productnr = products.productnr
   INNER JOIN FContainer
     ON stamps.ID = FContainer.id

   WHERE  (stamps.TIME > '" & dtmYesterday & "' + ' 06:00:00')
     AND (stamps.TIME < '" & dtmNow & "' + ' 06:00:00')
     AND fcontainer.flaggid = 5
 ) AS dt
WHERE rn = 1
ORDER BY time


Replace special character I am trying to replace the character ' with the character ", using:

UPDATE posts SET text = replace(text, ''', '"');

but:

  1 errors were found during analysis.
  Ending quote ' was expected. (near "" at position 59)

MySQL accepts ' and " interchangeably.  So use ' to quote " and " to quote ':

REPLACE(text, "'", '"')

(There are many other solutions, involving hex, conversions, etc, but the above seems simple and straightforward.)

But...  The error message does not look like a MySQL error, so I suspect your query is in a quoted string in your client language.  So...

What client language?  Does it use backslash (\) for escaping things?


Improve speed of stored proc with lots of inserts [Azure-SQL] Running an S0. @@version = Microsoft SQL Azure (RTM) - 12.0.2000.8   Jul 21 2017 14:10:34   Copyright (C) 2017 Microsoft Corporation. All rights reserved.
The stored proc uses 3 tables. One main object with a relation to prices and a relation to features.

I have about 10.000 main objects with per piece 10 features and avg 3000 prices. So that will cause the prices table to have 30 million rows.

I have a C# console app which runs my stored proc per mainobject and have speed up price and feature storage by using types and table values. I have a dictionary in my app with mainobject code and id so I can fill the id of items which do not need an insert. So that I can preserve the primary key, since they don't change much. I had 3 stored procedures at first. One per table, but instead of 3 stored proc executions, I moved to an all in one sp.
Here is my SQL code:

ALTER PROCEDURE [dbo].[OneSpToRuleThemAll]
(
    @code nvarchar(max),
    @name nvarchar(max),
    @lat float,
    @lng float,
    @country int,
    @operator int,
    @id int output,
    @dtFeatures as FeatureType READONLY,
    @dtPrices as PriceType READONLY
)
AS
BEGIN
    SET NOCOUNT ON
-- begin stored proc 1:
    IF (@id > 0)
    begin
        UPDATE MainObject SET
            [Name] = @name,
            Latitude = @lat,
            Longitude = @lng,
            CountryId = @country,
            OperatorId = @operator,
        WHERE code = @code;
    end
    else
    begin
        INSERT INTO [dbo].[MainObject]
           ([Code]
           ,[Name]
           ,[Latitude]
           ,[Longitude]
           ,[CountryId]
           ,[OperatorId])
     VALUES
           (@code, @name, @lat, @lng, @country, @operator);
            select @id = SCOPE_IDENTITY();
    end
-- begin stored proc 2:
    delete from features where MainObjectId = @id;
        INSERT INTO [dbo].[Features]
           ([ExternalId]
           ,[Mandatory]
           ,MainObjectId])
     select [ExternalId]
           ,[Mandatory]
           ,MainObjectId] from @dtFeatures;
-- begin stored proc 3:
    delete from prices where MainObjectid = @id;
    INSERT INTO [Prices]
        ([From]
        ,[To]
        ,[PriceInclDiscount]
        ,[PriceExclDiscount]
        ,[MainObjectId])
    select [From]
        ,[To]
        ,[PriceInclDiscount]
        ,[PriceExclDiscount]
        ,[MainObjectId] from @dtPrices;

END

edit as @S4v1n suggested, I had to post the execution plan. I used https://stackoverflow.com/a/4146573/169714 this code to get the full SQL command and pasted that in SSMS and got this queryplan for one of the mainobjects:

Here is the execution plan of the 2nd stored proc:

You should definitely separate it into multiple smaller procedures. Using just one procedure will cause a lot of locking and blocking, and generally degrade performance.

As for optimizing:

The first statement is update that updates data based on a @Code provided. Unless if its a clustered index created on it, you should create a nonclustered index on a Code column so you could prevent the whole table scan just to find the code.

Same logic could be applied for deleting. delete from prices where MainObjectid = @id; Having an index on MainObjectID, and generally on any Foreign Key column would provide you decent performance benefits (Whether you delete, or join the table, or even update records in parent table).

As you are inserting some data and deleting data based on ID of that insert, you could create a procedure that will do the first part of the code (Update or Insert if condition is met) and then return you the value of that ID(How to create objects with output parameters), which you could store in some int var in your c# app. Once you get a value you could execute the 2nd part of the procedure ,which includes deleting and some value inserting. Still it wont give you a best solution as you are inserting and deleting data in the same transaction - meaning it will most likely escalate into object lock, making you unable ( depending on transaction level) to make other select queries on those tables, but it will overall cut the % time of that update/insert initial statement.

In summary keep in mind that smaller and faster transactions are much more effective than larger and complex transactions, for performance and concurrency standpoint.


Locking the database to a single user so that other user wont change it I am using SQL Server CE 4.0. In office the database (.sdf) file are on a server. When I want to change design of the database I have to copy it to local PC and access it since n/w connection to a compact database not allowed.

But when I modify the design of tables I have to be sure that no other user in my office connects to that database and changes the same database. Because I have to again copy the changed database to my server PC and I don't want the design changes made by others. So how can I be sure that no one else connected to the database which I have been editing. Is there any method of locking the database to a single user when I edit the design of the database.

Mark the database file a read-only during the schema change, or run the schema change as sql commands from your app.


Does using secondary replica for read operations still required license? I wanted to move some of the reporting workload to the secondary replica but the following things stopped me:

no writes can be performed (including creating temporary tables);
a license is required;

I guess I might get it wrong... I've checked the docs again and it is said that:

  Though you cannot write data to secondary databases, you can write to
  read-write databases on the server instance that hosts the secondary
  replica, including user databases and system databases such as tempdb.

which basically means I can execute every reporting query.

But I am not able to find any information about the licensing in the docs. Is the licensing requirement off, now?

Yes, it required license as both of the instances are active/running.

Availability Group has set of databases that you want to synchronize to secondary replica. It's not necessary to add all databases to availability group, you can add the databases for which you want fault tolerance.

So if your primary replica goes down only those database are part of the availability group would fail-over to secondary & it becomes primary.

MS doesn't restrict you to use the secondary nodes having read-only secondary replica for read-only purpose only, you can also create and use databases on the secondary nodes.

So, it needs a license for secondary replica whether you use it for read-only or read-write both.

Thanks!


Insert into table with column same values as auto increment column ( computed column ) Say I have a table with "column1" (Primary Key, Auto Increment) and "column2".

How can insert into the table with column2 will have the same values as column1 which is auto increment column?

You can create a table with a Calculated column that it's value is equal to column1.
Each time you insert a row into your table column1 will be inserted as identity and column2 will be euqal to that.

MySql :

CREATE TABLE myTable
  (column1 int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  column2 int);
INSERT INTO myTable (name) VALUES('John Doe')
UPDATE mytable SET column2 = column1;

Best ways for MySql : Calculated column or trigger that I refer you to these answers : Calculated Column MySql , Insert, then update, Create before insert trigger

You can add other columns like name.

Sql Server :

CREATE TABLE myTable
  (column1 int identity (1,1) NOT NULL,
  name nvarchar(100),
  column2 as column1);

INSERT INTO myTable (name) VALUES('John Doe')


how to nest query with avg and sum group by outer in MySQL 5.7 I have a dataset like this:
tenant_code app_id reg_count
  1          1       0
  1          2       0
  2          1       1
  2          2       1
  3          1       5
  3          2       5

and now I want to group by tenant_code in the outer query and sum reg_count,but the reg_count should avg app_id in the inner query first and next sum reg_count in outer, what should I do to make it work? This is the query now I am using but did not work:
select
    sum(
        select avg(b.reg_count)
        from h_tenant_overview b
        where b.tenant_code = a.tenant_code
        group by b.tenant_code,b.app_id
    ) as reg_count
from h_tenant_overview a
group by tenant_code

the result should look like this if success:
tenant_code  reg_count
  1                 0
  2                 1
  3                 5

There is no need to define inner query, try this,

added if condition in app_id count to prevent divisible by zero error

SELECT
    tenant_code,
    SUM(reg_count) AS sum_reg_count,
    IF(COUNT(app_id) = 0, 0, SUM(reg_count) / COUNT(app_id)) AS avg_reg_count,
    IF(COUNT(app_id) = 0, 0, FLOOR(SUM(reg_count) / COUNT(app_id))) AS round_reg_count
FROM h_tenant_overview
GROUP BY tenant_code

Output:
tenant_code | sum_reg_count | avg_reg_count | round_reg_count
1             1               0.5000          0
2             2               1.0000          1
3             10              5.0000          5


Error in 'Master Database Restore' in SQL Server 2012 (Because of difference in SQL Server Build) I have a backup of a master database that was taken from SQL Server 2012 11.00.2218 build.

Now I have a new SQL Server installation with build 11.00.2100. I tried restoring the master database after running in single-use mode but got the error as follows:

  The backup of the system database on the device E:\master_backup.bak cannot be restored because it was created by a different version of the server (11.00.2218) than this server (11.00.2100).

I also tried installing SQL Server 2012 SP2. But still the same error. Please help

Upgrade your destination server to build 11.00.2218 before restoring the backup of master, that's your only chance.

If you can't do that (say, the destination master is corrupted, which is why you are trying to do this restore), then rebuild the master via this method so you can get the instance up, then apply the upgrade, then restore master.


How to partition existing table in postgres? I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to suggestions:

The existing table is the master and children inherit from it. Over time move data from master to child, but there will be a period of time where some of the data is in the master table and some in the children.
Create a new master and children tables. Create copy of data in existing table in child tables (so data will reside in two places). Once child tables have most recent data, change all inserts going forward to point to new master table and delete existing table.

Since #1 requires copying data from the master to the child while it is in an active production environment, I personally went with #2 (creating a new master). This prevents disruptions to the original table while it is actively in use and if there are any issues, I can easily delete the new master without issue and continue using the original table. Here are the steps to do it:

Create new master table.

CREATE TABLE new_master (
    id          serial,
    counter     integer,
    dt_created  DATE DEFAULT CURRENT_DATE NOT NULL
);

Create children that inherit from master.

CREATE TABLE child_2014 (
    CONSTRAINT pk_2014 PRIMARY KEY (id),
    CONSTRAINT ck_2014 CHECK ( dt_created < DATE '2015-01-01' )
) INHERITS (new_master);
CREATE INDEX idx_2014 ON child_2014 (dt_created);

CREATE TABLE child_2015 (
    CONSTRAINT pk_2015 PRIMARY KEY (id),
    CONSTRAINT ck_2015 CHECK ( dt_created >= DATE '2015-01-01' AND dt_created < DATE '2016-01-01' )
) INHERITS (new_master);
CREATE INDEX idx_2015 ON child_2015 (dt_created);

...

Copy all historical data to new master table

INSERT INTO child_2014 (id,counter,dt_created)
SELECT id,counter,dt_created
from old_master
where dt_created < '01/01/2015'::date;

Temporarily pause new inserts/updates to production database
Copy most recent data to new master table

INSERT INTO child_2015 (id,counter,dt_created)
SELECT id,counter,dt_created
from old_master
where dt_created >= '01/01/2015'::date AND dt_created < '01/01/2016'::date;

Rename tables so that new_master becomes the production database.

ALTER TABLE old_master RENAME TO old_master_backup;
ALTER TABLE new_master RENAME TO old_master;

Add function for INSERT statements to old_master so that data gets passed to correct partition.

CREATE OR REPLACE FUNCTION fn_insert() RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.dt_created >= DATE '2015-01-01' AND
         NEW.dt_created < DATE '2016-01-01' ) THEN
        INSERT INTO child_2015 VALUES (NEW.*);
    ELSIF ( NEW.dt_created < DATE '2015-01-01' ) THEN
        INSERT INTO child_2014 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Add trigger so that function is called on INSERTS

CREATE TRIGGER tr_insert BEFORE INSERT ON old_master
FOR EACH ROW EXECUTE PROCEDURE fn_insert();

Set constraint exclusion to ON

SET constraint_exclusion = on;

Re-enable UPDATES and INSERTS on production database
Set up trigger or cron so that new partitions get created and function gets updated to assign new data to correct partition. Reference this article for code examples
Delete old_master_backup


Oracle - restore a single table In a large database, an activity was performed to upload the data.
Due to some mistake, a month's data was entered again, which was left in the temp holding table.
So, to cover this up, we took dump of 2 tables from the back-up restored on another machine.
Renamed the tables where error occurred and restored from the backup.
But when the tables were restored the constraints failed to load as the same name is already in use.
Used the oracle exp/imp commands to export and import the data.

I'm not a DBA, but need help on this issue.

You could use Flashback to return the table data back to its original state.

Is Flashback enabled?

Database needs to be in archive mode:

select log_mode from v$database;

Should return "ARCHIVEMODE"

Check if flashback is enabled:

select flashback_on from v$database;

Should return "YES"

If "NO", you can enable flashback by runing:

alter database flashback on;

Check undo retention:

show parameter undo

Default is 900 seconds (15-minutes). So you can Flashback a table to 15-minutes ago using this command:

Alter table t enable row movement ;

FLASHBACK TABLE t TO TIMESTAMP TO_TIMESTAMP ('2015-aug-11 12:00:00', 'YYYY-MON-DD HH24:MI:SS');

Other Flashback commands:

Flashback database = > enables you to take the entire database to a past point in time (using flashback logs, db_flashback_retention_target).
Flashback drop => lets you retrieve accidentally dropped tables and indexes (using the recycle bin).
Flashback table => lets you recover a table to a time in the past (using undo data).
Flashback query => lets you query and restore data rows to a point in time (using undo data).

For Flashback table using SCN see this link:
https://oracle-base.com/articles/10g/flashback-10g#flashback_table


restore table from .frm and .ibd file? I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install mysql-server, I believe version 5.1 was installed. After I copy the ddms directory under /var/lib/mysql, some of its tables work fine, these are the tables with an associated set of three files: a .frm file, a .MYD file and a .MYI file.

However, there are two tables with a different set of files: a .frm file and a .ibd file. These two tables didn't show up in the table list in phpMyAdmin. When I look at the error log, it says:

[ERROR] Cannot find or open table ddms/dictionary_item from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.

Please help with restoring these two tables. Thanks.

InnoDB tables cannot be copied the same way that MyISAM tables can.

Just copying the .frm and .ibd files from one location to another is asking for trouble. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.

I wrote two posts in DBA StackExchange about this tablespace id concept

Table compression in InnoDB? (under the heading 'Restoring Databases')
How to Recover an InnoDB table whose files were moved around

Here is excellent link on how to reattach any .ibd file to ibdata1 in the event of mismatched tablespace ids : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should come to the immediate realization that copying .ibd files is just plain crazy.

You could apply the suggestions from the Chris Calendar link, or you could go back to the old installation of mysql, startup up mysql, and then mysqldump the ddms database. Then, import that mysqldump into your new mysql instance. Trust me, this would be far easier.


How to calculate a total by comparing current and next row I have a table with some rows:

I have a few questions to accomplish the task altogether. Is it possible to calculate total of transmitted and received based on checked_date and ip and connect_time using mysql query? If yes, can I use logical operators to calculate total of transmitted and received? To get correct total transmitted and received I need to use a formula like:

if connect_time[i] > connect_time[j]
then total += transmitted[i]+transmitted[j]
else total += ABS(transmitted[i]-transmitted[j])
end

How to get it done with this formula, does anyone have an idea? P.S. i = current row and j = next row.

Using user variables, you calculate running total for each line like a spreadsheet

PROPOSED QUERY

SET @total = 0;
SET @curr_trx = 0;
SET @curr_con = 0;
SET @lineitem = 0;
SELECT GrandTotal FROM
(
    SELECT
        (@lineitem:=@lineitem+1) line,
        (@prev_con:=@curr_con),
        (@curr_con:=connect_time),
        (@prev_trx:=@curr_trx),
        (@curr_trx:=transmitted),
        (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
        (@total:=@total+@newvalue) GrandTotal
    FROM mytable
) A WHERE line = @lineitem;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS timyash;
Query OK, 1 row affected (0.22 sec)

mysql> CREATE DATABASE timyash;
Query OK, 1 row affected (0.00 sec)

mysql> USE timyash
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     transmitted INT NOT NULL,
    ->     connect_time INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.35 sec)

mysql> INSERT INTO mytable (transmitted,connect_time) VALUES
    -> (2650131,117987),(6465178,78073),(25905117,159268),
    -> (59178089,410282),(73502942,596408),(75695427,683045),
    -> (77576167,740379);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
|  1 |     2650131 |       117987 |
|  2 |     6465178 |        78073 |
|  3 |    25905117 |       159268 |
|  4 |    59178089 |       410282 |
|  5 |    73502942 |       596408 |
|  6 |    75695427 |       683045 |
|  7 |    77576167 |       740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_trx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_con = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @lineitem = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GrandTotal FROM
    -> (
    ->     SELECT
    ->         (@lineitem:=@lineitem+1) line,
    ->         (@prev_con:=@curr_con),
    ->         (@curr_con:=connect_time),
    ->         (@prev_trx:=@curr_trx),
    ->         (@curr_trx:=transmitted),
    ->         (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
    ->         (@total:=@total+@newvalue) GrandTotal
    ->     FROM mytable
    -> ) A WHERE line = @lineitem;
+------------+
| GrandTotal |
+------------+
|   82876429 |
+------------+
1 row in set (0.08 sec)

mysql>

PROPOSED QUERY EXPLAINED

Look at the subquery

SELECT
    (@lineitem:=@lineitem+1) line,
    (@prev_con:=@curr_con),
    (@curr_con:=connect_time),
    (@prev_trx:=@curr_trx),
    (@curr_trx:=transmitted),
    (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))),
    (@total:=@total+@newvalue) GrandTotal
FROM mytable

If you run this with the variables, you see everything being added up for you line by line

mysql> SET @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_trx = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @curr_con = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @lineitem = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     (@lineitem:=@lineitem+1) line,
    ->     (@prev_con:=@curr_con),
    ->     (@curr_con:=connect_time),
    ->     (@prev_trx:=@curr_trx),
    ->     (@curr_trx:=transmitted),
    ->     (@newvalue:=IF(@prev_con>@curr_con,@prev_trx+@curr_trx,ABS(@prev_trx-@curr_trx))) newval,
    ->     (@total:=@total+@newvalue) GrandTotal
    -> FROM mytable;
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
| line | (@prev_con:=@curr_con) | (@curr_con:=connect_time) | (@prev_trx:=@curr_trx) | (@curr_trx:=transmitted) | newval   | GrandTotal |
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
|    1 |                      0 |                    117987 |                      0 |                  2650131 |  2650131 |    2650131 |
|    2 |                 117987 |                     78073 |                2650131 |                  6465178 |  9115309 |   11765440 |
|    3 |                  78073 |                    159268 |                6465178 |                 25905117 | 19439939 |   31205379 |
|    4 |                 159268 |                    410282 |               25905117 |                 59178089 | 33272972 |   64478351 |
|    5 |                 410282 |                    596408 |               59178089 |                 73502942 | 14324853 |   78803204 |
|    6 |                 596408 |                    683045 |               73502942 |                 75695427 |  2192485 |   80995689 |
|    7 |                 683045 |                    740379 |               75695427 |                 77576167 |  1880740 |   82876429 |
+------+------------------------+---------------------------+------------------------+--------------------------+----------+------------+
7 rows in set (0.00 sec)

mysql>

The proposed query has the subquery return a lineitem number. I get the last one to show the final total.

Remember the formula from your question ?

if connect_time[i] > connect_time[j]
then total += transmitted[i]+transmitted[j]
else total += ABS(transmitted[i]-transmitted[j])
end

The newval column computes the formula


Determine the command type in a DDL trigger / get names and datatypes of new columns Given strings corresponding to DDL commands, how can I determine which DDL command (column addition, constraint addition etc.) it precisely is?

I have a DDL trigger on a database that will be fired whenever any table in the database is altered; a system table will be logging the DDL commands that have been fired on any table in the database.

So the commands will look like:

1. ALTER TABLE [dbo.table] ADD [column] VARCHAR(20) NULL;
2. ALTER TABLE [dbo.table] ADD [column] VARCHAR(20) NULL CONSTRAINT [cons] UNIQUE;
3. ALTER TABLE [dbo.table] WITH NOCHECK ADD CONSTRAINT [cons] CHECK ([column] > 1);
4. ALTER TABLE [dbo.table] WITH NOCHECK ADD [column1] VARCHAR(20) NULL,
   CONSTRAINT [cons] CHECK ([column] > 1);

Is there any way to discern among the above 4 DDL statements? I'm only interested in the DDL that adds a new column to a table, and wish to retrieve only the name and datatype of this newly column.

So far I've been fidgeting with functions like SUBSTRING(), LEN(), and CHARINDEX() without much luck.

Ps. My trigger is able to fetch the name of the table on which the DDL is being performed.

The trigger will simply have one of the above 4 options as input and needs to distinguish when a column change has occurred and only retrieve the new column name and its corresponding data type.

Following an alteration made on a table, the trigger will be fired, this exact alteration command is being logged in one of the system table as part of the CDC feature that I'm using to track changes(cinching my access to the command). This CDC system table also enables me to retrieve the name of the table on which the alteration has been made, ergo i have another keyword i.e. table name that I know from the ALTER command.

The list of events that can be used for DDL Triggers can be found on the following MSDN page: DDL Event Groups. If you look through that list, you will notice that they do not offer a level of granularity below the base CREATE / ALTER / DROP {ObjectType} ... So trapping ALTER_TABLE will get all ALTER TABLE... statements.

Once a DDL Trigger is fired, you need to use the EVENTDATA() function to get the details, in XML form, of the event that fired the DDL Trigger. Different types of commands can have different data points related to the event, so we first need to see what is available for ALTER_TABLE. According to the linked documentation for EVENTDATA(), the various options can be found by looking at the following file:

C:\Program Files\Microsoft SQL Server\{version_number}\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd

Or, we can do a simple test by creating a DDL Trigger and having it do nothing more than return the EVENTDATA() value for us to see how it gets filled out:

CREATE TRIGGER [CaptureAlterTableAddColumn]
ON DATABASE
FOR ALTER_TABLE
AS
  SET NOCOUNT ON;

  SELECT EVENTDATA() AS [AlterTableEventData];

Once that DDL Trigger is in place, run the following to see what we have to work with:

CREATE TABLE dbo.AlterTableTest (Col1 INT);

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest ADD Col2 INT;
ROLLBACK;

That should return the following:

<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2015-08-27T14:34:48.730</PostTime>
  <SPID>55</SPID>
  <ServerName>DALI</ServerName>
  <LoginName>Dali\Solomon</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>Test</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>AlterTableTest</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <AlterTableActionList>
    <Create>
      <Columns>
        <Name>Col2</Name>
      </Columns>
    </Create>
  </AlterTableActionList>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
                QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>ALTER TABLE dbo.AlterTableTest ADD Col2 INT;
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

So this looks promising. The <AlterTableActionList> element has sub-elements <Create>\<Columns> so now we have a way that doesn't require imprecise text parsing with SUBSTRING, etc to determine if this ALTER TABLE ADD [column]... vs ALTER TABLE ADD CONSTRAINT.... But what about ALTER TABLE ALTER COLUMN...? If you try:

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest ALTER COLUMN [Col1] BIGINT;
ROLLBACK;

It shows that the <Create> element is now <Alter>. Great. But why is there a sub-element for <Columns> with a <Column> sub-element instead of it just being:

<Create>
  <Column Name="Col2" />
</Create>

If you try:

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest ADD Col3 INT, Col4 DATETIME;
ROLLBACK;

It will show:

...
<Create>
  <Columns>
    <Name>Col3</Name>
    <Name>Col4</Name>
  </Columns>
</Create>
...

At this point we have the following:

SET NOCOUNT ON;

DECLARE @Columns TABLE ([ColumnName] sysname NOT NULL);

INSERT INTO @Columns (ColumnName)
  SELECT t.c.value(N'(./text())[1]', N'sysname') AS [ColumnName]
  FROM   EVENTDATA.nodes(N'/EVENT_INSTANCE/AlterTableActionList/Create/Columns/Name') t(c);

IF (@@ROWCOUNT = 0)
BEGIN
  RETURN; -- no columns added, so exit
END;

DECLARE @Query NVARCHAR(MAX);
SET @Query = EVENTDATA.value(N'(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]',
                             N'NVARCHAR(MAX)');

Now you just need to parse @Query using the values in @Columns as guides for where to start looking for the datatype. Given the possible variations of how T-SQL can be structured, it will require Regular Expressions to properly extract the datatype(s). You might already have some SQLCLR functions for that, or you can get the Free version of SQL# (which I am the author of) which has several RegEx functions.

UPDATE (2016-07-08)

I originally had thought that the query from EVENTDATA() needed to be parsed in order to get the datatypes, but @ErikE had the great suggestion to check the actual columns that had been added. Since the column names naturally need to be unique, and we have the column names already, that makes it easy to JOIN to the system catalog view sys.columns** and then to a couple of others to get the rest of the info. The final result is:

CREATE
--ALTER
TRIGGER [CaptureAlterTableAddColumn]
ON DATABASE
FOR ALTER_TABLE
AS
  SET NOCOUNT ON;

  -- SELECT EVENTDATA() AS [AlterTableEventData]; -- uncomment line for debug

  DECLARE @Columns TABLE ([ColumnName] sysname
                                       COLLATE Latin1_General_100_BIN2
                                       NOT NULL,
                          [Datatype] NVARCHAR(50) NULL);
  DECLARE @Temp XML = EVENTDATA(),
          @SchemaName sysname,
          @ObjectName sysname;

  INSERT INTO @Columns (ColumnName)
    SELECT t.c.value(N'(./text())[1]', N'sysname') AS [ColumnName]
    FROM  @Temp.nodes(N'/EVENT_INSTANCE/AlterTableActionList/Create/Columns/Name') t(c);

  IF (@@ROWCOUNT = 0)
  BEGIN
    RETURN; -- no columns added, so exit
  END;

  -- SELECT * FROM @Columns; -- uncomment line for debug

  SET @SchemaName = EVENTDATA().value(N'(/EVENT_INSTANCE/SchemaName/text())[1]',
                                      N'sysname');
  SET @ObjectName = EVENTDATA().value(N'(/EVENT_INSTANCE/ObjectName/text())[1]',
                                      N'sysname');

  SELECT CASE
           WHEN scol.[user_type_id] IN (165, 167, 173,175)
             THEN styp.[name] + N'(' + CASE scol.[max_length]
                                         WHEN -1 THEN N'MAX'
                                         ELSE CONVERT(NVARCHAR(10), scol.[max_length])
                                       END + N')' -- max_length: 1 - 8000, -1 = MAX
           WHEN scol.[user_type_id] IN (231, 239)
             THEN styp.[name] + N'(' + CASE scol.[max_length]
                                         WHEN -1 THEN N'MAX'
                                         ELSE CONVERT(NVARCHAR(10),
                                                      (scol.[max_length] / 2))
                                       END + N')' -- max_length: (2 - 8000)/2, -1 = MAX
           WHEN scol.[user_type_id] IN (41, 42, 43)
             THEN styp.[name] + N'(' + CONVERT(NVARCHAR(10),
                                               scol.[scale]) + N')' -- scale: 1 - 7
           WHEN scol.[user_type_id] IN (106, 108)
             THEN styp.[name] + N'(' + CONVERT(NVARCHAR(10), scol.[precision])
                  + N', ' + CONVERT(NVARCHAR(10), scol.[scale]) + N')' -- prec. & scale
           ELSE styp.[name]
         END AS [DataType],
         scol.collation_name,
         scol.is_nullable,
         scol.is_rowguidcol,
         scol.is_identity,
         scol.is_computed,
         scol.is_sparse,
         scol.is_xml_document,
         scol.xml_collection_id
  FROM   @Columns col
  INNER JOIN sys.columns scol
          ON scol.[name] = col.[ColumnName] COLLATE Latin1_General_100_BIN2
  INNER JOIN sys.objects sobj
          ON sobj.[object_id] = scol.[object_id]
  INNER JOIN sys.schemas sscm
          ON sscm.[schema_id] = sobj.[schema_id]
  INNER JOIN sys.types styp
          ON styp.[user_type_id] = scol.[user_type_id]
  WHERE   sscm.[name] = @SchemaName
  AND     sobj.[name] = @ObjectName;

And a fairly complete test is:

BEGIN TRAN
ALTER TABLE dbo.AlterTableTest
  ADD Col3 INT, Col4 DATETIME, cOl5 NvARchar(13), col6 nvarchar(max),
      col7 datetime2(4), COL8 DEciMAL(18, 9), CoL9 NUMERIC(10),
      col10 sql_variant, col11 float, col12 float(2), col13 float(22),
      col14 varchar(1111), col15 VARchar(mAx), coL16 varbinary(max),
     col17 varbinary(333), col18 binary(334), col19 nchar(789),
      col20 char(77) sparse NULL, col21 xml NOT Null,
      col22 uniqueIdentifier ROWGUIDCOL, col23 AS ([Col3] * 1.2);
ROLLBACK;

** I am using the sys.columns system catalog view (and a couple of others) instead of INFORMATION_SCHEMA.ROUTINE_COLUMNS because INFORMATION_SCHEMA does not contain Microsoft SQL Server-specific properties. For example, the SELECT statement in the Trigger references the following columns (among others) that are not present in INFORMATION_SCHEMA.ROUTINE_COLUMNS:

is_rowguidcol
is_identity
is_computed
is_sparse
is_xml_document
xml_collection_id

This list was taken from SQL Server 2012 (columns are the same for SQL Server 2014). If you are running on SQL Server 2016 then you will have additional columns that might need to be incorporated into whatever this Trigger is doing. And if you are running on SQL Server 2005, 2008, or 2008 R2, then you will have a few less columns (e.g. is_sparse was added in SQL Server 2008).


Is there an equivalent of "OPTION(RECOMPILE)" or "WITH RECOMPILE" for an entire connection? I'm curious.  Is there any way to prevent SQL query execution plans from being cached for an entire connection's duration?

Can I run a command that basically says, "use OPTION(RECOMPILE) for all subsequent commands until I tell you to stop doing so?"

I'm acutely aware of all the performance trade-offs here, and I know this is not a step to be taken lightly.  However, I'm in a unique situation where this behavior may be advantageous.

UPDATE: I found trace flag 253, but cannot find official mention of what exactly it does: https://stackoverflow.com/questions/2596587/what-does-sql-server-trace-flag-253-do

Execution plans do not last for the duration of a connection: they are shared across all connections.

It has to be specified per query because any plan cache/reuse issues affect only that plan.

What are you trying to do please, and why do you think it would help?

Edit, after comment

The plan must exist in cache to be used (and reused). When it's cached, it uses memory. Recompiling would use the same memory and use extra CPU etc to recompile.

You said "tons of commands" and "thousands of databases": this is your problem. And probably no "dbo." etc to help plan reuse

Thoughts:

buy bigger server + more RAM
migrate to 64 bit with as much RAM as possible
split databases onto different servers
forced parameterisation
check quality of queries