Specifying the IP address to be used when connecting to an instance

Have a look at How to setup and use a SQL Server alias

Excerpts below:

  Problem

  I have an application that has a specified database connection that I
  either can't or don't want to change. However, I need to move the
  database to a new server. How can I do this easily without breaking
  other things that maybe using this connection and/or database?

  Solution

  By using an alias, we can keep the application configuration the same,
  but tell the operating system to look somewhere else for the database.
  One proviso is that once an alias is created, it is set at the
  operating system level. That means if we have two applications which
  connect to MySQLServer on that computer, once the alias is created,
  both will be re-pointed based on the alias. You can create an alias
  using one of two utilities:

  SQL Server Configuration Manager
  SQL Server Client Network Utility

  SQL Server Configuration Manager

  Let's start by looking at how to do it using SQL Server Configuration
  Manager. In the left pane of SQL Server Configuration Manager, if you
  expand the SQL Native Client Configuration folder, there is a
  subfolder called Aliases (see Figure 1). If we click on this
  subfolder, we'll see any aliases that have been defined for the system
  shown in the right pane. In most cases, there won't be any defined. To
  create a new alias, right-click on Aliases and choose New Alias...
  from the pop-up menu.

Figure 1:

  A dialog window comes up (Figure 2) which allows us to specify our new
  alias. In the Alias Name property, you specify the name the
  application uses. For instance, if it connects to a SQL Server called
  MyOldServer, that's what you enter in here. You can also enter an
  alias that appears to point to a named instance, such as
  MyOldServer\MyInstance. You can then specify the server where the
  database really is, to include configuring the protocol, the server
  name, and the port. Clicking the OK button will create the alias. In
  the case of Figure 2, I'm creating an alias called MyOldServer which
  points to my local server's default instance.

Figure 2:

  You should now see the new alias listed in the right pane (Figure 3).

  The next step is to test the connection. You can do so quickly by
  either creating and testing a Data Source Name (DSN), or if you have
  the SQL Server client tools installed, by using SQL Server Management
  Studio (SSMS). I'll do so using SSMS. Figure 4 shows the dialog window
  to open a new connection to a database engine. For Server name:, enter
  in the alias you specified. In the example we called the alias
  OldServerName, so that's what we'll use here.

  Figure 4:

  If the alias is pointed correctly to the new location, a successful
  connection should be established. In SSMS, I was opening a new
  connection for Object Explorer. Figure 5 shows that the connection was
  successfully made using the alias. Note that as far as SSMS is
  concerned, the server name is the alias.

  Figure 5:


A1.

How large are the databases?

If you need logical backup, you can use mysqldump (--single-transaction, --master-data are some options you need to review and use).
For speed, you want to go with mydumper/myloader tool. It will also make sure of taking individual dumps.

Advantage here is you can grab the database you need to restore without having touch other databases of the instance.
If you choose to go with mysqldump, you still can use  mysqldumpsplitter to extract the tables/databases of your choice to export from full dump.

Alternatively, you might want to use the physical backup using Percona's Xtrabackup which provides hotbackup. Here is a post to setup Xtrabackup using Holland framework.

A2.

Backup from Active Master? No!

If we have replication failure on read-only master than priority is to get it fixed and then take the backup. But try to avoid backsups from Active Master.
It's also advised to make sure your backups are stored on a different location other than read-only master!

A3.

Binary log backups provide point-in-time restore. You can store binary logs to a separate partition and have it backedup/snapshot or scripted to copy files to a separate location.

Check out these  two articles: 1 & 2

Hope this is helpful!!


SQL Server - Analyzing Multiple Stored Procedure Statistics

If these are in a live database, then you can use the DMV sys.dm_exec_procedure_stats to pull information for all the Stored Procedures that have been run and cached on the instance.

A basic query to pull all procedures ordered by the Average Duration may look something like this:

USE <DATABASE>;
GO

SELECT
  o.name,
  deps.total_elapsed_time/CAST(deps.execution_count AS DECIMAL(10,2))/1000000 AS AvgDurationInS,
  deps.total_worker_time/CAST(deps.execution_count AS DECIMAL(10,2))/1000000 AS AvgCPUInS,
  deps.total_logical_reads/CAST(deps.execution_count AS DECIMAL(10,2)) AS AvgLogicalReads,
  deps.cached_time
FROM sys.dm_exec_procedure_stats AS deps
INNER JOIN sys.objects AS o
  ON o.object_id = deps.object_id
WHERE deps.database_id = DB_ID()
ORDER BY AvgDurationInS DESC;

However, this doesn't show you all the queries within the Stored Procedure and break down each one by the same metrics. You can do that using sys.dm_exec_query_stats


Can we retrieve historical database growth report for MySQL?

You cannot obtain the info about DB size in the past - server do not store those information.

You can obtain the info about current DB size. It can be obtained from the INFORMATION_SCHEMA.FILES system pseudotable. You can shedule the get of this info in some table using CREATE EVENT, so You will see the dynamic of size change.

But I think (especially assuming You use InnoDB engine with file_per_table enabled) the best way is to obtain DB size info directly from the OS' filesystem (and shedule it using OS' sheduler - cron or another). Of course, You can store that info in MYSQL server table :) - it's not a problem to add it via command-line client.


Way to obtain full list of column dependencies for stored procedure?

Here is my full code as requested (still working on reproducible example for missing dependencies):

CREATE PROCEDURE [dbo].[usp_v9_SprocDocInfo_FullDependency_SingleSproc]
@SprocName NVARCHAR(150) = ''

AS
BEGIN

DECLARE @ObjName NVARCHAR(128) = NULL
DECLARE @rowCount INT = 0
DECLARE @HasNulls BIT = 0
DECLARE @DepExists BIT = 0

--temp table to hold output
--match this against view!!!
CREATE TABLE #TempData
(
FullName NVARCHAR(300) not null,
ShortName NVARCHAR(128) not null,
TableName NVARCHAR(128),
ObjectName NVARCHAR(128),
column_name NVARCHAR(128),
[definition] NVARCHAR(MAX),
LastUpdated DATETIME,
[Type] NVARCHAR(60),
[object_id] INT,
SprocNo BIGINT
)

--temp table to hold pure dependencies
CREATE TABLE #Sproc_FullTableCols
(
RefEntity NVARCHAR(256),
TableName NVARCHAR(256),
ColName NVARCHAR(256),
TypeDesc NVARCHAR(256)
)

--first, grab known dependency data for this particular sproc from the correct view
INSERT INTO #TempData
SELECT FullName,
ShortName,
TableName,
ObjectName,
column_name,
[definition],
LastUpdated,
[Type],
[OBJECT_ID],
SprocNo
FROM v9_Sproc_DocInfo
WHERE ShortName = @SprocName

--next grab any data not covered in the previous query
--this will be tables/views and ALL columns for objects found in sproc
INSERT INTO #Sproc_FullTableCols
--https://www.sqlrx.com/using-sys-sql_expression_dependencies-as-a-single-source-to-find-referenced-and-referencing-objects/
SELECT DISTINCT OBJECT_NAME(referencing_id) AS referencing_entity_name,
    CASE WHEN t.name is null then V.name when V.name is null THEN t.name ELSE NULL END,
    c.name,
    case when t.name is not null then t.type_desc
        when v.name is not null then v.type_desc
        else o.type_desc end AS referencing_desciption
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
LEFT OUTER JOIN sys.tables t on sed.referenced_entity_name = t.name
LEFT OUTER JOIN sys.views v on sed.referenced_entity_name = v.name
LEFT OUTER JOIN sys.columns c on (c.object_id = t.object_id OR c.object_id = v.object_id)
INNER JOIN sys.dm_sql_referenced_entities (N'dbo.' + @SprocName, 'OBJECT') s
ON s.referenced_entity_name = sed.referenced_entity_name
WHERE referencing_id = OBJECT_ID(N'dbo.' + @SprocName)

--clean up
DELETE FROM #Sproc_FullTableCols
WHERE (TableName IS NULL OR ColName IS NULL)-- OR MinorRef IS NULL)

WHILE @HasNulls = 0
BEGIN

    --pull the first row of junk data from the v9 results
    SET @ObjName = (SELECT TOP 1 ObjectName FROM #TempData WHERE (TableName IS NULL AND column_name IS NULL))

    SET @DepExists = CASE WHEN @ObjName IN (SELECT TableName FROM #TempData) THEN 1 ELSE 0 END

    --see if pull was successful; if so update flag accordingly
    IF (@ObjName IS NOT NULL) SET @HasNulls = 1
    ELSE BREAK

    IF @HasNulls = 1 AND @DepExists = 0
    BEGIN

        INSERT INTO #TempData
        SELECT DISTINCT (N'dbo.' + @SprocName) AS FullName,
            @SprocName AS ShortName,
            @ObjName AS TableName,
            '-' AS ObjectName,
            z.ColumnName AS column_name,
            z.Description AS [definition],
            GETDATE() AS LastUpdated,
            '*' + (N'' + c.TypeDesc) AS [Type],
            OBJECT_ID(N'dbo.' + @SprocName) AS [object_id],
            0 AS SprocNo
        FROM z9_BaseTables_Columns z
        INNER JOIN #Sproc_FullTableCols c
            ON (z.[Table] = c.TableName collate Latin1_General_CI_AI
            AND z.ColumnName = c.ColName collate Latin1_General_CI_AI)
        WHERE z.[Table] = @ObjName collate Latin1_General_CI_AI

    END

    --clean out row used for input
    DELETE TOP (1) FROM #TempData
    WHERE ObjectName = @ObjName
    AND TableName IS NULL
    AND column_name IS NULL

    SET @HasNulls = 0
    SET @ObjName = ''

END

--finally, print results then discard the temp table
SELECT * FROM #TempData

order by [Type] asc, FullName asc, ObjectName asc, TableName asc, column_name asc, LastUpdated asc

DROP TABLE #TempData

DROP TABLE #Sproc_FullTableCols

END

The code posted will create a procedure that grabs known dependency data for a procedure from a view (v9_Sproc_DocInfo) that uses only sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. Then I pull the full list of columns for any table used by the procedure, filter out anything already contained in v9_Sproc_DocInfo, and combine this data with the view data as output.


Need help identifying how to make a function selectable using table() clause and a custom data type

You need to define the function as pipelined. Below works in 19c.
Package header:
create or replace package my_new_package is

  -- Types
  -------------
  type my_data_type is record (
      myfield1          VARCHAR2(10)
      ,myfield2         VARCHAR2(10)
    );

   type my_data_type_TABLE is table of my_data_type;

   -- Functions
   -------------
   FUNCTION get_data(i_id_number varchar2) return my_data_type_table pipelined;

end my_new_package;
/

Package body:
create or replace package body my_new_package is

  function get_data(i_id_number varchar2) return my_data_type_table pipelined IS
    current_row my_data_type;
    all_rows my_data_type_table;
    n integer;

    -- Will update the cursor to pull real data
    cursor cur is
      select '1' myfield1, '10' myfield2 from dual
      union all
      select '2' myfield1, '20' myfield2 from dual
    ;
  BEGIN
    n := 0;
    for rec in cur LOOP
      n := n+ 1;
      pipe row(rec);
    END LOOP;

  END;

end my_new_package;
/

Test:
select * from table(my_new_package.get_data('A'));

MYFIELD1   MYFIELD2
---------- ----------
1          10
2          20


are you sure you're doing what you want? In first query you specified localhost while in second you specified %... so for % you will have to connect to server remotely or using ip.

Check below:

root@server3:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3797
Server version: 5.5.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user user1@localhost identified by 'user1';
Query OK, 0 rows affected (0.21 sec)

mysql>
[1]+  Stopped                 mysql
root@server3:~# mysql -uuser1 -puser1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3798
Server version: 5.5.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
root@server3:~# fg
mysql
create user user2@'%'; grant select on test.* to user2;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> show grants for user2;
+-----------------------------------------+
| Grants for user2@%                      |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'%'       |
| GRANT SELECT ON `test`.* TO 'user2'@'%' |
+-----------------------------------------+
2 rows in set (0.03 sec)

mysql> \! ifconfig
eth0      Link encap:Ethernet  HWaddr 00:0c:29:db:fe:fe
          inet addr:192.101.86.13  Bcast:192.101.86.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fedb:fefe/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:250027 errors:0 dropped:0 overruns:0 frame:0
          TX packets:259715 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:105217244 (105.2 MB)  TX bytes:29466344 (29.4 MB)
          Interrupt:19 Base address:0x2024

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:158798 errors:0 dropped:0 overruns:0 frame:0
          TX packets:158798 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:33510924 (33.5 MB)  TX bytes:33510924 (33.5 MB)

mysql>
[1]+  Stopped                 mysql
root@server3:~# mysql -uuser2 -p -h192.101.86.13
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3804
Server version: 5.5.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
root@server3:~#


If you are browsing in Excel and want to be able to see dimension attribute members that do not have corresponding rows in a fact table (measure returns nothing), there is a pivot table setting you can use.

Access the PivotTable Options dialog box by right clicking on the pivot table and choosing PivotTable Options. On the Display tab, check the box next to Show items with no data on rows. This will show all values of whatever hierarchy is in your rows regardless of if the measure in the Values section of your pivot table returns a non-blank result.

You can do the same thing for columns by checking the item underneath the one indicated in the image. This option must be set for each pivot table and will apply to all fields in the rows (or columns) when you check the box.


would there by any TR Log Backups for that time period?

If there is no activity at all, there would be not transactions. But there is no harm in taking that backup as a part of your backup schedule (which is every 15 mins).

It will look like -

/*Processed 0 pages for database 'logbackup_test', file 'logbackup_test_log' on file 1.
100 percent processed.
BACKUP LOG successfully processed 0 pages in 0.072 seconds (0.000 MB/sec).
*/

  what would happen if I happen to restore those transaction log ?

You will be able to restore them in sequence or you can possibly skip it as well- if there is NO gap in the Log Sequence Numbers of the log backups..


The "do stuff" part

Create a role, and give it the permissions you want:

USE YourDatabase;
GO
CREATE ROLE StandardUsers;
GO
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE, EXECUTE
  ON SCHEMA::Sec TO StandardUsers;
-- you have to grant object-level rights at the database level
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION
  TO StandardUsers;

Now add the users you want to that role:

EXEC sys.sp_addrolemember @rolename = N'StandardUsers',
  @membername = N'database_principal_name';
-- rinse, repeat

(Note that ALTER ROLE is much better syntax for managing role members, but that syntax wasn't added until SQL Server 2012.)

The "see stuff" part

Management Studio for the most part will not show users objects they are prohibited from seeing. If you do the following to the letter, and then:

CREATE LOGIN splat WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE USER splat FROM LOGIN splat;
GO
EXEC sys.sp_addrolemember @rolename = N'StandardUsers', @membername = N'splat';

Now, connect to Object Explorer with this new login, and go into your database. If you expand the tables node, you should only see tables in the Sec schema.

If you see others, then it might be that this security layer was implemented in a later version of Management Studio (I'm using 2016 RC0, and don't have a 2008 R2 version anywhere within reach). If that's the case, you could always upgrade (the 2012 and 2014 versions can manage 2008 R2 just fine, as long as they aren't doing exotic things like SSIS packages).


Converting Access SQL HAVING command to T-SQL

Create the view without the HAVING clause, and when you query the view, filter it using:

WHERE Guia = ????

...with your "????" being the value that you're passing in from your form.


Using Azure MSSQL "contained database" how do you force a user to reset their password?

This is currently not supported and you cannot use Must_Chance, Check_Policy or Check_expiration as of now.

  Keyword or statement option 'check_policy' is not supported in this
  version of SQL Server.

You can vote for this in the forum :

https://feedback.azure.com/forums/217321-sql-database/suggestions/10491111-azure-sql-database-password-force-change-on-first


Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving?

Yes, it will catch up, using streaming only, if (and only if), the number of WAL segments generated since the last update on the standby is less than the value of wal_keep_segments in postgresql.conf.  This is covered in this section of the documentation: Replication


Round-tripping column datatype causes size of table to grow

When changing the data type of a column, SQL Server will choose to either:

Change the metadata only;
Change the metadata and read all the existing values to ensure they fit; or
Change every row physically

Even if every row must be changed, SQL Server still take steps (where possible) to prioritize speed over final size, on the basis that we want DDL changes to complete as quickly as possible. Optimizing the storage space can wait for a maintenance window.

Changing float to smallint can be accommodated within the existing space allocated for the row, but it does leave some unused space. As has been mentioned, this can be reclaimed by fully rebuilding the changed structure.


How to express an empty relation (or table) in PostgreSQL?

Your last attempt can be changed to:

select count(*) from ( select where false ) t

Which however is not standard SQL.

The only way I can think of in standard SQL is to use another subselect based on a VALUES clause that returns nothing:

select count(*)
from (
  select *
  from (values (null) ) x
  where false
) t

Or use an intersection that is empty:

select count(*)
from (
  select 1
  intersect
  select 2
) t

That's partially standard SQL, because a FROM clause is required by the standard.


Same schema, same data, same execution plan?

The hardware configuration can affect plan selection as I discuss in my answer to the related Q & A:

difference in execution plans on UAT and PROD server

The question doesn't quantify the "3x" performance difference, but assuming it is important enough to be concerned about:

You can force the broad shape of a plan completely using a Plan Guide or USE PLAN hint. This will prevent the optimizer choosing a different plan shape over time as the data volume and distribution changes, so it is important to review forced plans regularly.
You can often get closer to the desired plan shape with targeted FORCESEEK hints on tables that should be accessed by a seek (e.g. driven by a nested loops join) rather than a scan (with a hash or merge join). In your case, the FORCESEEK hint(s) could be applied to Account and/or CustomerLocation. This may be preferable to forcing the whole plan shape as in the previous point, as it gives the optimizer freedom in other areas of the plan.
You could give the optimizer a better chance with the @country is null or ca.Country = @country predicate by using a RECOMPILE hint, or writing two separate queries (one for the NULL case; the other for non-NULL) surrounded by an IF @country IS NULL test. Try the two separate queries first.
You should be able to avoid the sort spill with a QUERYTRACEON (7470) hint. This is documented in:

FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct
There is no supported way to force a Stream Aggregate (over a Sort Distinct), without forcing the whole plan.
You should consider indexing the heap table variable (a PK at least), and/or using a temporary table instead.

I would start by writing the query as two alternatives (or using the RECOMPILE hint, if you can tolerate a recompilation on each execution), with the 7470 trace flag enabled, and go from there. Plan forcing would be my least favourite option.

If the server has a very large amount of memory, and plans routinely prefer memory-consuming operators when they should not, you could consider testing your whole workload with TF 2335 as in the linked Q & A. I would try the more targeted approaches first.

See also my article: Parameter Sniffing, Embedding, and the RECOMPILE Options


Increasing the decimal precision abruptly increased the size of table

Keep in mind that the dropped column is still physically present in the row.

But the real killer is probably that, if this is a heap (table w/o clustered index), then row updates that increase the row size may cause an avalanche of forwarding. Consider that if you start with pages at nearly 100% full then any row increase will cause the row to no longer fit in the page, so a forward record has to be left in place and the row has to be placed somewhere else. Repeat this for every row (extreme case) and you have a pretty nasty scenario.

If is a B-Tree then the same scenario will cause page splits which are better because during the split the copied rows reclaim the space between the rows on the page, which the heap forwarding case does not.


Programmatically copy index from one table to another, via a stored procedure?

This is a really complicated thing to do in SQL code.  It's complicated because indexes have so many options.  Potential issues/complications:

Covering indexes
Included fields
Column order
Filtered indexes
Complicated index types (xml indexes, etc)

You will be much better off either scripting these out in SSMS or using the .NET SMO framework for this.

To appropriately account for all the above issues will require querying a large number of system tables and using CURSORs, WHILE loops, or other iterative constructs to get things in the right order.

It ain't pretty.


Forms is a Middleware product in and of itself - not a user-developed J2EE application. http://www.oracle.com/technetwork/developer-tools/forms/overview/index.html is the best start page here. This means that to deploy to the cloud, you will need to deploy to a supported stack with the application server and forms runtime in place. That, in turn, most likely means that you would need to be running a custom VM at your cloud provider.

You mention as a reason access from anywhere. This is not a unique ability of the cloud. https://docs.oracle.com/middleware/12213/formsandreports/deploy-forms/using-oracle-forms-services-http-listener-and-oracle-weblogic-server.htm#FSDEP228 covers routing to forms via the HTTP Listener which you could locate in your own DMZ or in the cloud.


Speed of MySQL query for the most recent 800 entries in chronological order

You could break out of that subquery like this:

SELECT Device_ip, Status, timestamp
    FROM ping_results
    WHERE Device_ip = '192.168.1.1'
    AND timestamp > DATE_SUB(NOW(), INTERVAL 800 MINUTE )
    ORDER BY timestamp ASC

That should simplify the query plan a bit, and you're only doing one ORDER BY instead of two.

As everyone else also mentioned, indexes are a good idea for improving performance, and will probably do more than manipulating the query.


Huge difference in execution time of update statement with default value

From Database SQL Language Reference, 11g Release 2 (11.2):

  When you add a column, the initial value of each row for the new column is null.

  If you specify the DEFAULT clause for a NOT NULL column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.

  This optimized behavior differs from earlier releases, when as part of the ALTER TABLE operation Oracle Database updated each row in the newly created column with the default value, and then fired any update triggers defined on the table. In this release, no triggers are fired because the default is stored only as metadata. The optimized behavior is subject to the following restrictions:

  The table cannot have any LOB columns. It cannot be index-organized, temporary, or part of a cluster. It also cannot be a queue table, an object table, or the container table of a materialized view.
  If the table has a Virtual Private Database (VPD) policy on it, then the optimized behavior will not take place unless the user who issues the ALTER TABLE ... ADD statement has the EXEMPT ACCESS POLICY system privilege.
  The column being added cannot be encrypted, and cannot be an object column, nested table column, or a LOB column.

  If you specify the DEFAULT clause for a nullable column, then the default value is added to existing rows as part of this ALTER TABLE statement, and any update triggers defined on the table are fired. This behavior also results if you change a NOT NULL column with a default value to be nullable.

Maybe the default values were actually written to the rows. So check the version of your database and the structure of you table!

There is a hidden parameter _ADD_COL_OPTIM_ENABLED in 11g that can be used to disable this metadata optimization and maybe the parameter COMPATIBLE. This is described in the metalink note

Init.ora Parameter "_ADD_COL_OPTIM_ENABLED" [Hidden] Reference Note (Doc ID 1492674.1)

But all these parameters are valid system wide and not only for a table. So if for one table everything works as expected the parameters seems to be set properly for the instance.

In this metalink noted cited above also the following query is published that shows the columns that were added with this metadata optimization:

select owner, object_name, name
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
    and object_id=obj#;

With this query you can check if your columns are set with this metadata optimization. You can search for your table and column by

select owner, object_name, name
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
    and object_id=obj#
    and object_name='EMS_DATA'
    and name='STATUS'

If this returns

no rows selected

then all rows where updated. The query

select owner, object_name, name
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
    and object_id=obj#
    and object_name='ACC'
    and name='STATUS'

should return at least a row for your ACC table.


How to create a "match result" data type?

Maybe erical already has two columns that keep the "home score and away score as two separate int fields", as Mark and Phil already commented. Then, the remaining part of the question would be whether it is possible to create a user-defined type for a separate string column that stores the match result for faster printing.

So, the answer in this situation would be that yes, it is possible. If unsure about the syntax of the CREATE TYPE statement, it is documented at the following URL:

https://msdn.microsoft.com/en-us/library/ms175007.aspx

The following would be an example of how to do it:

CREATE TYPE MatchType
FROM [VARCHAR](5);

DECLARE @Draw [MatchType];

SET @Draw = '00-00';

DECLARE @Final [MatchType];

SET @Final = '02-01';

SELECT   @Draw  AS [Draw Match]
       , @Final AS [Final Match Sample];

-- Draw Match   Final Match Sample
-- 00-00        02-01

Or, maybe erical was thinking about a composite user defined type. Instead of creating an alias based on an already existing type, what if it were possible to mix two already existing types into a brand new compound?

Microsoft SQL Server does not support that. Instead, Oracle comes with a RECORD type. An example would be the following:

-- Define a user type to hold (HomeScore, AwayScore)
TYPE MatchType IS RECORD
(  HomeScore VARCHAR(2)
 , AwayScore VARCHAR(2));


You should be able to just do a restore/recover over top of the existing Host2.  If you are trying to avoid a complete recovery, then I suspect the answer is you can't, but there are two notable exceptions.

If you never opened the database on Host2 and you have all the archive logs between the first backup and the second, then you can recover Host2 using them.
If you have opened Host2, you still might be able to do this if you have flashback database enabled and can flash back to the point before you opened it.


Remove first all null records from first part of time series until one value is not null and return the rest, in PostgreSQL

select * from tbl
where dt>= (select min(dt) from tbl where col1 is not null or col2 is not null)


why archived log size is small but fast recovery area still has a lot free space

that issue was caused due to lack of space in FRA(Flashback recovery area) if configured or the archive log destination.

option 1.
Restore the archive log to an alternate location which has space

example:

RMAN> run {
set ARCHIVELOG DESTINATION TO '<new location with sufficient free space>';
recover database until time "to_date('2013-12-25 09:00:00', 'YYYY-MM-DD HH24:MI:SS')";
}

option 2.
Set MAXSIZE limit

example:

RMAN> run {
recover database until time "to_date('2013-12-25 09:00:00', 'YYYY-MM-DD HH24:MI:SS')" delete archivelog maxsize 50 K ; }


Copy Azure premium storage to standard storage for redundancy

The only way that I'm aware of would be to just start copying, robocopy is likely the most efficient method.

However, if you are aware writing backups to local disk uses up your alloted IOPS. It is best practice, and recommended by Microsoft, to use the backup to URL for database backups. Doing this does not count against your IOPS for the disk or VM.


In your question, there is not enough information to see where the problem is from technical point of view; however, I can see that there is a problem in communication between you as developer and the DBA. This is not abnormal though as DBAs and devs do not always 'speak the same language'.

Let's go the some points in your question:

You should rely on the 'slow query log' to get your slow queries. Monitoring them in PHP may not be accurate measurement for slowness of the queries.
The query that took very little time in PhpMyAdmin could be cached. Use "SELECT SQL_NO_CACHE", and repeat at times that are not close to each other to avoid hardware level caching.
"SELECT *" vs "SELECT field1, field2": If you don't need all fields in your result, don't use "SELECT *", as it will waste resources sending non-wanted values.
"Too Many Connections" could be of different reasons, one of them is slow queries that lock the tables so other connections are waiting and piled up to the max where the server refuses new connections.

My recommendation is that you talk to your admin and work both as one team, not as two separate teams.

HTH


MariaDB writes at 500kb/s how to get it to write at normal speeds?

query_cache_type=1
query_cache_size=80M

For every write, it must scan the 80M for any references to the table being written to, and purge those entries.  This is an example of where the Query cache hurts performance, and should be completely disabled for production servers.

innodb_flush_log_at_trx_commit=1

cause a disk flush at every "transaction".  If you are using autocommit=ON and writing one row per SQL statement, then this is a huge overhead.  Some workarounds:

BEGIN;
write lots of rows
COMMIT;

and/or

INSERT ... VALUES (...), (...), ... -- Writing lots of rows in a single SQL.

and/or

Switch to SSDs

500KB/s is not a useful metric -- Are they tiny rows or huge rows?  100 rows per second is a typical speed for HDD drives with all the worst settings (innodb_flatc=1, single-row transactions, etc).  Adding any one of the above tips, you might get to 1000 rows/sec.  Going beyond that is possible, but more challenging.

As for local versus remote...  There could be differences in the settings.  There could be significant latency.  A distance of 120ms (a few thousand miles or kilometers) would explain the 10 minutes.

My high speed ingestion blog may be useful.


what monitor before applying changes on the server? - looking for a basic answer - starting point only

I believe starting with Baseline Data would be good to monitor before and after changes.

Information which you might want to capture like:

Basic Information
System Usage
File and Database Sizing Information
Wait Statistics   etc..

If you do not want to use third party tools, you should start looking into collecting above data and when as explained 5 Reasons You Must Start Capturing Baseline Data

Also, in order to set these baselines, you will need to create a database and can capture for duration according to the workload behaviors as explained in
Capturing Baselines on Production SQL Servers.

Also refer to this MSDN forum discussion on same.


Using Oracle 12c database (SCAN) with Oracle 8 client

Oracle will try to have at least one active scan listener on every node of the cluster. They are running on a vip and that vip can be relocated to any of the nodes.
As long as your app does not care in what instance it connects, it does not matter where your connection takes you. Problem comes when your app tries to connect to a specific service that is not on the node where the first returned listener address is located. In that case you are better off with a regular address list.
The scan address is unavailable during maintenance and while it is reassigned.


how to fetch different data on compare rows of two tables

Here is your original query

SELECT ua.package_name, c.id as cid, c.title, c.description, c.iconlink, c.marketlink
FROM `user_app` AS ua INNER JOIN `creative` AS c
ON ua.package_name <> c.package_name
where ua.imei_num = '30c899b5c0f29dbc95418c4173f6f745'
and c.package_name in (12,13)
group by `package_name`

I would like to suggest refactoring the query to retrieve needed rows before doing the JOIN

SELECT ua.package_name, c.id as cid, c.title, c.description, c.iconlink, c.marketlink
FROM
(
    SELECT package_name FROM `user_app`
    WHERE imei_num = '30c899b5c0f29dbc95418c4173f6f745'
) AS ua INNER JOIN
(
    SELECT * FROM `creative`
    WHERE c.package_name in (12,13)
) AS c
ON ua.package_name <> c.package_name
group by ua.package_name;

Give it a Try !!!


You can do this with a simple composite UNIQUE constraint: UNIQUE (entity_id, pin_code). Example table:

CREATE TABLE pin_codes
  ( entity_id  int NOT NULL,
    pin_code   text NOT NULL,
    FOREIGN KEY (entity_id)
        REFERENCES entities (entity_id),
    UNIQUE (entity_id, pin_code)
  ) ;


Best way to design / query multi-level relationship table

I would be tempted to deploy all of the techniques you've mentioned. Let me explain. The normalised Service/ City/ State/ Country is great for OLTP processing. So retain it for that part of the application and treat it as the actual data store.

As you mentioned, this is likely to make search complicated so denormalising would be advantageous. Unlike your proposal I would denormalise into a separate table which exists only to assist search. It is an explicit acknowledgement that a real DBMS on an actual computer does not have the performance characteristics the theoretical computer scientists would like to imagine. By putting it in a separate table one isolates the compromised physical necessity from the preferred, normalised design.

Finally I would combine all the search terms in a single column in this denormalised table. One way would be to together the Service Name, City Name, State Name and Country Name, space delimited, and place a full text index on that column.

Another would be to have three columns - id, name and source_table. The first two come from the data values listed in the question. source_table says which of the normalised tables this comes from. Sample data, taken from the question, would be:

  id     name      source_table
  -----  --------  ------------
  1      Service1  Service
  2      Service2  Service
  ...
  1      KL        City
  3      JB        City
  ...
  2      Penang    State
  ..
  1      Malaysia  Country

The query would match the user's search term against this table's name column:

select
  id,
  source_table
from search_table
where name = <user-provided value>

The returned id and source_table can be used to determine which values to choose from the normalised tables. These can be joined to each other in the normal fashion. Since there are four layers in the hierarchy there can be only four possible queries (service, service/ city, service/ city/ state, service/ city/ state/ country). It would be simple to call the appropriate one like this:

if source_table = "Service"

  select
    <columns>
  from Service
  where id = <search_table.id>

if source_table = "City"

  select
    <columns>
  from Service
  inner join City
    on Service.cityid = City.id
  where City.id = <search_table.id>

... etc.

Be aware that names may be duplicated across these levels (Monaco, for example) and decide what you wish to do with these cases.

The process could be simplified somewhat if names could be guaranteed to be unique and natural keys were used rather than surrogate ones.

I wonder, though, if all this effort is worthwhile. How many rows will there be in these tables? How poor is performance of a query on the normalised tables, with indexes, that compares the user's search value to each name column with ORs between? By creating a representative amount of dummy data and measuring response time on production-like hardware you could save some development, and a lot of maintenance, work.


How to return a result set from oracle procedure ?

You don't need a return statement, because it is using OPEN and FOR... It is the same like RETURN...

The error is in the name of the table, you put it in "" double quotes.  Remove that, use simply the name of the table, and use the type  SYS_REFCURSOR like this:

CREATE OR REPLACE
PROCEDURE ProcSelectEveryThing(cursor_ OUT SYS_REFCURSOR)
AS

BEGIN

OPEN cursor_ FOR
      SELECT * FROM tblTest;
END;


How to get list of dates between From Date and To Date in MySQL

Based on the accepted answer on Stack Overflow here, you can adapt it to fit your requirements:

select *
from emp_unavailablity,
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) datelist from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) as v
where datelist>from_date and datelist <to_date;

Basically we build an inline view with a list of all dates for the next few thousand years, and use that as a list to select from.

Sample SQL Fiddle here.


What does Oracle impdp do when a name clashes with a materialized view or synonym?

Naming conflicts will result in an error and generally cause the object in the datapump file to be skipped. This can lead to all sorts of other dependency issues. The simplest way to avoid that is to drop the target application schema/account entirely and recreate it, using a script like the one here: https://oracle-base.com/dba/script?category=script_creation&file=user_ddl.sql to construct the right user DDL for the user in the target database, or by including the application schema definition in the datapump file and letting datapump create it for you. So...
Step 1: run the script to get the create user DDL.
Step 2: drop the user using the cascade option to drop all objects.
Step 3: run the user DDL script from step 1 to recreate the schema, assuming the password is different or there are any other differences between production and development that you need to preserve.
Step 4: run impdp to load all of the schema objects. If datapump tries to create the schema and it already exists it will throw an error, but you can ignore it.
For your second question, materialized views have two parts: a table with data, and the materialized view definition, which includes the query. Both - the table with data and the definition of the MV - are replicated with datapump when you copy a schema.


Does Oracle support table-level & column-level permissions?

Create a view which simply selects all the other columns form the table in question excluding the columns that you don't want the people to have access to.  Give them rights to query the view and not the table.


How to make a table that could add many same number of different item?

You can't do it correctly with a single table, more exactly your database will be redundant and seriously vulnerable against inconsistencies.

You should read about database normalization.

A normalized solution would be this:

Table users, columns:

user_id (integer, also primary key)
...any other user data, name etc...

Table coupons, columns:

coupon_id (integer, also primary key)
...any other coupon data, name, value, etc...

Table user_coupon (it is the important), it is a so named bridge table, columns:

user_id (foreign key to users(user_id))
coupon_id (foreign key to coupons(coupon_id))
count (integer, shows how many coupons with id coupon_id has the user user_id)

The important thing is that the primary key on table user_coupon must be user_id and coupon_id!


Find out which client IP and Query using highest network Bandwidth from SQL Server

The DMVs sys.dm_exec_connections and sys.dm_exec_sql_text have most of what you need.  Try something like this:

SELECT TOP 10 session_id, connect_time, num_reads, num_writes, last_read, last_write, client_net_address, most_recent_sql_handle, x.*
FROM sys.dm_exec_connections
    OUTER APPLY sys.dm_exec_sql_text( most_recent_sql_handle ) x
ORDER BY num_reads + num_writes DESC

You might also wish to review some of the other DMVs, eg sys.dm_exec_requests and sys.dm_exec_sessions for useful information.


PostgreSQL pl/perl trigger, differentiate null vs empty

You did not specify PostgreSQL version. On 9.0.5 I have observed the same behavior (not sure if it's a bug, see comments below).

This is easy to workaround - you can first test for definedness to catch NULL, and if it passed, test for empty string.

if ( not defined $_TD->{ new }{ $column } ) {
    elog( NOTICE, "New value in column $column is not defined" );
}
elsif ( $_TD->{ new }{ $column } eq '' ) {
    elog( NOTICE, "New value in column $column is empty" );
}
if ( not defined $_TD->{ old }{ $column } ) {
    elog( NOTICE, "Old value in column $column is not defined" );
}
elsif ( $_TD->{ old }{ $column } eq '' ) {
    elog( NOTICE, "Old value in column $column is empty" );
}


How to sort the data fetched from horizontal partitioned tables

It sounds like your architecture has gone past partitioning and is sharded. If you have system software or an application framework in place which can handle sharding that is where this requirement should be implemented.

In order to get the correct sort sequence you will have to have the results from all involved shards in the same place at the same time. There is no way to get some results from one shard and then get more results from another shard and be able to guarantee any sort sequence the user may choose.

One way (as BriteSponge suggested) would be to define a view which spans all the shards. Let the DBMS deal with the complexities of marshalling the various shards' results sets. This would be my preferred solution if it can be managed. It separates the application from the DB implementation details.

If you have to write this yourself there is some hope in the fact you have paging in place. This limits the maximum number or rows you will show in the UI and, therefore, the maximum number of rows that have to be returned from each shard. From the date range you can determine which shards are required. To each shard you submit a query of the form

select <columns>
from <table>
where date between <start> and <end>
order by <user selection>
fetch first <number> rows only

The <number> will be the maximum number of rows you want to show in a single page in the UI. While it is unlikely they will all come from a single shard it is possible so that's what the limit must be.

If your version of Oracle does not support the final limiting clause the following will still work, but will be much less efficient.

Perform a multi-way merge on the resultsets. Stop when you reach the limit for one page in the UI. Discard the remainder of the resultsets.

When the user pages forward, re-submit the SELECT using standard paging techniques.

When the user chooses a different sort sequence amend the SQL to match and resubmit to each shard.

My preference would be to implement this server side in a stored procedure. This will keep application and storage as separate as possible. Also, rather than a multi-way merge, the various resultsets can all go into a single working table which is then sorted and the correct number of rows returned to the application.

If you do implement it client-side, rather than discarding the unused portion of the resultsets they could be cached. This which may reduce the load for subsequent pages at the cost of additional complexity.


PostgreSQL equivalent of MySQL query variables?

This is easy to do inside a PL/pgSQL function (or a DO block):

create function myfunc() returns void language plpgsql as $$
  declare
    aintconst constant int = -333;
    arealconst constant real = -9.99;
    pfid int;
    clientid int;
  begin

    select id from platform where bios like '%INTEL%' into pfid;

    select id from client where platformID = pfid into clientid;

  end $$;

You can also use GUC variables:

--set a session variable
set mycustom.var = 'value';

--use it
select * from mytable where some_column = current_setting('mycustom.var');

Or you can use a CTE with a join:

with myvars as (
  select
    -333::int as aint,
    -9.99::real as areal
)

select
  a.*
from mytable a
join myvars on true
where
  a.thing = aint


Many-to-many tag filter query with a junction table get slower with more tags. Can it be faster?

Let's compare your two queries in part 1. Assume you have 100 videos and 10 tags evenly distributed. Every:

SELECT video_id vid FROM tag_rel WHERE tag_id=...

then produces 10 rows. If you join n of those the amount of rows that have to be examined is 10^n. In your example, n is 5 so 100000 rows. The result is then filtered down to whatever amount of videos that have all those tags, and compared with the videos that satisfy the other predicates. This comparison once again multiplies the numbers of rows that have to be compared.

For second query it is sufficient to examine 1000 rows given the assumptions above. This is of course greatly simplified but should give you an idea of the amount of extra work that has to be done in the first case.

What you want to do is to find the videos that have all tags, i.e.:

FORALL x:p(x)

FORALL is the universal quantifier (see for example: https://en.wikipedia.org/wiki/Quantifier_(logic)). Unfortenate, there is no operator in SQL that directly implements this. One way to overcome this is to translate it to an existential quantifier:

FORALL x:p(x) <=> NOT EXISTS x:NOT p(x)

This can be implemented in SQL, however most people find it more intuitive to count the distinct number of tags per movie and compare it to the length of tag list. Your query would then become:

SELECT video_id vid
FROM tag_rel
WHERE tag_id in (1,9,27,13,67)
GROUP BY video_id
HAVING COUNT(tag_id) = 5 -- 5 is the number of tags

Now we can simply join this back with videos:

SELECT v.* -- dont use * in your code
FROM video v
JOIN (
    SELECT video_id vid
    FROM tag_rel
    WHERE tag_id in (1,9,27,13,67)
    GROUP BY video_id
    HAVING COUNT(tag_id) = 5 -- 5 is the number of tags
) t
    ON t.video_id = v.video_id
WHERE is_x=0
  AND is_y=0
  AND deleted IS NULL;

The list of tags can easily be replaced by a select.

Apparently this question is not as efficient as the original, but I'll still add a possible optimization:

SELECT v.* -- dont use * in your code
FROM video v
JOIN (
    SELECT video_id vid
    FROM tag_rel
    WHERE tag_id in (1,9,27,13,67)
    GROUP BY video_id
    HAVING COUNT(tag_id) = 5 -- 5 is the number of tags
    LIMIT 0, 60
) t
    ON t.video_id = v.video_id
WHERE is_x=0
  AND is_y=0
  AND deleted IS NULL
LIMIT 0, 60;


How to avoid index scans in SQL Server 2005?

Any solution :)

No, not really.

As you said yourself - CellRow is just not very selective - 5 possible values, 100'000 rows = roughly 20'000 rows for each possible value.

SQL Server's query optimizer recognizes this and probably figures it's easier and more efficient to do a index scan rather than a seek for 20'000 rows.

The only way to avoid this would be to use a more selective index, i.e. some other column that selects 2%, 3% or max. 5% of the rows for each query.

PS: Check your execution plan - does it get the values straight from the index, or does it need a "Key Lookup" step to go get the data??

You didn't mention what data types your columns are - if the CellValue isn't too big, you could add it to the index (or at least include it in the index) to avoid costly key lookups:

CREATE INDEX IX_CellRow_CellValues
ON dbo.Cell(CellRow) INCLUDE(CellValue)

You'd still have the index scan, though


How to build a query that gets its values from a file

If the restriction on creating tables includes "external tables", then you need to get management to lift that restriction.  As stated, you can't expect oracle (or any rdbms product, for that matter) to just magically be able to recognize some external file as a data source and be able to process it with SQL statements.  External tables is how oracle does that.  If you read up on external tables in the oracle docs, you will see that it is not a table that contains data within the database, but simply a description of an external file, and that allows oracle to treat that file as if it were an actual table.


Does increased Data Dictionary size affects performance

So a good starting point for this is Martin Widlake's post on the size of the 'data dictionary'. The term can be a bit woolly as there's a bunch of stuff that Oracle stores (such as executable code, job history etc) which may or may not be relevant.

https://mwidlake.wordpress.com/2009/08/03/why-is-my-system-tablespace-so-big/

He points out an example where he has 13 Gb in a data dictionary segment "C_OBJ#_INTCOL#". That is technically a cluster rather than a regular table, but it is really just a special table where the data is arranged so that related items tend to be stored in the same block on disk. That object is storing histogram information for table columns which tells you that, for a specific table/column, you might have 60% of values as US, 25% as Canada and 15% Mexico, or that you have twice as much data for October 2017 as you did for October 2016.

Whether that's a good use of space will depend on your situation. Histograms are basically used to determine the best access path. For example, given a query that has both a date and zip code paths available, which is going to involve less work.

Ultimately, it isn't about the size of a data dictionary, but a more precise question of whether storing additional information in there can help avoid mistakes. Even then, the downside is often the impact of gathering the additional information and keeping it up-to-date rather than the space required to store it.

Additional :
A cluster tends to take up a bit more space than a regular table as trying to keep related data together needs to allow more space for the data to grow rather than moving it around when it doesn't fit.


Way to find when system health file is rolling over

You can get all the system_health event files and their oldest event like this:

DECLARE @file_name AS nvarchar(max);
DECLARE @file_path AS nvarchar(max);

SELECT
    @file_name =
        CAST(st.target_data AS xml).value(
            N'(EventFileTarget/File/@name)[1]', N'nvarchar(max)')
FROM sys.dm_xe_sessions s
    INNER JOIN sys.dm_xe_session_targets st
        ON s.[address] = st.event_session_address
WHERE
    st.target_name = 'event_file'
    AND s.[name] = 'system_health';

SELECT @file_path = LEFT(
    @file_name,
    LEN(@file_name) - CHARINDEX('\', REVERSE(@file_name)) + 1);

SELECT
    files.[file_name],
    MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetime')) AS oldest_event
FROM sys.fn_xe_file_target_read_file
(
    @file_path + 'system_health*',
    null, null, null
) files
GROUP BY files.[file_name]
OPTION(NO_PERFORMANCE_SPOOL, QUERYTRACEON 8649);

Note: for versions of SQL Server that don't support the NO_PERFORMANCE_SPOOL query hint (prior to SQL Server 2016), you can replace that with with QUERYTRACEON 8690 (see Spool operator and trace flag 8690 for details).

Hat tip to Erik Darling for suggesting the query hints, which sped things up significantly in my tests

The dates / times returned by that query are in UTC.  You could use an approach like this one to convert to server local time:

SELECT
    files.[file_name],
    MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetime')) AS oldest_event_utc,
    SWITCHOFFSET
    (
        MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetimeoffset')),
        DATENAME(TzOffset, SYSDATETIMEOFFSET())
    ) AS oldest_event

The code above

gets the filename for the currently-active event file,
then attempts to extract the path using this method,
then uses the sys.fn_xe_file_target_read_file dynamic management function to get oldest event from each file

So one way to accomplish your goal would be to run that query as a scheduled agent job, and log the results to a table.  Then you'd be able to see when the "oldest event" changes for each file (AKA when the file rolls over).

Recall that files can roll over for a number of different reasons.

If performance is an issue, and you are comfortable with PowerShell, you are likely much better off using the approach offered by Dan Guzman here.


First check that your computer name is not more than 15 characters as MSDTC relies on NetBIOS , if it is less than that follow the instructions from Microsoft on How to troubleshoot connectivity issues in MS DTC by using the DTCPing tool.


Change default collation for character set utf8mb4 to utf8mb4_unicode_ci

I don't think there is a way to change that DEFAULT.

Anyway, it would be better to use utf8mb4_unicode_520_ci, which is based on a later Unicode standard.

Just get into the habit of specifying CHARACTER SET and COLLATION on all connections and CREATE TABLEs.  MySQL and MariaDB are gradually changing from latin1_swedish_ci to utf8mb4_0900_ai_ci.  MariaDB is not there yet, but I expect them to move soon.  And "900" is probably not the last Unicode standard.

By explicitly specifying the charset and collation, you maintain control and consistency, even if it is an out-dated pair.

A compromise...

But charset and collation on CREATE DATABASE.  Then any tables built without specific settings will inherit those settings.  And columns within that table will inherit from the table's settings.


Optimizing the table to hold a million records

The possible solution is to limit the resulting set to 10-20 rows as well as sorting not only alphabetical but also by frequency. There are vocabularies exists that contains words along with the probability to be met in the text. May be you have to build your own based on the query statistics. That is called ranking and is used by the search engines. In the simpliest case you have the table like that:

+------+------+
! word ! prob !
+------+------+

and the query is :

SELECT word
  FROM table
 WHERE word LIKE a%
 ORDER BY prob DESC
        , word ASC
 LIMIT 10
;


Why does `pg_lsclusters` not list my Postgres cluster?

In order to use these postgres common tools with a cluster, they need to be used consistently. You would use pg_createcluster -d /path/to/data, instead of initdb. pg_createcluster adds the cluster to postgres-common in order to facilitate these handy operations. It is a wrapper, just like pg_ctlcluster, around the lower-level tools (e.g., initdb and pg_ctl), providing access from one place to operating on all of your clusters. It also has some defaults, such as an automatically configured log location based on the cluster name. this means you don't have to use -l /var/log/my-custom.log when you're creating or starting the server.


You can only achieve such validation at the database level (when not using forms and VBA) using a CHECK constraint

ALTER TABLE Bookings
ADD CONSTRAINT NoDoubleBookings CHECK(
       NOT EXISTS(
             SELECT 1
             FROM Bookings b1, Bookings b2
             WHERE b1.BookDate + b1.BookStart < b2.BookDate + b2.BookStart
             AND b1.BookDate + b1.BookEnd > b2.BookDate + b2.BookStart
       )
)

More details on adding check constraints to Access databases can be found here


Does SQL check each And condition if first condition fails?

What you're talking about is Short Circuit evaluation.

Unlike in CASE statements (except certain scenarios using variables and aggregate functions), where the order is important, you have little control over the order in which the WHERE clause in SQL Server is evaluated (except with some grouping paratheses). So, even if it does short-circuit, SQL Server may choose to evaluate the second clause first, especially if that is referenced as the first column in the index it chooses to use for the query.

SQL Server will still attempt to parse the entire query to make sure it is logically correct, regardless of whether the first AND condition fails.

For example, if I use the AdventureWorks database and look for a City that I know doesn't exist:

USE AdventureWorks;
GO

-- obviously don't use SELECT * in production
SELECT
   *
FROM Person.Address AS a
WHERE City = 'Bournemouth' AND 1/0 = 1

Even though City = 'Bournemouth' will always be FALSE, I still get the error:

Msg 8134, Level 16, State 1, Line 5
Divide by zero error encountered.


find total money of parent node of tree according to a particular pattern

Test Data:

memberid    MemberID    joiningposition packagetype
   RPM00000     NULL         Root   free
   RPM71572     RPM00000    Left    Royal
   RPM323768    RPM00000    Right   Royal
   RPM715790    RPM71572    Left    free
   RPM323769    RPM71572    Right   free
   RPM715987    RPM323768   Left    free
   RPM323985    RPM323768   Right   free
   RPM733333    RPM323985   Right   free
   RPM324444    RPM715987   Right   Royal

create a table to store final value

CREATE TABLE [dbo].[Wallatpayout]
(
    [childid] [varchar](50) NULL,
    [joiningposition] [varchar](50) NULL,
    [DateofJoing] [varchar](50)    NULL,
    [packagetype] [varchar](50) NULL,
    [Total] [int] NULL,
    [FirstPayoutstatus] [varchar](30) NULL,
    [DateofPayout] [datetime]    NULL
)

create procedure [dbo].[sunnypro] as
DECLARE @pId varchar(40) = 'RPM00000';
Declare @Id int set @Id=(select id from registration where hildid=@pId)
begin

-- Recursive
CTE WITH R AS (
    -- Anchor
    SELECT
        BU.DateofJoing,
        BU.childid,
        BU.joiningposition,
        BU.packagetype
        FROM registration AS BU
        WHERE
        BU.MemberID = @pId and
       BU.joiningposition IN ('Left', 'Right')
        or BU.packagetype in('Royal','Platinum','Majestic')
         and BU.Id>@id
   UNION All
    -- Recursive part
    SELECT
         BU.DateofJoing,
         BU.childid,
         R.joiningposition,
        BU.packagetype
    FROM R
    JOIN registration AS BU
        ON BU.MemberID = R.childid
    WHERE
        BU.joiningposition IN ('Left', 'Right') and
      BU.packagetype in('Royal','Platinum','Majestic')
     and BU.Id>@id )
INSERT INTO Wallatpayout
           (childid
           ,packagetype
           ,joiningposition
           ,DateofJoing
           ,Total)
-- Final groups of nodes found
SELECT top 3
R.childid,    R.packagetype,
    R.joiningposition,
    R.DateofJoing,
    Total = COUNT_BIG(*) FROM R where R.packagetype in('Royal','Platinum','Majestic')
GROUP BY
R.childid,
    R.joiningposition,
    R.DateofJoing,
    R.packagetype
    OPTION (MAXRECURSION 0);
end

This code is helpful for multi-level marketing, to find all left node and right node by passing particular id or parentid.


How does order of index affect performance in SQL Server?

In this article someone looked at the performance of an index and the ordering of the data in the index.

Summary
As we have shown creating an index in ascending or descending order does not make a big difference when there is only one column, but when there is a need to sort data in two different directions one column in ascending order and the other column in descending order the way the index is created does make a big difference.


Firstly, why on earth are you storing dates as BIGINT?!

Date fields should be stored as dates, otherwise you could end up inserting invalid dates and you'll have trouble doing basic date calculations, as per now.

To answer your question, you'll need to CAST the InvoiceDate field, firstly to a VARCHAR, then to a DATE type:

SELECT DATENAME(WEEKDAY, CAST(CAST(InvoiceDate AS VARCHAR(10)) AS DATE)) AS [WeekDay]
    FROM Store;


SQL Server 2008 R2 Drops Connections when Anti-Virus Scan is running

I see that you are still running SQL Server 2008 R2 SP1, so I would suggest that you update your software.  If you update to SQL Server 2008 R2 SP2 CU 2 there is a fix for this problem.

You can read about this particular problem at: http://support.microsoft.com/kb/2491214

I would recommend not applying the "workaround" but just getting up-to-date with SP2 CU2 (at least).  You should know that SP2 CU8 is available.


Question about MySQL Semisynchronous Replication

Baron Schwartz wrote an excellent article on this, and I think one key sentence answers your question:

  What does semi-synchronous replication guarantee me then? If there are no errors or timeouts, then the guarantee is essentially that only one transaction per client is likely to be lost if the master crashes.

So semi-synchronous replication minimises (but doesn't entirely eliminate) the chance of this exact problem:

Replicant B's replication IO Thread starts running behind its master - Server A - for some reason
Server A dies - unrecoverably
Transactions are lost forever

As noted in Baron's post (and based on your wording I'm sure you're aware of this), it isn't bulletproof - MySQL will fall back to regular replication if it stops getting a response from the replicants - but irrecoverable data loss is less likely to occur.

The upshot is that it prevents the situation above, it's "out of the box", and it doesn't require any third-party or infrastructure-level solutions (e.g. Galera/Xtradb Cluster, or DRBD). Whether the benefits are worth the cost is likely to vary depending on your situation.


why would rebuild index reduce btree leaf numbers

A living index often accumulates empty spaces in the leaf nodes — e.g. due to deletes. Rebuilding the index will make sure all leafs are filled up to the configured threshold.

Note that I wrote it might reduce the number of leaf nodes. If have only inserts on this table, you won't even get this benefit.

Also keep in mind that the overall context of this article is an argument against rebuilding indexes without really good reason.


SQL Server 2008 encrypted connection using multiple server names

I'm pretty sure that you'll need a certificate which has multiple CNs not one with DNS entries.

  The certificate was generated using Web Server template.

I don't think that's the correct template.  I don't have access to a CA at the moment, but I don't think that template uses the correct server authentication certificate. The note in my book says:

    The certificate must be a server authentication certificate that requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1). The certificate must also be created using the KeySpec option of AT_KEYEXCHANGE; optionally the key usage property will include key encipherment.


Since you are uploading to Azure SQL Database, this is likely to do with how fast they will let you push log records to the replicas you have. You can't configure those or decrease any throttling they're going to do; depending on service tier you're likely just going to have to resign yourself to a certain threshold on speed for bulk data transfer, whether you take this speed hit from their throttling, or changes you make yourself by injecting batching / delays to indirectly reduce log writes per time frame.

In other words, the wait isn't documented, and there's nothing you're going to be able to do about it anyway, except maybe move up to a better service tier. But even that is no guarantee, since I am sure there is throttling across the board, and it might even always be the same regardless of tier.


MySQL: How to find MySQL 5.1 (with built-in InnoDB) or MySQL 5.1 (with InnoDB-plugin)?

If it's a plugin then you'll see variable innodb_version:

mysql> show variables like 'innodb_version';
+----------------+----------------+
| Variable_name  | Value          |
+----------------+----------------+
| innodb_version | 5.5.35-rel33.0 |
+----------------+----------------+

InnoDB as a plugin had more features and was more actively developed. That happened due to historical reasons (at that time MySQL was owned by Sun and InnoDB was owned by Oracle).

I wouldn't recommend using 5.1 as it's EOL. 5.1 isn't any faster than 5.5 or 5.6. Why would it be if the huge team in Oracle works on InnoDB  and 5.5 and 5.6 are great releases. It reminds me common myth "MyISAM is faster than InnoDB".


Sql server - Rebuilding Indexes - Does it affect foreign keys?

will this invalidate Foreign Keys from other tables pointing to the table I am rebuilding, or will the links be preserved?

No it wont invalidate foreign keys.

Rebuilding an index drops and re-creates the index thereby removing fragmentation and it reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. FOREIGN KEY constraints do not have to be dropped in advance.

Now if you disable Index then :

The Query optimizer wont be able to use the index and any of your queries that uses index hint referencing the disabled index will FAIL. SQL Server retains the metadata about the index and the usage stats in sys.dm_db_index_usage_stats. Also, Foreign Key constraints are DISABLED.

Disabling Non-Clustered (NC) Index: This will straight away delete the index pages and thereby freeing up the space in the database.

Disabling a CLUSTERED Index: All related NC indexes and views become unavailable and FK's are also disabled. Any queries that reference the table will fail, as a CLUSTERED index is itself the data. Note that the data will still remain, but will be inaccessible. Only way to again access data is to REBUILD the index.


It ensures that the constraint is enabled after it is created.  Your ALTER TABLE statement includes WITH NOCHECK which is the piece that says not to check for existing bad data during the creation of the constraint.

As written, the existing data will not be checked against the constraint because of the WITH NOCHECK in the first statement.  Issuing the second statement will enable the check against the constraint for any future changes to the table that are covered by the constraint, up to the point that an ALTER TABLE [dbo].[T2] NOCHECK CONSTRAINT [FK_T2_T1] is issued.

The statements, as written, are basically saying "Create this foreign key constraint but don't check it against existing data.  Make it active for any upcoming changes to the data."


MySQL still seems to be establishing a connection even when access is denied, causing aborted connects and instability

Sometimes, running FLUSH HOSTS; will clear up the host cache when you can no longer connect. Why ?

According to the MySQL Documentation on FLUSH HOSTS

  Empties the host cache. You should flush the host cache if some of your hosts change IP address or if the error message Host 'host_name' is blocked occurs. (See Section B.5.2.6, “Host 'host_name' is blocked”.) When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host cache enables further connection attempts from the host. The default value of max_connect_errors is 10. To avoid this error message, start the server with max_connect_errors set to a large value.

When you restart mysql, it clears the host cache in a very heavy-handed manner. Therefore, to apply some preventative to this problem, you should be setting max_connect_errors to 1000000000000 (Yes, that's a trillion). Since max_connect_errors is dynamic, a mysql restart is not needed. Just run

mysql> SET GLOBAL max_connect_errors = 1000000000000;

then add the variable to my.cnf like this

[mysqld]
max_connect_errors = 1000000000000

Give it a Try !!!


Is there a collation to sort the following strings in the following order 1,2,3,6,10,10A,10B,11?

No. Collation is about alphabetical sorting, depending on code page, accent, case, width, kana. Numbers characters (0-9) have none of there properties.

So 9 is always after 10B in any sort.

You have to split it up as you noted or sort like this:

ORDER BY
    RIGHT('                              ' + MyColumn, 30)

The length in the right determines how many spaces you have.

You could of course:

have 2 columns to make this unnecessary (and far quicker) and have a computed column to combine them
insist on leading zeros
right justify in a char (a stored version of my RIGHT above)

The latter 2 suggestions are like my RIGHT above and slightly different. Quicker to sort (no processing of the colukmn needed) but more storage required


Postgres datetime sort slow and not using index

Better query

Seems like a perfect occasion for a couple of LATERAL joins, which should be much faster here:

SELECT g.*, p.platform_list, m.metacritic_list
     , r.review_list, n.genre_list, p.release_list
FROM  (
   SELECT *
   FROM   gb_game
   ORDER  BY release_date DESC NULLS LAST  -- !? see below
   LIMIT  25
   OFFSET 0
   ) g
,      LATERAL (
   SELECT array_agg(platform_id) AS platform_list
        , array_agg(id)          AS release_list
   FROM   gb_release
   WHERE  game_id = g.id
   ) p
,      LATERAL (
   SELECT ARRAY (
      SELECT metacritic_id
      FROM   gb_game_metacritic
      WHERE  game_id = g.id)     AS metacritic_list
   ) m
,      LATERAL (
   SELECT ARRAY (
      SELECT review_id
      FROM   gb_game_reviews
      WHERE  game_id = g.id)     AS review_list
   ) r
,      LATERAL (
   SELECT ARRAY (
      SELECT genre_id
      FROM   gb_game_genre
      WHERE  game_id = g.id)     AS genre_list
   ) n
ORDER   BY g.release_date DESC NULLS LAST;  -- repeat inner sort order

Indexes

You need an index on each game_id column in the child tables.
Multicolumn indexes might be even better if (and only if) you can get index-only scans out of them:

gb_release (game_id, id, platform_id)  -- special: 2 additional columns
gb_game_metacritic (game_id, metacritic_id)
gb_game_reviews (game_id, review_id)
gb_game_genre (game_id, genre_id)

The order of columns matters.

And finally, you need an index on gb_game (release_date).
There is no data type called datetime in Postgres. Assuming you mean timestamp (some ORMs map this way).

Either way, if you really mean to ORDER BY g.release_date DESC, a plain index on (release_date DESC) or just (release_date) does the job

If the column can be NULL, I suspect you actually want to sort NULL values last. Adapt your query like I did to ORDER BY g.release_date DESC NULLS LAST. A plain index on (release_date) still works, but is not the optimum. Use a matching sort order in your index:

gb_game (release_date DESC NULLS LAST)

Or (almost equivalent):

gb_game (release_date ASC NULLS FIRST)

More:

Unused index in range of dates query
PostgreSQL sort by datetime asc, null first?

Explain query

You join to multiple tables that can have n related rows for a single row in gb_game, resulting rows in the intermediary derived table are multiplied with every join to multiple rows. That's not only a major performance issue, it's also incorrect. Or at least I assume as much while detailed information is missing. Related:

Two SQL LEFT JOINS produce incorrect result
Is there a way I can speed up queries against this particular view?

I avoid the problem a priori by aggregating each child table separately in LATERAL subqueries. That seems particularly efficient, since you only pick 25 rows total.

While being at it I use a faster ARRAY constructor for single column aggregation (while I still use array_agg() for the case where two columns from the same table are aggregated separately.

Note that the order of elements in each array is arbitrary. You can add an ORDER BY clause in each lateral subquery to determine the order.

The last three lateral subqueries could be replaced with a correlated subqueries:

What is the difference between LATERAL and a subquery in PostgreSQL?


Calculate Percentage with JOINS and original value

One way to get expected output can be
select case when total_count = 0 then 0.0 else count_1*100.0/total_count end as pcnt
from
(
SELECT count(case when b.statition_id is null then 1 end) as count_1,
count(*) as total_count
FROM `bigquery-public-data.new_york_citibike.citibike_trips` as a
LEFT OUTER JOIN  `bigquery-public-data.new_york_citibike.citibike_stations` as b
ON a.start_station_id = b.station_id
)x

Note, it returns 0.0 if total_count is 0 to avoid division by zero.  You may want to return null(casted to proper decimal type ) in such case instead.


Which join to use ? and how to add rank column on the result

With out some sample code to go on I created my own set

I had to join on both ID and Name for the rank in order to prevent cross joins

create table #Table4 (ID int, Names nvarchar(25), Moneys Money)
create table #Table5 (ID int, Place nvarchar(25))

insert into #Table4(ID,Names,Moneys)values(1,'waffles',1000),
   (2,'Pancakes',2000),(3,'Blueberries',3000),(3,'Eggs',4000),(3,'Toast',5000)
insert into #table5(ID,Place)values(1,'House'),(2,'Kitchen')

select x.Rn, t4.ID,t5.Place,t4.Names,t4.Moneys
from #Table4 t4
left join #table5 t5
    on t4.ID = t5.ID
inner join (select t4a.ID, t4a.Names, rank() over(partition by t4a.ID order
     by t4a.Moneys desc) as Rn from #Table4 t4a) x
    on t4.ID = x.ID
    and t4.Names = x.Names
order by ID,RN

Results will look something like this

Rn  ID  Place   Names       Moneys
1   1   House   waffles     1000.00
1   2   Kitchen Pancakes    2000.00
1   3   NULL    Toast       5000.00
2   3   NULL    Eggs        4000.00
3   3   NULL    Blueberries 3000.00


Deadlock debugging with node: log responsible queries

Sure. Just look into the PostgreSQL log file.

This information is not sent to the client for security reasons: otherwise, you could craft SQL statements that deadlock with other queries to see somebody else's statements, which might contain confidential data.


Auto-Shrink, Encryption & Recovery Model properties in SQL Server 2008 R2

The simple recovery model means that you don't have the option of point in time recovery - essentially, if you take a full backup of the database each day, any data you enter after the backup might be lost if there was a problem. You'd restore to how the data was in the last good backup. As long as your data doesn't change that much between full backups, this is the simplest solution.

Full recovery means you also need to take regular transaction log backups as well - it allows you to restore to a point in time if there is a problem. Typically, you might take a daily full backup of the databases, and transaction log backups every hour. The problem is that if the transaction log backups fail, the transaction logs themselves will grow - and fill the discs if it's not fixed.

Auto-shrink is a setting where SQL Server will trying to shrink the database files when it is idle. It causes performance havoc, and will be removed in a future SQL Server release. Don't use it! (gbn added bold!)

Encryption is a bit more complicated, and best left alone unless you really understand all that is involved IMHO.


MySQL restore only Incremental backups with innobackupex

First lets understand that to take an incremental backup you need to keep full backup on the server where you are taking incremental.

Though you take a full backup on SERVER(A) you may push to AWSSERVER(B). It doesn't matter, but inorder to take an incremental you need to keep the full backup copy as source backup.

UPDATE

Below is the approach for incremental, you will be able to get your answers whether the idea you have is a feasible one or not.

After Xtrabackup Full backup is done.

  mkdir -p  /backup/

  innobackupex --defaults-file=/etc/my.cnf --user=mysql
  --password=‘****’  /backup/

From here /backup/2017-06-01_18-01-52  full backup set is obtained.

Take incremental backup with full backup.

  mkdir -p /backup/INCR

  innobackupex --defaults-file=/etc/my.cnf --user=mysql
  --password=‘*****’ --incremental /backup/INCR --incremental-basedir=/backup/2017-06-01_18-01-52 --slave-info

From here /backup/INCR/2017-06-01_18-10-41/ incremental delta data is obtained.

Merge the backups

  innobackupex --defaults-file=/etc/my.cnf --user=mysql
  --password=‘*****’ --apply-log-only /backup/2017-06-01_18-01-52/ --incremental-dir=/backup/INCR/2017-06-01_18-10-41/

From here  /backup/2017-06-01_18-01-52/2017-06-01_18-22-29 Final backup set is merged to parent(FULL BACKUP) and created a sub directory out of it.

Apply logs to Final Backup Set.

  innobackupex --defaults-file=/etc/my.cnf --user=mysql
  --password=‘*****’  --apply-log /backup/2017-06-01_18-01-52/2017-06-01_18-22-29

Now this is your final data set : /backup/2017-06-01_18-01-52/2017-06-01_18-22-29/* , replace data dir to this data set -> Ensure every 1 to 4 steps had no errors and completed OK is found on all outputs.  Test the result incremental backup data on a Test DB and Restore it to see if the server starts without any error messages

If your my.cnf is pointed to /mysql/data/

  service mysqld stop
  mv /mysql/data/* /tmp/MOVED/
  cp -r /backup/2017-06-01_18-01-52/2017-06-01_18-22-29/* /mysql/data/
  chown -R mysql:mysql /mysql/data/
  chmod -R 775 /mysql/data/
  service mysqld start
  Login to mysql

Check error log and do few select queries .


They are redundant. Second index should be usable by any query that would be using the first one.


Why does MSSQL choose scan in execution plan?

Estimates, a huge amount of columns selected and predicate pushdown

The query's estimates are not accounting for the residual predicate on the scan being of a higher cost than the seek + key lookup to get all these extra columns from the clustered index. This results in the clustered index scan + residual predicate being chosen instead of the index seek.

  My version is Microsoft SQL Server 2016 (RTM-GDR)

These estimates on predicate pushdown where improved in SQL server 2016 SP1

Update to improve diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2016

  To improve diagnostics for the scenario that's described in the
  "Symptoms" section, SQL Server 2016 Service Pack 1 (SP1) introduces a
  new showplan XML attribute, Estimated Rows Read. This attribute
  provides the estimated count of rows will be read by the operator
  before the residual predicate is applied. This update is a complement
  to KB 3107397.

This adds the  EstimatedRowsRead="" to the query plan XML, in your case this would be close or matching the residual predicate if the scan is chosen.

This should fix your issue

Residual predicate example

Reading 1.2M rows to return 0

Index scan query Estimated total cost

EstimatedTotalSubtreeCost="0.00449281">

Index seek query Estimated total cost

EstimatedTotalSubtreeCost="0.00672858">

Which is higher than the index scan estimations due to not accounting for the residual predicate, and that is why the lesser performing plan was chosen.

The main solution

The main solution would be upgrading to at least SP1 to add the:

Update to improve diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2016

You should patch sooner and more often, since SP2 CU6 is out as of March 19, 2019, this would be a far better choice.

Another side note, SP1 for SQL Server 2016 adds many additional features such as In memory OLTP, Compression, Columnstore indexes, ....

Other workarounds that may or may not be worth mentioning

Selecting less columns if they are not needed
Adding all these columns to the NC index's included columns
You could try disabling row goals with OPTION(QUERYTRACEON 4138) (maybe)
Using the WITH(INDEX)) Hint

Comparison with SQL Server 2016 SP1

when running a query alike yours, forcing the clustered index to be used on a SQL2016 SP1 version:

The estimated subtreecost is much higher.

EstimatedTotalSubtreeCost="93.6951"

Where your estimated subtreecost for the clustered index scan

<RelOp AvgRowSize="4788" EstimateCPU="1.36996" EstimateIO="185.267" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00448209" TableCardinality="1245280">

Is low

EstimatedTotalSubtreeCost="0.00448209"

With the main difference being

EstimatedRowsRead="1000000"

shown when executing the query on the SQL 2016 with SP1 applied.

And when testing with the NC index specified

CREATE INDEX IX_PARTITION_DATAAREAID_REVRECID
ON dbo.LEDGERJOURNALTRANS(PARTITION,DATAAREAID,REVRECID);

<RelOp AvgRowSize="980" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1000000">

the EstimatedTotalSubtreeCost for the index seek (not total for entire plan) is also low:

 EstimatedTotalSubtreeCost="0.0032831

and the total estimated subtree cost for my test query is very close to yours

EstimatedTotalSubtreeCost="0.00657048">


MSSQL Server 2012 Query Do we Need T-SQL for query with IF Condition?

You're talking about a CASE expression:

DECLARE @Table table (StudentId INT, GradeExam1 int, GradeExam2 int);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (1,95,88);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (2,85,93);
insert into @Table (StudentId,GradeExam1,GradeExam2) values (3,75,87);

SELECT StudentId
    ,CASE
        WHEN GradeExam1 >= 90
            AND GradeExam1 <= 100
            THEN 'A'
        WHEN GradeExam1 >= 80
            AND GradeExam1 <= 90
            THEN 'B'
        WHEN GradeExam1 >= 70
            AND GradeExam1 <= 80
            THEN 'C'
        END AS GradeExam1
    ,CASE
        WHEN GradeExam2 >= 90
            AND GradeExam2 <= 100
            THEN 'A'
        WHEN GradeExam2 >= 80
            AND GradeExam2 <= 90
            THEN 'B'
        WHEN GradeExam2 >= 70
            AND GradeExam2 <= 80
            THEN 'C'
        END AS GradeExam2
FROM @table;

My example of a CASE expression is Standard SQL and not specific to just the T-SQL syntax.

Check here, if you'd like to learn more about the implementation of the CASE expression.


NUMBER_PRECISION is useless except for one case:  ZEROFILL.  Otherwise, it is an indication of how many columns of output are needed to display the largest number.

INT UNSIGNED needs 10 digits.  INT SIGNED needs 11 columns -- a sign (if negative) and 10 digits.

Ignore the column there.  Ignore it when you see int(11).

And don't bother declaring a column to be INT(100) or INT(2); it is still a 32-bit (4-byte) signed binary number.


How to prevent insert row duplicate value in a column and equal the any value

This can be done with a partial index. A bit complicated in Oracle because it doesn't allow a where clause in the create index statement (as e.g. Postgres does).

But you can still trick Oracle in only indexing a sub-set of the rows by exploiting the fact that entries where all index columns are null are not put into the index. So you create a a unique index on an expression that is only not-null if the status is 'ENABLE':

create unique index ix_only_one_enabled
  on table1 ( case when status = 'ENABLE' then idaccount else null end);

The expression will yield null for all rows where the status is something else than enable and those rows won't be put into the index. Which in turn means than only the IDACCOUNT values for rows are put into the index where the status is ENABLE

Note that 'enable' and 'ENABLE' are two different values for Oracle. You probably also want a check constraint on your column to make sure you only put uppercase values in there (or use a trigger),

Unrelated but: you most probably do not want to use CHAR(10) but VARCHAR(10) due to the padding that happens when using the dreaded char data type.


Cumulative data and dates - my joins aren't working

As inspired by a_horse_with_no_name, I moved the right part of the join to a subquery so the where clause doesn't interfere with the join condition

This works as intended:

WITH dates  as (SELECT min(date) as start_date,
                       max(date) as end_date
                from training_training
                where athlete_id = 1)

SELECT  distinct(d.date),
   sum(distance) OVER (ORDER BY d.date)
FROM
  (
    SELECT generate_series(start_date, end_date, interval '1 day') as date
    FROM dates
  ) d
LEFT JOIN
  (
    SELECT
      date,
      distance
    FROM training_training
    WHERE athlete_id = 1
          AND kind IN ('t', 'd', 'i', 'w')
  ) t
ON d.date = t.date

GROUP BY d.date, distance
ORDER BY d.date


Without more information about the size of data, what relevant indexes exist and the number of likely matched that will be in the "IN" list, "it depends" is the best answer you will get. Also I'm assuming you are meaning SELECT <something> FROM <somewhere> WHERE <column> IN <set> - if not then my answer will be less relevant. Giving sample queries in your questions will help improve the answers you get in response.

As a general rule though an IN clause with static values can not be parametrised so you will end up using ad-hoc SQL which is generally best avoided for a number of reasons.

If you are using SELECT within the IN clause and you are checking against a column in the inner select that is a primary key or otherwise declared UNIQUE then you will often find that the query planner will end up doing the same thing as the simple join (certainly this is often the case with MSSQL) so at that point it is a question of style/preference. I don't know if mySQL would see this equivalence or not: to test this use the EXPLAIN method to see what it decides is possible+appropriate.

Do remember though that the results of SELECT stuff FROM here WHERE here.property IN (SELECT match FROM there) could be quite different from SELECT stuff FROM here JOIN there ON there.match = here.property - the above comments are only relevant to circumstances where the two are going to be equivalent. The former will return at most one row for each found in here even if there are several rows in there where there.match = here.property where in the latter case one copy of the row from here will be returned for each match found in there. Where there is a difference in possible output the decision is made for you: use the one that gives the right result! As another general rule (for which there are exceptions, as always with general rules!) do try avoid using the JOIN method with a DISTINCT modifier where the IN variant would work.


Is there a way to 'mark' a single row in a table and automatically unmark others?

Wrong solution.

Have another table with one row that you keep changing.  It contains the PRIMARY KEY of the one marked row.  (Delete the row if you want to have no rows 'marked'.)  JOIN the two tables to get the marked rows.


Best way to organize compound indexes on partitioned table (date by range)?

I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date column.

Not necessarily. Since AUTO_INCREMENT columns don't have to be PRIMARY KEYs, you could instead do something like the following and the AUTO_INCREMENT column would still work.

ALTER TABLE myTable DROP PRIMARY KEY, ADD KEY(myAutoIncrementColumn);

  Should I create a single index on the date column?

You have queries that are doing lookups on the AUTO_INCREMENT column, so I would still keep its index in place (PRIMARY or without). However since 95% of your queries are hitting the date column, you're going to want to add a secondary index:

ALTER TABLE myTable ADD KEY (myDateColumn);

If you changed the AUTO_INCREMENT column from being a PRIMARY KEY to simple a KEY index like I mentioned above, your date column isn't part of any non-unique indexes anymore, so you can now create the partition:

ALTER TABLE myTable
PARTITION BY RANGE ( UNIX_TIMESTAMP(myDateColumn) ) (
  PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-01-01') ),
  PARTITION p1 VALUES LESS THAN ( MAXVALUE )
);


Query Store plan forcing does NOT affect queries on the secondary

Using Query Store to force a plan on the primary certainly looks like it forces the plan on the secondary.

I tried running a query on a non-prod server, and then flushing the query store with sp_query_store_flush_db (which was required to get the data to sync across to the secondary).  Here's the secondary on the left (note the circled warning about being "read only"), and the primary on the right:

Now I'll click "Force Plan" on the right, and then refresh both views:

So the "forcing" at least carried over in the underlying Query Store tables.  This makes sense, given that the articles quoted in the OP make the point that query forcing should remain in-place after a failover:

  Question: Will QDS retain FORCED Plan information when Database failover from Primary replica to secondary Replica?

  Answer: Yes, QDS store Forced Plan information in sys.query_store_plan table, so in case of failover you will continue to see same behavior on new Primary.

But does the forcing behavior actually take place?  I'll now run the same query on both servers.  On the primary, as expected, the "UsePlan" attribute is there in the plan XML:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="11096" CachedPlanSize="288" CompileTime="82"
           CompileCPU="78" CompileMemory="2104" UsePlan="true">

And in the UI:

On the secondary (note the different server name), the plan was not forced.  Here's the same plan XML snippet:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="11096" CachedPlanSize="288" CompileTime="32"
           CompileCPU="28" CompileMemory="1656">

Plan Guides do NOT affect queries on the secondary

I created a plan guide on the primary using this code (table names changed to protect the innocent):

EXEC sp_create_plan_guide
    @name = 'plan-guide-test',
    @stmt = N'SELECT TOP (1000) *
FROM dbo.TableName t
WHERE
    NOT EXISTS
    (
        SELECT NULL
        FROM dbo.OtherTable o
        WHERE t.Id = o.TableName
    );',
    @type = N'SQL',
    @module_or_batch = NULL,
    @hints = N'OPTION (MAXDOP 1)';

The plan guide was, of course, effective on the primary, as evidenced by the execution plan:

<StmtSimple StatementCompId="1" StatementEstRows="1000" ... StatementType="SELECT"
            PlanGuideDB="..._UAT" PlanGuideName="plan-guide-test" ...>

I did confirm at this point that the plan guide was replicated to the secondary.

Running the same query on the secondary, the execution plan is missing all the signs of being forced by a plan guide:

<StmtSimple StatementCompId="1" StatementEstRows="1000" ... StatementType="SELECT"
            QueryHash="0xECF8A24F126EE77A" QueryPlanHash="0x0E93CF7FEAC1B6EA"
            RetrievedFromCache="true" SecurityPolicyApplied="false">


SSRS Report drop down parameters. Use SP or embeded SQL query for datasource?

Apparently there is not a set (or recommended) standard (that I could find). Since development needs vary from project to project the developer should decide during the development process, whether to create stored procedures for their parameter data sources or to simply hard code the drop down values inside the report itself.


The data you are generating from raspberry pi, temperature and humidity sensor is a time series data. MongoDB has documented some best practices and key tips for storing the time series data.

Time Series Data and MongoDB: Part 2 – Schema Design Best Practices

This explains the following schema patterns for time series data.

Scenario 1: One document per data point

Scenario 2: Time-based bucketing of one document per minute

Scenario 3: Size-based bucketing


Configured values different to Running values, even after full restart

Jakub Hromadík originally wrote in a comment:

  This just happened to me. No matter what I did, the running value was always Disabled. What finally did it for me was repairing the SQL Server installation. I'm running SQL Server 2016 SP1

This also worked for me.

Note also:

From the documentation, filestream access level Server Configuration Option

  Before this option has any effect, the Windows administration settings for FILESTREAM must be enabled

and be sure to follow all the steps in How to: Enable FILESTREAM


Is it dangerous to have duplicated entries in a table?

In this case, I would keep the single table and allow repeated song titles.

Lookup tables are used for finite sets (artist, status, city etc). Moving song titles to a lookup table will result in a lookup table nearly as large as the main table. If anything, artist should be a separate lookup table because this will be common attributes for several song entities.

A table will generally store an object or entity, in this case a song. The common attributes for a song can be moved to a lookup table, but song title isn't one of these common attributes. The same song title is usually a coincidence: you'd probably be better with "CoverOf" and "ReReleaseOf" columns to deal with these cases.

The fact of storing the same text in several rows isn't breaking any rules here because it isn't repeated text in the lookup sense

Also see: Proper Use of Lookup Tables