MS SQL Server IP address change considerations I am very new to MS SQL Server Administration and wants to learn on Migration of SQL Server in detail. We are in a Datacenter migration Project where the Windows server (SQL Server is residing in it) is going to be migrated AS-IS from Source to Destination. I would like to know what are the things that we need to take care for the SQL Server IP address connectivity issues and other related connectivity problems. correct me if anything wrong in framing the question and thanks in advance

If you are just changing IP address ONLY, then there are less things to worry.

Check

Change the IP address and restart SQL Server.
If your applications are referencing IP address instead of HOST, then they need to change their web.config files to reflect the new IP address.
Linked servers referencing IP address needs to be changed as well.
If any user workstations are connecting using IP, then they need to flush DNS using ipconfig /flushdns or better just restart their workstations.
If the server is part of Failover cluster then follow this steps as well.

If you want to be proactive and find out the applications connecting to your sql server instance, then use this server side trace and then plan accordingly for downtime.

Note: make sure you assign a static IP address and port to sql server.


SQL Server 2008 Installation pointers for optimizing Performance and Scalability We are working for a client with an asp.net/SQL server application which has been using a SQL Server 2005 system until now.

The client has decided to upgrade the database to SQL Server 2008 R2 and has procured a powerful server machine with 16 processors with enough RAM and installed Windows Server 2008 64 bit on the system.

Now we are tasked with the installation of SQL Server 2008 on the system followed by migration of the existing DB from the SQL 2005 box.

Now I am not a SQL Server DBA nor am I a System Administrator by role. Hence, in spite of reading up a lot of the literature on the net, I am not quite able to make sense of it all or put it in the right order of execution. Can somebody explain me the following items, preferably in terms of bullet lists:

What are the points of consideration during installation of SQL Server 2008, specifically in terms of

Configuring memory usage
Configuring the SQL Server to take advantage of the multiple processors available
Other factors to configure to enable SQL Server 2008 features for performance and scalability
Other points, if any, for configuring the Windows Server 2008 with respect to SQL Server 2008

Note: We will only use the Database Engine services in SQL Server. The applications does not use SSIS, SSRS, etc.

It is SQL Server 2008 or SQL Server 2008 R2 ?? Because in your question you mention that client want to upgrade from 2005 to 2008 R2 and you're asking about migration from 2005 to 2008. So for sake of answer I am assuming it is 2008 R2

Since you will not be using other services like SSRS,SSAS and SSIS it should make you like a little easy. Some suggestions are,

Use separate physical drives for data and log files for all user DBs
Separate physical drive for temp db, backups,system dbs
Aligned drive partitions (however it should be taken care by itself since you are using Win 2008 R2 but better check 'em)
Setup proper auto increment based on your expected growth
Proper recovery mode (Full recovery mode is recommended)

Actually you can find loads of tips for SQL Server database server configuration. Following links can give you some help.
For SQL server 2005/2008 (but can be used for 2008 R2)
Or General MSDN help for SQL Server 2008 R2
Or google for "SQL Server best practices", which can be of different categories like installation, dev, security configuration, administration, migration etc. But I really doubt that you will find all-in-one type of solution somewhere because in most of the cases it really depends on your requirements.


Retrieving changed rows only from a table with 10^7 rows I have a table with about 300 columns and about 107 rows and I have to retrieve changed rows only, ordered by time (not distinct, but changed). The result set may be limited to 100 rows as well.

I've tried the following query with lag window function:

-- Suppose, we want to retrieve data from column1, column2 and column3 fields
-- There may be other fields, though
SELECT
  w."stamp",
  w."column1",
  w."column2",
  w."column3"
FROM (
       SELECT
         o.stamp,
         o.obj_id,
         "o"."column1",
         "o"."column2",
         "o"."column3",
         lag(o."column1") OVER (ORDER BY stamp) AS "_prev_column1",
         lag(o."column2") OVER (ORDER BY stamp) AS "_prev_column2",
         lag(o."column3") OVER (ORDER BY stamp) AS "_prev_column3"
       FROM "table_name" o
       WHERE o.stamp BETWEEN '01.12.2015 00:00' AND '23.01.2016 00:00'
       ORDER BY o.stamp DESC
     ) AS w
WHERE
  w.obj_id = 42 AND w.stamp BETWEEN '01.12.2015 00:00' AND '23.01.2016 00:00' AND
  ("w"."_prev_column_1", "w"."_prev_column_2", "w"."_prev_column_3") IS DISTINCT FROM
  ("w"."column_1", "w"."column_2", "w"."column_3")
ORDER BY w.stamp DESC
LIMIT 100;

However, it takes too much time to complete. Is it possible to optimize this query, or the problem should be solved in another way (e.g., custom function)?

Table definition:

CREATE TABLE table_name (
  id BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('ds_dyn_sequence'::regclass),
  obj_id BIGINT NOT NULL,
  stamp TIMESTAMP NOT NULL DEFAULT now(),
  column1 BIGINT,
  column2 BIGINT,
  column3 BIGINT
  -- Other fields, all BIGINT NULL
);

CREATE UNIQUE INDEX obj_id_stamp_key ON table_name USING BTREE (obj_id, stamp);

The table contains about 104 rows per a hour. The table will be limited to three months, so, the total number of rows will approximately be 2*107.

PostgreSQL version: 9.3

If the version was 9.3 or newer, I would try this rewrite of the query:

SELECT
    w.stamp,
    w.column1,
    w.column2,
    w.column3
FROM
    "table_name" AS w
  JOIN LATERAL
    ( SELECT p.column1, p.column2, p.column3
      FROM "table_name" AS p
      WHERE p.stamp < w.stamp
        AND p.stamp >= '2015-12-01'::timestamp
      ORDER BY p.stamp DESC
      LIMIT 1
    ) AS p
    ON (w.column1, w.column2, w.column3)
       IS DISTINCT FROM
       (p.column1, p.column2, p.column3)
WHERE w.obj_id = 42
  AND w.stamp >= '2015-12-01'::timestamp
  AND w.stamp <= '2016-01-23'::timestamp
ORDER BY w.stamp DESC
LIMIT 100 ;

with the index you have and with an index on (obj_id, stamp DESC)

Unrelated to efficiency: are you sure that you want to use BETWEEN with timestamps? It's more common to work with inclusive-exclusive ranges. In other words, shouldn't this condition:

  AND w.stamp <= '2016-01-23'::timestamp

be?:

  AND w.stamp  < '2016-01-23'::timestamp

With BETWEEN (and <=), you are searching though a range that is 53 whole days long + 1 microsecond (or whatever the accuracy of timestamp is).


How to select the rows affected by an update When performing an update query (the following is just an example; any update query could be used) such as:
update t1
inner join t2 on t1.id=t2.id
set t1.name="foo" where t2.name="bar";
Query OK, 324 rows affected (1.82 sec)

how do you see which rows have been affected (the 324 rows affected in the response)? I tried converting the expression to a select, such as
select * from t1
inner join t2 on t1.id=t2.id
where t1.name="foo";

but this also returns the rows that were already name="foo" before the update, too.
Conceptually, I would like to do something like
select * from rows_affected;

but of course this does not work. Is there a method that will allow the inspection/selection of rows affected by an update query? Or is the only solution to do the select before the update to see which rows will be affected?

This sounds like a job for SELECT FOR UPDATE. Please see MySQL Docs on this
I have discussed this over the years

Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
Jan 02, 2012 : LOCK IN SHARE MODE
Mar 18, 2012 : select for update gives error on indexed column
May 09, 2012 : Transaction Lock Timeouts When Updating a Row
May 13, 2012 : Cannot update certain rows in innodb tables
Aug 10, 2012 ; Similar function NOWAIT in MySQL
Feb 12, 2014 : row locking within ACID transaction innodb


Would appreciate some suggestions on how I should model this data I am setting up a website that will have users. Users can search for songs and (if signed in) save them to a playlist or their library of songs.

I want it to be like iTunes in that all the songs on the the playlists are part of the library so when the user choses to view their library of songs, the songs on the playlists are shown as well as the songs just added to the library. I'd like a song to be able to be stored in multiple playlists.

Right now I am using Mongodb, doing something like such:

var UserSchema = new Schema();

var Library = new Schema({
    songs: [SongsSchema],
    user: Schema.ObjectId
});

var Playlist = new Schema({
title: String,
description: String,
user: Schema.ObjectId
});

var SongsSchema = new Schema({
position: Number,
name: String,
artist: String,
    artistGid: String,
album: String,
    albumGid: String
time: Number,
url: String,
    gid: String,
    location: String (either youtube, vimeo, soundcloud for now),
    playlist: [Schema.ObjectId] (array of object ids that point to playlists?)
});

Any other ideas?

edit: More details:

So what I have is a user, library, playlist, and song model.

Essentially I am trying to model something like iTunes.

A user has a library and the library belongs to that user.
A user can have several playlists and the playlist belongs to the user.
A song can be in many different user libraries and many different playlists that belong to different users or the same user (perhaps even on the same playlist multiple times?).

When a user visits a page they can add songs to a playlist and will also see a list of their current playlists and their library. If they click the playlist it will show all the songs on that playlist if they click library it will show all the songs in their library( so like iTunes all the songs on all the playlists and ones just added to the library).

Not sure of the best way to model this.

NOTE to people with itchy downvote fingers: I know that OP has asked about MongoDB and the answer that follows is RDBMS.  However, if you check out the comments you'll see that I did ask why MongoDB and OP's answer is a presumption of necessity due to performance.  Since nobody has come forth with a Mongo-centric answer in four days, I am going to offer my suggestion, which is to let RDBMS do what it's good at.

Raladical, as I noted in my comments to your question, I think that what you are trying to build is perfectly suited to the relational data model.  Here is a quick data model sketch:

This model makes the following assumptions:

Users can have exactly one library.
Users can have many playlists.
Songs can appear in any number of playlists.
Users can only put songs in a playlist if it's already in their library (Note that there is an easy way around this if that is not your intention.)
Depending on the constraints you put on LIBRARY_ITEM and PLAYLIST_ITEM you can have the same song exactly once or several times in both the library and any given playlist.

You can also easily extend this model to normalize for things like ARTIST and possibly even location, if that makes sense in your application.

This model is normalized (3NF) and if you index it properly it will be very performant.  If you really want MongoDB or if there is something else about your system that is well suited to non-relational then you can definitely do it that way.  If not, you should think about RDBMS for this one.


How can I query the SQL Server Compatibility Level into a variable? I know to query the current Compatibility Level of a database with

declare @dbname sysname
select @dbname = db_name(0)
exec sp_dbcmptlevel @dbname

But this seems to return its result via print and I have found no way to assign the value to a variable and use it to control if ... else ... constructs to execute different code depending on current setting.

You could query the system table sys.databases:

-- for SQL 2005+
declare @level tinyint;
select @level = [compatibility_level]
from sys.databases
where name = 'AdventureWorks'

Select @level
GO

-- for SQL 2000+
declare @level tinyint;
select @level = [cmptlevel]
from sysdatabases
where name = 'AdventureWorks'

Select @level
GO


Single Slave with Multiple Masters. Does this pattern exist in MongoDB? Can we have a single mongoDB remotely (online) which can act as a slave to multiple master mongoDBs (which operate offline)?
For example, I have huge number of transactions which I like to perform on my local MongoDBs parallely on multiple systems (offline).
At the end of the day, I want to push minute part of my offline MongoDB records to my single slave MongoDB which is remote and online.
Does this pattern exist?

I don't think so Single Slave with Multipe Master Possible. As per MongoDB BOL Here Master-slave replication preceded replica sets and made it possible to have a large number of non-master (i.e. slave) nodes, as well as to restrict replicated operations to only a single database; however, master-slave replication provides less redundancy and does not automate failover.

  WARNING
  Deprecated since version 3.6: MongoDB 3.6 deprecates the use of
  master-slave replication.

  Master-slave replication has been deprecated for components of sharded
  clusters since MongoDB 3.2.

Replica sets replace master-slave replication for most use cases. If possible, use replica sets rather than master-slave replication for all new production deployments. This documentation remains to support legacy deployments and for archival purposes only.

For further your ref Master Slave Replication


What SQL Server database version do I need? I am not a database experts but part of the project I am currently interested in accepting requires to do a DB replication.

The system will have many outlets... says Site A, Site B, Site C, etc..

All sites will have a local DB, but only one of the site (says Site A) DB will be a central DB..

Other sites will push their data to Site A once a day (through intranet)....and it's only one way replication.. i.e. Site B does not need to receive data/update/etc from Site A or Site C... so basically Site A will be a hub and other sites will be client to Site A and they only need to push their data to Site A....

What kind of SQL Server edition for each of the sites (assuming that the size of the db is not so big - 10GB will be enough) I am thinking to use SQL Server 2008 R2.

Also, What kind of replication does it require? Merge-replication?

from my understanding so far, I need

Site A: SQL Server Standard (or Enterprise)
Site B, C, etc.: SQL Server Express

Indeed Merge Replication seems to be the best fit for this scenario.
You can also check Transactional Replication with Updateable Subscriptions with queued updates, but it is less suited for situations where the servers are mostly disconnected, and the conflict detection is quite limited.

You are also correct about the versions you need: Standard or Enterprise for the main server, Express for the subscribers.


mysql query to fetch group wise count I am having mysql tables like below

tbl_source_1

SRC_ID_1 | SOURCE_NAME_1
1        | ABC
2        | DEF
3        | GHI

tbl_source_2

SRC_ID_2 | SOURCE_NAME_2
1        | NAME_1
2        | NAME_2
3        | NAME_3

tbl_user

user_id | user_nm
1       | JACK
2       | MACK

tbl_data

dta_id | SRC_ID_1 | SRC_ID_2 | user_id | data_name
1      | 1        | 1        | 1       | XYZ
2      | 1        | 1        | 1       | SAD
3      | 2        | 2        | 2       | BAD

now i want COUNT of data (group by source_2) and want result like below

SOURCE_NAME_1 | user_nm | NAME_1         | NAME_2 | NAME_3
ABC           | JACK    | 2 (it's count) |  0     | 0
DEF           | MACK    | 0              |  1     | 0

group by source_1 and user and count by source_2

Use pivot emulation:

SELECT SOURCE_NAME_1,
       user_nm,
       SUM(SOURCE_NAME_2 = 'NAME_1') NAME_1,
       SUM(SOURCE_NAME_2 = 'NAME_2') NAME_2,
       SUM(SOURCE_NAME_2 = 'NAME_3') NAME_3
FROM tbl_data
LEFT JOIN tbl_user ON ...
LEFT JOIN tbl_source_1 ON ...
LEFT JOIN tbl_source_2 ON ...
GROUP BY SOURCE_NAME_1, user_nm

If SOURCE_NAME_2 values list is not static, use stored procedure which pivot data, or (better) count data only, then pivot it on the client side using client report subsystem.


How to get same value in a unique column but differentiated by other column values? I have a table like this

 ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID
---------------------------------------------------
  1 |   VITAMIN A  |       1         |      1
  2 |  PARACETAMOL |       2         |      1

PRODUCT_NAME must be unique for same manufacturer and same package ID, BUT it can be duplicate if manufacturer ID or Product ID are different.
I tried using UNIQUE but it is rejecting same values with different Manufacturer ID or Package ID

My desired output would be like :

 ID | PRODUCT_NAME | MANUFACTURER_ID | PACKAGE_ID
---------------------------------------------------
  1 |   VITAMIN A  |       1         |      1
  2 |  PARACETAMOL |       2         |      1
  3 |   VITAMIN A  |       2         |      1

Notice third value of PRODUCT_NAME 'VITAMIN A' is same but MANUFACTURER_ID is different.
Do we have any work-around for this other than UNIQUE?

I think the problem here is that you were unaware that UNIQUE constraints can contain more than one field. Based on your question, I suggested 2 fields, i.e. both product and manufacturer.

It appears now (following comments) that 3 are required, as originally suggested by @ypercube.

From the link I originally posted here, the syntax you require is as follows:

CREATE UNIQUE INDEX 'my_product_uq'
ON my_product (product_name, manufacturer_id , package_id);


Execution time of triggers fired when a procedure is executed in postgresql I have a stored procedure which may delete and/or insert tuples into a database table. This table has an AFTER INSERT OR DELETE OR UPDATE trigger. Basically I want to retrieve how much time was spent in this trigger as a result of changes that were caused by the stored procedure.

I have tried to use EXPLAIN ANALYZE for this:

EXPLAIN (FORMAT JSON, ANALYZE, TIMING) SELECT f(x);

However the result that I get contains no information about triggers:

[
   {
      'Plan':{
         'Node Type':'Result',
         'Parallel Aware':False,
         'Startup Cost':0.0,
         'Total Cost':0.26,
         'Plan Rows':1,
         'Plan Width':4,
         'Actual Startup Time':37.086,
         'Actual Total Time':37.087,
         'Actual Rows':1,
         'Actual Loops':1
      },
      'Planning Time':0.041,
      'Triggers':[

      ],
      'Execution Time':37.092
   }
]

The database system is posgresql, accessed in python via psycopg2. Is there any way to get the timings from the database system directly?

You can use pg_stat_statements extension to collect stats over all queries in your db. You can indirectly see to total runtime of INSERT/UPDATE OR DELETE for needed table.


PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup I am trying to dump the schema from one of my PostgreSQL 9.3+PostGIS databases:

/usr/lib/postgresql/9.3/bin/pg_dump --schema-only
                                    -h stg-db.....rds.amazonaws.com
                                    -U user_name db_name > stg_db_schema.sql

The command works well on my dev db, but fails on stg and prod due to:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation direction_lookup
pg_dump: [archiver (db)] query was: LOCK TABLE tiger.direction_lookup IN ACCESS SHARE MODE

The problem seems to be permissions for tiger.direction_lookup. I Googled a bit, and this table seems to be related to PostGIS.

I have tried --exclude-table-data=tiger.direction_lookup, with no effect (it is not supposed to work for schemas anyway).

Any idea how to be able to dump the schema of my DB?

It seems that I had to re-run the AWS RDS PostGIS instructions:

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser')
  FROM (
    SELECT nspname, relname
    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
    WHERE nspname in ('tiger','topology') AND
    relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;

This is clearly an RDS Bug, because these instructions should not be executed more than once.

Perhaps this happened due to a database minor version upgrade.


script to find all bigint that can be converted to int fields in postgresql performance I want to compact a postgresql 11 db and my idea is to convert lots of bigint to int data types if and only if there is a reasonable lag to the limit of int data type considering the data inside.

So, can you help me in finding a script to find all bigint that can be converted to int fields in postgresql ?

Following script should help to do the job:

create or replace procedure cbi ()
language plpgsql
as $$
--
declare
s text;
max_value bigint;
c cursor for
 select
  pg_namespace.nspname,
  pg_class.relname,
  pg_attribute.attname,
  pg_type.typname
 from pg_class
 join pg_namespace
 on pg_class.relnamespace = pg_namespace.oid
 and pg_namespace.nspname <> 'pg_catalog'
 join pg_attribute
 on pg_class.oid = pg_attribute.attrelid
 join pg_type
 on pg_attribute.atttypid = pg_type.oid
 and pg_type.typname='int8'
 where pg_class.relkind in ('r','v');
begin
 for r in c
 loop
  s := 'select  max(' || r.attname || ') from ' || r.nspname || '.' || r.relname;
  execute s into max_value;
  if max_value <= +2147483647 and max_value >= -2147483648
  then
   raise notice '%.%.% max=% can be replaced with int.', r.nspname, r.relname, r.attname, max_value;
  else
   raise notice '%.%.% max=% CANNOT be replaced with int.', r.nspname, r.relname, r.attname, max_value;
  end if;
 end loop;
end;
--
$$;

Example:

postgres=# call cbi();
NOTICE:  public.pg_stat_statements.queryid max=9148650842799727408 CANNOT be replaced with int.
NOTICE:  public.pg_stat_statements.calls max=118 can be replaced with int.
NOTICE:  public.pg_stat_statements.rows max=4060311 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_hit max=577699 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_read max=637 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_dirtied max=991 can be replaced with int.
NOTICE:  public.pg_stat_statements.shared_blks_written max=991 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_hit max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_read max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_dirtied max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.local_blks_written max=0 can be replaced with int.
NOTICE:  public.pg_stat_statements.temp_blks_read max=171 can be replaced with int.
NOTICE:  public.pg_stat_statements.temp_blks_written max=171 can be replaced with int.
NOTICE:  public.t.x max=1 can be replaced with int.
CALL
postgres=#


What's the owner of SqlServerAgent job for? What is the possible use of owner of an SQL Server Agent job, I wonder?

Removed, and left it blank and the job still succeeds

The job owner determines the execution context of the job's steps.

For a T-SQL job step: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified database user (set on the Advanced page of the Job Step Properties dialog). If the job owner is not a sysadmin then the step will execute as the job owner.

For other job steps: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified Proxy. If the job owner is not a sysadmin then the step will execute as a specified Proxy (which requires the job owner to have permission to use the proxy).

A SQL Agent Proxy is created based on a SQL Server Credential which stores the secret associated with some external security principal. For example, sysadmin Kim creates a Credential for ADVENTUREWORKS\Bob and then creates a Proxy based on that Credential. She makes the Proxy active for the CmdExec subsystem. She gives (nonsysadmin) Qin the rights to use that Proxy. Qin can now create a CmdExec job step that executes as ADVERNTUREWORKS\Bob.

See Implement SQL Server Agent Security in Books Online.


Best way to store a value which could be multiple types I'd like to re-ask a question in a more direct and general way:

How do you create a table to store values which could be multiple different types?

In my case, the values provide diagnostics about an event. Ex: Event occurred -> Store readings from multiple PLC's which contain pertinent information about the event. The PLC's could monitor any type of data.

Some examples I can think of:

Create a column for every possible type and create another column to indicate which column to use

Ex: Cols: IntVal, StrVal, BoolVal, Type. Vals: null, null, True, "BOOL"

Store the values no matter what as a varchar

It would appear that you have already been told that you are straying down the road to the EAV model. Take a look at the image here for why the EAV model should be avoided at virtually all costs.

Bill Karwin, the author responsible for the image above, has written a book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" and he devotes the first chapter to the EAV anti-pattern. He's also a big hitter on this group and huge on StackOverflow (for database issues).

My advice would be to have a table for each type of result and then to use VIEWs to combine them when necessary.

As an example, you could have

CREATE TABLE char_result
(
  question_id INT,
  user_id INT,
  cresult CHAR,
  result_correct BOOLEAN (or equivalent in your RDBMS)
  ..
  <other stuff>
  ..
);

Do the same for num_result, except substitute nresult INT (FLOAT... whatever) for cresult - same idea for VARCHAR &c.

Then create VIEWs over your different result tables for result_correct (and other fields - number_of_attempts... &c. - whatever your other fields are). In this case, you are comparing like with like and not peforming calculations equivalent to adding population to altitude as per the hilarious image referenced above!


Grouping rows in ranges Let's say I have the following (contrived) table of monitoring data:

RowID       DateStamp            Success         Error
-----       -----------------    -------         -----------
1001        5/24/2019 11:23am    1               None
1004        5/24/2019 11:24am    1               None
1005        5/24/2019 11:25am    1               None
1009        5/24/2019 11:26am    0               SQL Timeout
1018        5/24/2019 11:27am    0               SQL Timeout
1019        5/24/2019 11:28am    1               None
1026        5/24/2019 11:29am    1               None
1035        5/24/2019 11:30am    0               Planned Maintenance
1100        5/24/2019 11:31am    0               Planned Maintenance
1111        5/24/2019 11:32am    1               None

I'd like to group rows with the same status and error, but only if they fall next to each other in sequence by date or by ID (ID will be ordered correctly, but is not guaranteed to be sequential), as follows:

Starting             Ending               Polls  Success   Error
-----------------    -----------------    -----  -------   -----------
5/24/2019 11:23am    5/24/2019 11:25am    3      1         None
5/24/2019 11:26am    5/24/2019 11:27am    2      0         SQL Timeout
5/24/2019 11:28am    5/24/2019 11:29am    2      1         None
5/24/2019 11:30am    5/24/2019 11:31am    2      0         Planned Maintenance
5/24/2019 11:32am    5/24/2019 11:32am    1      1         None

A simple GROUP BY doesn't work:

SELECT MIN(DateStamp) as Starting, MAX(DateStamp) as Ending,
      Count(*) as Polls, Success, Error
FROM myTable
GROUP BY Success, Error
ORDER BY Starting

Since that combines all rows with the same status/error, regardless of when they occur:

Starting             Ending               Polls  Success   Error
-----------------    -----------------    -----  -------   -----------
5/24/2019 11:23am    5/24/2019 11:32am    6      1         None
5/24/2019 11:26am    5/24/2019 11:27am    2      0         SQL Timeout
5/24/2019 11:30am    5/24/2019 11:31am    2      0         Planned Maintenance

Is there an easy way to do this in SQL Server 2012?

An alternative way to identify the islands is using the LAG analytic function and the SUM() window aggregate function:

WITH
  sentinels AS
  (
    SELECT
      DateStamp,
      Success,
      Error,
      Grp = CASE
              WHEN Success = LAG(Success) OVER (ORDER BY DateStamp ASC)
               AND Error   = LAG(Error  ) OVER (ORDER BY DateStamp ASC)
              THEN 0
              ELSE 1
            END
    FROM
      dbo.myTable
  ),
  fullyMarked AS
  (
    SELECT
      DateStamp,
      Success,
      Error,
      Grp = SUM(Grp) OVER (ORDER BY DateStamp ASC ROWS UNBOUNDED PRECEDING)
    FROM
      sentinels
  )
SELECT
  Starting  = MIN(DateStamp),
  Ending    = MAX(DateStamp),
  Polls     = COUNT(*),
  Success,
  Error
FROM
  fullyMarked
GROUP BY
  Success,
  Error,
  Grp
ORDER BY
  Starting ASC
;

The sentinels CTE marks the first row of each island with a 1 and the others with a 0:

RowID  DateStamp          Success  Error                Grp
-----  -----------------  -------  -------------------  ---
1001   5/24/2019 11:23am  1        None                 1
1004   5/24/2019 11:24am  1        None                 0
1005   5/24/2019 11:25am  1        None                 0
1009   5/24/2019 11:26am  0        SQL Timeout          1
1018   5/24/2019 11:27am  0        SQL Timeout          0
1019   5/24/2019 11:28am  1        None                 1
1026   5/24/2019 11:29am  1        None                 0
1035   5/24/2019 11:30am  0        Planned Maintenance  1
1100   5/24/2019 11:31am  0        Planned Maintenance  0
1111   5/24/2019 11:32am  1        None                 1

The fullyMarked CTE takes the output of sentinels and calculates the running total of the Grp column, thus effectively assigning a unique ID to each island:

RowID  DateStamp          Success  Error                Grp
-----  -----------------  -------  -------------------  ---
1001   5/24/2019 11:23am  1        None                 1    -- 1
1004   5/24/2019 11:24am  1        None                 1    -- 1+0
1005   5/24/2019 11:25am  1        None                 1    -- 1+0+0
1009   5/24/2019 11:26am  0        SQL Timeout          2    -- 1+0+0+1
1018   5/24/2019 11:27am  0        SQL Timeout          2    -- 1+0+0+1+0
1019   5/24/2019 11:28am  1        None                 3    -- 1+0+0+1+0+1
1026   5/24/2019 11:29am  1        None                 3    -- 1+0+0+1+0+1+0
1035   5/24/2019 11:30am  0        Planned Maintenance  4    -- 1+0+0+1+0+1+0+1
1100   5/24/2019 11:31am  0        Planned Maintenance  4    -- 1+0+0+1+0+1+0+1+0
1111   5/24/2019 11:32am  1        None                 5    -- 1+0+0+1+0+1+0+1+0+1

The final step is to just group by Success, Error, Grp and aggregate data of other columns as required, which gives you the expected result.

You can use this live demo at db<>fiddle.uk for playing with this solution.


Lock All Column Changes, Except One Column On Trigger I want to ask how to lock all updates except one column with Trigger, is it possible to achieve this?
For example my table structure is:
--------------------------
id | name | age | point
--------------------------
1  | Test | 10  | 10
2  | Test | 12  | 10

So what I want is to lock all changes update columns, except the point column.
Thank you in advance, sorry for my bad english

The best thing is to use permissions. If datamoduser is the database user that performs the data modifications, make sure the user has no permissions except
GRANT UPDATE (point) ON mytable TO datamoduser;

Then all UPDATE statements except the ones that update point will fail.


PostgreSQL failed on Select I am trying to use a result from a select for another select:

DECLARE
_port int;
BEGIN
select alter_port into _port from database where ....;
select data inot data_list from database where PortNumber = alter_port;

The second select always fails, the first select returns the data I want.

But, when I force assign value

alter_port := 2602;
select data from database where PortNumber = alter_port;

Then I get the data I need.

Why has this failed? I know the table has the data I want.
How do I check Error code for select statement?

Below are the table and function definitions:

CREATE TABLE AltRoute (
    GUID uuid PRIMARY KEY,
    PortNumber int NULL,
    ControllerNumber int NULL ,
    AltRtePortNumber int NULL ,
    AltRtePortName varchar (256) NULL ,
    AltRtePortList varchar(300) NOT NULL DEFAULT '0',
    HighAltPort int NULL DEFAULT 0,
    MediumAltPort int NULL  DEFAULT 0,
    LowAltPort int NULL  DEFAULT 0,
    HighEnable int NULL CONSTRAINT DF_AltRoute_HighEnable DEFAULT (0),
    MediumEnable int NULL CONSTRAINT DF_AltRoute_MediumEnable DEFAULT (0),
    LowEnable int NULL CONSTRAINT DF_AltRoute_LowEnable DEFAULT (0),
    HighPortList varchar (405) NULL CONSTRAINT DF_AltRoute_HighPortList DEFAULT (0),
    MediumPortList varchar (405) NULL CONSTRAINT DF_AltRoute_MediumPortList DEFAULT (0),
    LowPortList varchar (405) NULL CONSTRAINT DF_AltRoute_LowPortList DEFAULT (0),
    DynamicQuery varchar (4000) NOT NULL DEFAULT 0
);

CREATE FUNCTION sp_ComposeHighPortList (_startPort int) RETURNS integer AS $$
DECLARE
_altRtePort INT = 0;
_altRtePortList VARCHAR (405) = '0';
_port INT = 0;
BEGIN

  _altRtePort := 0;
  _altRtePortList := '1';
  _port := 0;

  SELECT HighAltPort into _altRtePort
    FROM AltRoute
    WHERE PortNumber = _startPort and HighEnable = 1;

  IF (NOT FOUND) THEN
    RAISE  NOTICE 'sp_ComposeHighPortList - FAILED: SELECT  _altRtePort = HighAltPort FROM AltRoute WHERE PortNumber = _startPort and HighEnable = 1, _startPort=%', _startPort;
    RETURN 0;
  END IF;

  IF (_altRtePort = 0 or _altRtePort = _startPort) THEN
    _altRtePortList := '';
    _altRtePort := 0;
    _startPort := _startPort+1;
    RETURN 0;
  END IF;

  SELECT RTRIM(HighPortList) into _altRtePortList
  FROM AltRoute
  WHERE PortNumber = _altRtePort;

  IF (NOT FOUND) THEN
    RAISE NOTICE 'sp_ComposeHighPortList - FAILED: SELECT  _altRtePortList = RTRIM(HighPortList) FROM AltRoute WHERE PortNumber = _altRtePort : %', _altRtePort;
  END IF;
...
...
..
END;
$$ LANGUAGE plpgSQL;

I think it should be;

    DECLARE
      _port int;
    BEGIN
      select alter_port into _port from a_table where ....;
      select data into data_list from a_table where PortNumber = _port;


I inadvisable to use Datetime type for your answer_time, change it to TIME type because the 000-00-00 will fail with all datetime functiosn (look at the MySQL documentation : link).

So, when your table looks like :

[09:47:55] [DEV\MFO] mysql> desc MFO.phone_call;
+-------------+------------+------+-----+---------+-------+
| Field       | Type       | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+-------+
| phone       | bigint(20) | YES  |     | NULL    |       |
| answer_time | datetime   | YES  |     | NULL    |       |
+-------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

You could try that :

SELECT phone,
   Count(*),
   Count(CASE
           WHEN Time(answer_time) = 0 THEN 1
         end) AS NOT_ANSWERED,
   Count(CASE
           WHEN Time(answer_time) > 0 THEN 1
         end) AS ANSWERED
FROM   MFO.phone_call
GROUP  BY phone;

+-------+----------+--------------+----------+
| phone | count(*) | NOT_ANSWERED | ANSWERED |
+-------+----------+--------------+----------+
| 11111 |       21 |           10 |       11 |
| 22222 |        2 |            0 |        2 |
+-------+----------+--------------+----------+
2 rows in set (0.00 sec)

Max.


Time Difference Between actual total time and execution time in EXPLAIN ANALYZE I have the following output of EXPLAIN ANALYZE query on PostgreSQL 11.5

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT actor.actor_id, actor.first_name, act.num
FROM actor
  INNER JOIN (
    SELECT  actor_id, COUNT(DISTINCT film_category.category_id) as num
    FROM film_actor
      INNER JOIN film_category ON film_actor.film_id = film_category.film_id
    GROUP BY actor_id
  ) act ON act.actor_id = actor.actor_id
ORDER BY actor.actor_id ASC;

Merge Join  (cost=527.43..591.41 rows=200 width=18) (actual time=320.130..324.861 rows=200 loops=1)
  Output: actor.actor_id, actor.first_name, (count(DISTINCT film_category.category_id))
  Inner Unique: true
  Merge Cond: (actor.actor_id = film_actor.actor_id)
  Buffers: shared hit=9 read=35
  ->  Index Scan using actor_pkey on public.actor  (cost=0.14..16.16 rows=200 width=10) (actual time=77.146..77.272 rows=200 loops=1)
        Output: actor.actor_id, actor.first_name, actor.last_name, actor.last_update
        Buffers: shared hit=2 read=3
  ->  GroupAggregate  (cost=527.28..570.25 rows=200 width=10) (actual time=242.973..247.346 rows=200 loops=1)
        Output: film_actor.actor_id, count(DISTINCT film_category.category_id)
        Group Key: film_actor.actor_id
        Buffers: shared hit=7 read=32
        ->  Sort  (cost=527.28..540.94 rows=5462 width=4) (actual time=242.932..243.781 rows=5462 loops=1)
              Output: film_actor.actor_id, film_category.category_id
              Sort Key: film_actor.actor_id
              Sort Method: quicksort  Memory: 449kB
              Buffers: shared hit=7 read=32
              ->  Hash Join  (cost=28.50..188.22 rows=5462 width=4) (actual time=17.034..216.640 rows=5462 loops=1)
                    Output: film_actor.actor_id, film_category.category_id
                    Hash Cond: (film_actor.film_id = film_category.film_id)
                    Buffers: shared hit=4 read=32
                    ->  Seq Scan on public.film_actor  (cost=0.00..84.62 rows=5462 width=4) (actual time=0.019..195.884 rows=5462 loops=1)
                          Output: film_actor.actor_id, film_actor.film_id, film_actor.last_update
                          Buffers: shared hit=2 read=28
                    ->  Hash  (cost=16.00..16.00 rows=1000 width=4) (actual time=16.964..16.965 rows=1000 loops=1)
                          Output: film_category.category_id, film_category.film_id
                          Buckets: 1024  Batches: 1  Memory Usage: 44kB
                          Buffers: shared hit=2 read=4
                          ->  Seq Scan on public.film_category  (cost=0.00..16.00 rows=1000 width=4) (actual time=0.017..16.431 rows=1000 loops=1)
                                Output: film_category.category_id, film_category.film_id
                                Buffers: shared hit=2 read=4
Planning Time: 966.447 ms
Execution Time: 436.348 ms

On the first line, the actual total time is 324.861 ms but on the last line the execution time is 436.348 ms. What are the causes of this differences?

The source of EXPLAIN has:

    /* Create textual dump of plan tree */
    ExplainPrintPlan(es, queryDesc);

    if (es->summary && planduration)
    {
            double          plantime = INSTR_TIME_GET_DOUBLE(*planduration);

            ExplainPropertyFloat("Planning Time", "ms", 1000.0 * plantime, 3, es);
    }

    ...

    /*
     * Close down the query and free resources.  Include time for this in the
     * total execution time (although it should be pretty minimal).
     */
    INSTR_TIME_SET_CURRENT(starttime);

    ExecutorEnd(queryDesc);

    FreeQueryDesc(queryDesc);

    PopActiveSnapshot();

    /* We need a CCI just in case query expanded to multiple plans */
    if (es->analyze)
            CommandCounterIncrement();

    totaltime += elapsed_time(&starttime);

    /*
     * We only report execution time if we actually ran the query (that is,
     * the user specified ANALYZE), and if summary reporting is enabled (the
     * user can set SUMMARY OFF to not have the timing information included in
     * the output).  By default, ANALYZE sets SUMMARY to true.
     */
    if (es->summary && es->analyze)
            ExplainPropertyFloat("Execution Time", "ms", 1000.0 * totaltime, 3,
                                                     es);

Perhaps freeing of the query resources (tearing down the executor infrastructure, freeing the snapshot) takes an inordinate time. Hard to say.

Is that a one-time occurrence or do you see that result consistently?

If the latter, is there anything unusual about this query?


Rolling back truncate table I have a stored procedure that does the following:

BEGIN TRANSACTION

    -- Code to delete updated records from production (dbo) table
    DELETE FROM [dbo].[factMyTable]
    WHERE exists (SELECT *
        FROM [RAW].[MyTable]
        WHERE [RAW].[MyTable].[refno] = [dbo].[factMyTable].[refno]
        AND [RAW].[MyTable].[modification_dttm] >= [dbo].[factMyTable].[modification_dttm]
        )

    -- Code to perform the append of incremental records
    INSERT INTO [dbo].[factMyTable]
    SELECT
         [refno]
        ,[field1]
        ,[field2]
        ,[field3]
        ,[FieldN]
        ,[modification_dttm]
    FROM [RAW].[MyTable]

-- Truncate stage table and get ready for next load
TRUNCATE TABLE [RAW].[MyTable]

COMMIT TRANSACTION

As you can see above, I have a truncate command that is contained within a BEGIN/COMMIT transaction block. However I got an error when executing this stored procedure at the insert command, where a field set as NOT NULL was receiving a NULL value. As a result:

The insertion of records from the RAW table into the dbo table got rolled back; BUT
The truncation of the RAW table did not roll back.

The idea is that if there is an error with the insertion of data, the truncation shouldn't happen.

According to this article, we can roll back the truncate command, but perhaps my stored procedure is not scripted correctly. Perhaps there is a more direct way of ensuring that the truncation only happens if the insertion returns no errors? How would I go about it?

The insertion of records from the RAW table into the dbo table got
  rolled back; BUT
  The truncation of the RAW table did not roll back.

No, there was no rollback at all, and here is the repro.

With xact_abort off that is your default set option I created 2 tables, I then open transaction end do 2 inserts one of which(the second one) fails, I added select @@trancount and select from both tables so you can better see what happens:

--set xact_abort on

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go

create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;

select @@trancount as [@@trancount after commit];

select *
from dbo.t1;

select *
from dbo.t2;

As you see, no rollback was made, only your commit. You insert (-1) into dbo.t2 and this row is permanently there. This is because the error

  Msg 515, Level 16, State 2, Line 18 Cannot insert the value NULL into
  column 'col1', table 'dbo.t2'; column does not allow nulls.

is statement terminating only. The second statement fails so no row were inserted, but insert of (-1) was not rollbacked, and as you see after the error your transaction is still open. It's your commit that commits insert of -1 and table truncation.

Now the second test: uncomment set xact_abort on, this will make statment terminating only error be batch aborting, all the statements within transaction will be rolled back and execution will be interrupted as soon as the error occurs.

So t1 table will never be truncated and the insert of (-1) in t2 will be rolled back.

And now how your code should be written:

set xact_abort on;

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go

create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin try
begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;
end try

begin catch
    select @@trancount as [@@trancount in catch before rollback];
    if @@rowcount > 0 rollback;
    throw;
end catch;

Your code should always set xact_abort on and it should have try..catch block.

You should do rollback from catch ad throw the error


What are the real life cases for using Oracle advanced queuing? What are the cases when Oracle advanced queuing is the preferred mechanism for implementing functional requirements? For example, money transfer from bank account A to bank account B might theoretically be considered as two different operations, and might be implemented separately - first, enqueue money transfer from account A (update), then enqueue money trasfer to account B (update). However, it's obvious it can't be done like that because those two operation should be done in one consistent operation - in transaction.

Maybe advanced queuing should be only considered when developing stored procedures/functions that perform some logic that is both done internally (by doing DML operations and calling other local stored proc/fnc) and externally (by calling some webservices). When using such calls to webservices we can't wrap it all into consistent transaction, so the only way is to use some queuing mechanism...

Any real life detailed examples would be appreciated.

What I'm curious about is not the details of the technology itself, but rather what are the real life cases of using such messaging because I haven't done that before. Like, why do I need to pass some data (message payload) around?

I am not as familiar with Oracle as I am with Postgres.  Nonetheless, I will tell you where I see queuing approaches like this as ideal (as the author of pg_message_queue):  allowing database transactions to have non-transactional side-effects with minimal complexity cost.  A simple example is "when we commit a transaction saying we have shipped a part, let's send an email out notifying the customer."

You could do this without Oracle AQ, but Oracle AQ will likely make this simpler.  The key factor is that this allows you to enqueue the message such that it is visible on commit, and have the message which will be the basis of an email once the database transaction commits and not before.  If you try to send the message from inside the transaction, you get a number of nasty failure cases:

If the message fails to send should we abort the transaction?
If the message sends and the transaction later is rolled back, how do we un-send the email?  We can't.

Another example may be loosely coupled applications, integrated over a message queue.  Each application can largely run unaware of the other, but when transactions are committed, messages are sent to the other.  If they can't be delivered immediately, no problem.  We will process them when we can.

So the basic thing is that there are many cases where you want to centralize logic around the database where you can't really do everything properly in a single transaction.  Being able to send messages to other components on db commit is really helpful.

And sure you could build everything yourself without AQ.  But this has it already built for you.

Edit: I have re-read the Oracle docs here and they are hopelessly confusing, so I don't blame you for your confusion here.  If I wasn't already somewhat familiar with the sorts of things they were talking about, I am not sure I would have been able to follow them.  I am now 100% sure that my answer is on-target though.


Intellisense in SSMS 2012 not working for 2008 server I did most of the solutions provided here.

I know about

enabling IntelliSense
clearing the cache
connecting to a server >= SQL Server 2008
using the latest SQL Server Service Pack

and it works locally with the SQL Server 2012 engine.

But it still does not work when connecting to a SQL Server 2008 SP1 engine from my SSMS 2012. If I do

SELECT SERVERPROPERTY('ProductVersion')

I get 10.0.2573.0 as result. So the server is 2008 SP1.

Edit

After a few days it started working. I don't know why.

Try patching your server. I attempted to recreate your issue with SSMS 2012 (11.0.3000) by  connecting to four servers: one SQL Server 2008 (10.0.1600) and three SQL Server 2008 SP 1 (10.0.2531). As you can see the only difference between the four servers is one server does not have Service Pack 1  installed. On all of the servers with service pack 1 installed intellisence worked without issue. However, intellisence did not work on the server without service pack 1 installed.
You can download the service pack here.
http://www.microsoft.com/en-us/download/details.aspx?id=20302


Issue moving database between servers (cannot find .frm file even though it's there) I used MAMP on my local machine to make a little site with a mysql database. I moved all the site files over the to server. Great.

The name of my database is roster and it has one table called users. I copied the 'roster' folder which contains the following files:

db.opt
users.frm
users.MYD
users.MYI

I put the 'roster' folder in the mysql directory. I run the following:

<?php
$db = mysql_connect("localhost","username","password") or die ('Fail message1');
mysql_select_db("roster") or die("Fail message2");
?>

No error. Awesome. But when I run the following:

$query = "SELECT last_name, first_name, u_name, skype_id, primary_location FROM users";

$result = mysql_query($query)or die('query error'.mysql_error());

Then I receive the following error:

query errorCan't find file: './roster/users.frm' (errno: 13)

Furthermore, when I login to the server and login to mysql. I see the database and table:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| phones_db          |
| roster             |
+--------------------+
4 rows in set (0.00 sec)

mysql> use roster;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+------------------+
| Tables_in_roster |
+------------------+
| users            |
+------------------+
1 row in set (0.00 sec)

mysql> select * from users;
ERROR 1017 (HY000): Can't find file: './roster/users.frm' (errno: 13)
mysql>

I checked the 'roster' folder and it contains the four files mentioned at the beginning of this post. The file is where it supposed to be, right? Did I move the database wrong? What gives? Any ideas?

PROBLEM
You have a file permission issue.
The INFORMATION_SCHEMA database will not notice when doing just show tables; because it will scan for the .frm's existence. It's when the storage engine attempts to access the physical files (.MYD and .MYI') that mysqld will complain. All physical files of any table must have mysqlas its owner. When the error saysCan't find file: './roster/users.frm' (errno: 13)`, it really means it cannot find the table's physical files with proper file permissions.
SOLUTION
Change the owner of the roster folder and its contents to mysql.
GIVE IT A TRY !!!


Decompose a relation to 2NF and preserve all the functional dependencies For example, consider the following relation:

R(A, B, C, D, E)

With the functional dependencies (FDs) shown below:

{A→B,  BC→E,  D→C}

Here candidate key is {AD} and the relation is not in second normal form (2NF) because of the partial dependencies A→B,  D→C.

Now, if I decompose R as follows:

R1(AB) ,  R2(CD),  R3(ADE)

...these relations are now in 2NF, but the FD BC→E is lost.

How can I decompose R to comply with 2NF and preserve the FDs?

One decomposition in 3NF (and so also in 2NF) is:

R1(AB)
R2(BCE)
R3(CD)
R4(AD)

This decomposition can be obtained with the so-called synthesis algorithm for 3NF, it is a lossless decomposition and preserves the Functional Dependencies.


Mongo cannot find my collection My mongodb server has a database named villageContents

It has a collection named tablebusiness

If I run mongo I saw

MongoDB shell version: 2.0.7
connecting to: test
>

I wonder what "test" is. There is no database named test there.

I tried to execute

> villageContents.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})
Wed Aug 15 09:28:28 ReferenceError:is not defined (shell):1
>

I tried to execute

> test.villageContents.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})
Wed Aug 15 09:29:13 ReferenceError: test is not defined (shell):1
>

What did I do wrong?

Then I did

db.villageContents.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})

Nothing shows up. Indexes aren't even added.

So what's wrong?

I wonder what "test" is. There is no database named test there.

This is just the default database that the mongo shell uses when it connects, unless you insert something, it is empty.  You can do this with any database in fact, test is just the default.

The first command will not work because you did not prefix it with db, as such it attempts to find "isikota" as a variable (javascript) and fails to find it.  Similarly your next attempt does the same with a non-existent "test" variable.

Finally, this command is the right form (prefixed with db):

db.isikota.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"})

But, you did not change database (with use <database name>) or at least you did not mention trying to at least.  What this is going to do is create a 2d (geoindex) index on a "LatitudeLongitude" field on a collection named "isikota.tablebusiness" in the test database (db always refers to the database you are currently using).  Even if this collection does not exist, the index will be created (this is just an insert into the system.indexes namespace) and hence the command succeeds.  You just won't see anything else, because I believe you are still operating on the test database.

Assuming your data is actually in a database called "isikota" and that "tablebusiness" is your collection, what you actually want to do is this:

use isikota;
db.tablebusiness.ensureIndex({"LatitudeLongitude" : "2d"});

That will create the index on the "LatitudeLongitude" field in a collection named "tablebusiness" in a database named "isikota".


Will the foreign key constraint throw an error while restoring a database? I have never faced any issue like this, but I am just curious. Let's say I have a database dump file with me and I want to import the dump file to another database.
There are 2 tables table_A and table_B. table_B contains a primary key and table_A contains the foreign key. And serially, table_A is first and then table_B.

So, when I update the database table_A will be created with a foreign key and next table_B will be created that contains the primary key.

So, will there be any error in this case?

Depending on the type of backup, that can indeed happen. It's therefore recommended to disable foreign key checks before the restore, e.g. with

SET FOREIGN_KEY_CHECKS=0;

and re-enabling them afterwards with

SET FOREIGN_KEY_CHECKS=1;

This has another advantage: MySQL won't check the foreign key for every single imported record, but for all records at once. This will speed up your restore.

See Disabling foreign key checks on the command line on Stack Overflow.


Ways to Reduce Database Size and possibly improve performace. Untouched enviroment I am new to an environment. The databases are virtually untouched by a seasoned DBA. I am looking for ways to reduce DB size and possibly improve performance.

Unfortunately, I only have Web edition SQL Server 2008 so compression is unavailable. What are some things that I can do, before I ask for SQL and HW upgrades?

I'm hosting multiple sites, they all have the same schema structure, but may vary from customer to customer based on use. I want to reduce the size of the MDF files and the overall usage of the data drive they are on.

Zerothly, you need to test your backups and have a recovery plan if your not doing that.

Let's talk about some quick win performance first of all. Here are some options. Google can help flesh these out more.

Are their multiple named instances on the box?
Have you set max memory?
Have you set the number of files for tempdb?
If it's at all possible, put the data files and log files (usually with the extensions  MDF and LDF respectively) on different physical disks. Not just logical drive letters on the same disk drive.
Determine your index fragmentation levels.
Maintain your indexes. You have two good free options:

Ola Hallengren's solution is something I like on servers I have to maintain because it has a very verbose full manual mode for running from SSMS. It can also be automated.
Minion Reindex is going to scale better for you actual automated reindexing.

This will require reformatting the drive with the SQL Server databases, but setting your disk partitions correctly is important.
Enable Instant File Initialization.
Set your file growth options sensibly.

Space-wise:

If you delete data or indexes from the database, the MDFs don't shrink. You have to shrink them. Shrinking is generally bad, but on rare ocassions it makes sense.
Are you doing FULL or SIMPLE recovery mode? If Simple, do your clients not need point in time data? If your doing full recovery are you properly managing your transaction log backups?
What are you options for archiving data, dropping unused indexes and altering schema?


Performance for MySQL InnoDB B+Tree index with many duplicate values I'm trying to diagnose seemingly random performance issues with our database server. Below is a simplified scenario, hopefully generic enough to serve as a useful future reference for anyone looking for the same answer.

Suppose I have a (MySQL 5.6 w/ InnoDB) table like

CREATE TABLE Example (
    id INT NOT NULL AUTO_INCREMENT,
    secondary_id INT DEFAULT NULL,
    some_data TEXT NOT NULL,
    PRIMARY KEY (id),
    KEY (secondary_id)
) ENGINE=InnoDB;

with about 15 million rows. However, the secondary_id column is NULL for almost all rows, so the index on secondary_id has very, very low cardinality (in our case about ~30k). In our case, when we experience the performance issue I'm investigating, the process list for the server shows many (100+) queries of the form:

UPDATE Example SET secondary_id = NULL, some_data = '...' WHERE id = 123;

that are taking ~90+ seconds to complete, during which they are in the "updating" state. (These queries would be running in separate transactions.)

I'm specifically wondering if the transition from a not-null secondary_id to a null secondary_id is causing performance slowdowns in the above UPDATE. That is, is it possible that updating the index in this case takes a significant amount of time since there are so many rows (~15mil) that have the same value for that column (NULL)?

I guess this question stems from me not understanding how the B+Tree index will store row pointers for rows having duplicate index values. I'd guess that node would have a linked list (or something similar) with a pretty fast insertion time, so I'd guess the answer to my question is "No". But I'd like to confirm that with the experts, i.e. you all.

I've tried to do a fair amount of research here, but I've come up pretty empty handed. Probably the most comprehensive post is this one, which explains some different techniques for handling duplicate keys, but I'm specifically looking for InnoDB/MySQL's approach.

90 seconds for a single UPDATE sounds too, too much. There is probably some blocking involved and should be investigated.

Apart from that, having a column that has 98% the same (NULL) value does not sound good either. You should consider putting that column in a separate table (which would have only 30K rows). It would complicate your INSERT/DELETE/UPDATE procedures a bit but you would probably gain from the smaller indexes. Suggested design:

CREATE TABLE Example (
    id INT NOT NULL AUTO_INCREMENT,
    some_data TEXT NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB ;

CREATE TABLE Example_secondary (
    id INT NOT NULL,
    secondary_id INT NOT NULL,
    PRIMARY KEY (id),
    INDEX (secondary_id),
    FOREIGN KEY (id)
      REFERENCES Example (id)
) ENGINE = InnoDB ;

Then your UPDATE:

UPDATE Example
SET secondary_id = NULL,
    some_data = '...'
WHERE id = 123 ;

would become:

BEGIN ;
    UPDATE Example
    SET some_data = '...'
    WHERE id = 123 ;

    DELETE FROM Example_secondary
    WHERE id = 123 ;
COMMIT ;


STRING_AGG Not Recognized on V15.0.2 of SQL Server LocalDB I recently coded a stored procedure on SQL Server 2019 (14.0.3192.2) in AWS that utilized the function STRING_AGG with no issues. When I moved the code to my local test db it complained about that method.
Upon research I proceeded to update my local sql server db service from 14.x version to the latest 15.0.2 and tried to install the sproc again; to the failure

Incorrect syntax near '"'',STRING_AGG(ActionDATE, ''","''),''"'.

This feature has been viable since SQL Server 2017...so being missing in the local version of 2019 is somewhat puzzling.
Question
What recourse do I have besides installing SQL Server 2019 Developer edition to resolve this issue?

Intention
Note that the failure line with STRING_AGG is trying to create a JSON sub array of dates such as
[ '01/24/2020', '01/28/2020' ]:
Code
CREATE PROCEDURE [hcwfba].[GetOperationsAndActionDates]
    @operationId int,
    @locationId int
AS

WITH  [DATA] AS (SELECT DISTINCT id_Operation, id_Location , CONVERT(varchar, ActionDATE ,101) AS [ActionDATE] FROM OperationDetail)

SELECT distinct
         PD.id_Operation
       , PD.id_Location
       , JSON_QUERY((SELECT CONCAT(''["'',STRING_AGG(ActionDATE, ''","''),''"]'') FROM [DATA] DT2 WHERE DT2.id_Location = PD.id_Location)) As Dates
FROM [DATA] PD
WHERE PD.id_Operation = @operationId
AND PD.id_Location = @locationId
FOR JSON AUTO;

RETURN @@ROWCOUNT

JSON Result
[
    {
        "id_Operation": 13,
        "id_Location": 8675309,
        "Dates": [
            "12/03/2018",
            "08/27/2018",
            "10/22/2018"
        ]
    }
]

Per my comments: Have you tried to use the STRING_AGG() function with a very simple test case to confirm it's an issue with the function and not your syntax in your main query?
It seems your syntax was not formatted properly, rather than the STRING_AGG() function not being available on your instance version.


Why is my index on the join keys not used in a very simple hash join case? I have two tables:

create table animal
(
    aid integer,
    cid integer,
    aname varchar(255) default NULL::character varying,
    species text
);

create table daily_feeds
(
    aid integer,
    zid integer,
    shift integer,
    menu integer
);

and a query:

SELECT
aname,
shift
FROM animal
NATURAL JOIN daily_feeds
WHERE menu = 1;

The table animal contains around 40000 row, the table daily_feeds contains around 80000 row, 2 for each animal.

I thought that adding an index on the columns used for the join animal.aid and daily_feeds.aid could result in a different execution plan and improved performances.

create index aaid on animal (aid);

create index daid on daily_feeds using btree (aid);

This doesn't happen. Why is that?

Obviously this is a very simple and academic example that I'm trying to use to learn more about database query optimization.

EDIT:

Added execution plan when no indexes are present:

Hash Join  (cost=1439.24..2488.23 rows=499 width=10) (actual time=11.701..55.260 rows=487 loops=1)
    Hash Cond: (animal.aid = daily_feeds.aid)
    ->  Seq Scan on animal  (cost=0.00..694.00 rows=40000 width=10) (actual  time=0.007..20.775 rows=40000 loops=1)
    ->  Hash  (cost=1433.00..1433.00 rows=499 width=8) (actual time=11.590..11.590 rows=487 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 28kB
        ->  Seq Scan on daily_feeds  (cost=0.00..1433.00 rows=499 width=8) (actual time=0.011..11.218 rows=487 loops=1)
            Filter: (menu = 1)
            Rows Removed by Filter: 79513
Planning time: 0.124 ms
Execution time: 55.521 ms

EDIT2: another question here:

Since I use two indexes I can also make them clustered indexes, physically ordering the table by the aid attribute.

cluster animal using aaid;
cluster daily_feeds using daid;

If I do that the Execution Plan doesn't change again. Isn't a Merge Join a more effective join strategy if both tables are sorted by the key column?

I'm not really sure about the behavior of Postgres here.

Providing information on the actual query plan used would help.

Speaking in general, the DB engine would have to use the two indexes to identify rows in both tables that have matching aid values; then, look up all the actual rows in the daily_feeds table to see if menu is 1, and then look up the rows in animal to get the aname values.

If there was a large number of rows to be returned, and if those are your complete tables, then it may well have decided it would be faster to simply load the tables in the first place, and scan them to find the matching rows and do the menu check (at which point aname is already there and available).

Let me note here that I'm most familiar with the way SQL Server uses indexes. It's possible that some of the following may not directly apply to PostgreSQL. However, most of the basics seem to match up, and I'm not going to delve into specifics enough to be likely to go too far wrong.

The optimizer has three ways to find rows in daily_feeds. It can simply look through the entire table; it can use the aid index to seek to a particular value or range of values of aid, then use the index information to go to the correct pages in the table to get the rows; or, it can treat the index as if it was a table, and scan that (possibly looking up the rows in the full table, if all the data it needs isn't in the index).

There are many consideration an optimizer must make in figuring out how to get the data it needs. One factor is the amount of pages it might have to load to get the data. Loading pages form disk is one of the most time-consuming tasks for a database. So, if it can limit the number of pages it may need, then that should speed up the query.

For this query, we need to find two columns in animal (aid and aname), and three in daily_feeds (aid, menu, shift).

It has two ways to identify the rows it's interested in: rows where an aid in animal matches an aid in daily_feeds, and rows where menu is 1. I'll guess that statistics would indicate (certainly a foreign key relationship would, if it actually existed) that all or almost all rows would be returned form both tables based on the aid = aid check. That doesn't do a good job of reducing the number of rows we're looking for. However, menu = 1 knocks out a lot of rows. Since menu isn't in an index, we have to go to the table to filter it by that.

Once we've got the daily_feeds rows we want, we need to find the matching rows in animal to pick up aname. We can expect to be looking for around 500 rows, and must assume (worst case) they're evenly distributed through the animal data. I'm make another assumption here; that (even with a text column) the rows in the animal table aren't terribly wide. If the basic table is stored in 500 pages (ignoring the text data; it may be stored off-row, but we don't need it for this query), then the odds are that all the pages will have to be loaded to get the rows we need.

Now, there are two paths to proceed:

Use the aaid index on animal to find the 500 aid values we're looking for; then, using the index, go to the pages where the data lives and find them in each page.
Load the animal table directly, and scan it for the 500 aid values we're looking for directly.

The optimizer decided to use option 2. Looking at it from this perspective, that doesn't necessarily seem that odd.

Note: the engine may be deciding this entire plan before it takes its first step. This can lead to bad plans (if, for instance, it turned out that menu = 1 only brought back 5 rows, then using the index might be faster) on occasion. SQL Server can maintain statistics on columns that help it determine cardinality (how well a known value for column X will limit the target rows), to help it make these decisions. PostgreSQL has some sort of statistic facility as well - not sure if it's directly comparable or not.


Changing database compatibility from legacy CE to 120 Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)

From DEV point of view all the testing has been done and they dont see much diff and want to move to prod based on their testing.

In our testing ,For certain process where we see slowness like in SP's we found the part of statement that was slow and added query traceon hint , something like below keeping compat to 120, which helps keeping performance stable

SELECT  [AddressID],
    [AddressLine1],
    [AddressLine2]
FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND
    [City] = 'Burbank'
OPTION (QUERYTRACEON 9481);
GO

UPDATE- Editing question based on more findings-

Actually we found things getting worst for a table which calls scalar function within a computed column-

below is how that column looks

CATCH_WAY AS ([dbo].[fn_functionf1]([Col1])) PERSISTED NOT NULL

and part of query where it goes weird is somewhat looking like below

DELETE t2
   OUTPUT del.col1
          del.col2
          del.col3
   INTo #temp1
FROM #temp2 t2
INNER JOIN dbo.table1 tb1 on tb1.CATCH_WAY = ([dbo].[fn_functionf1](t2.[Col1])
AND t2.[col2] = tb1.[col2]
AND t3.[col3] = tb1.[col3]
AND ISNULL (t2.[col4],'') = ISNULL (tb1.[col4],'')

I know function is being called and is slow but the problem is with current compat i.e. 100 runs OK'ish slow but when changed to 120 it gets X100 times slow and if kept at 100 its X100 faster.
What is happening ?

This regression in performance usually happens because you already had a bad query that was considered "fast enough" under the old compat, but is super slow under the new compat, due to the weird/bad way it was written.

I'd get rid of the 2 functions in your DELETE statement's ON clause, by pre-computing them and storing them into your temp tables before the DELETE happens.

You could also try to rewrite the ISNULL parts, since ISNULL is also a function.

--new temp table to store table1
SELECT col2
    ,col3
    ,col4
    ,CATCH_WAY --this scalar function gets calculated here and saved into the temp table
INTO #tempTable1
FROM dbo.table1;

DELETE t2
   OUTPUT del.col1
          del.col2
          del.col3
   INTO #temp1
FROM #temp2 t2
JOIN #tempTable1 tb1
ON tb1.CATCH_WAY = ([dbo].[fn_functionf1](t2.[Col1]) --not a good idea
AND t2.[col2] = tb1.[col2]
AND t3.[col3] = tb1.[col3]
AND ISNULL (t2.[col4],'') = ISNULL (tb1.[col4],'') --not a good idea, the ISNULL "hides" the columns from the SQL Optimiser


Add missing column to SSIS, data not extracted to staging table I've inherited an environment that extracts data from a Dynamics AX 2012 ERP to a data warehouse for reporting. The original extraction set up by consultants did not include the proper date column needed by accounting. I have performed the following:

Created the column in the staging tables and destination tables
opened the packages in Visual Studio and found that each package file uses the same variable User::vSQLBase and contains the query that pulls all columns from the table needed in Dynamics AX. I altered that variable to include the date column.
I saved my changes and deployed the entire solution back to the data warehouse.
Ran the job in SQL Server, but none of the columns populated.

I did have an error appear in the debug of Visual Studio when I test executed the package.

  Warning: 0x800470C8 at Incremental Load, Read Delta [26]: The external
  columns for Read Delta are out of synchronization with the data source
  columns. The column "Document Date" needs to be added to the external
  columns.

I did contact the consultant who replied with "the new column is not mapped to the staging table in the package, be aware that both the full load and incremental load need the same mapping. and a full load is mandatory since this is a new column". I am further unable to contact the consultant due to budget limitations. What am I missing to get the column to the staging table?

In addition to adding the new column to the variable User::vSQLBase, you need to open the Data Flow for the staging table being loaded. Edit the source, and click on Columns, verify you see the new column, click OK. Then edit the Destination, and click on Mappings - and make sure that the new column from the source is pointing to the right column in the staging table. That will do the new column mapping the consultant was talking about. You'll need to make sure the mapping is updated for whatever moves data from staging to the destination table too. Then save and deploy again.

An incremental load only populates new or changed data since the last successful run. Do you care about the new column being populated in the destination only going forward? Or is it important for that data to exist in records previously imported as well? For an accounting system, I'd guess you'd want the new column populated for all the records in the table. In that case, I agree with your consultant that a full load is necessary.


Well,
Apologies to all those SQL guru's - this might be a simplistic answer.

Since the product table will only have one record per product you don't need the ID column and can just use the 'sku' as the key.

Insert into that table a distinct list of the products
 e.g

INSERT INTO products (sku,ProductName)
SELECT DISTINCT (sku,ProductName)
FROM OldProductTable

That's your product table sorted.

How can you tell from your original data which price is the latest price? You have potentially two prices it could be. Lets presume the latest ID is the latest price.

Now you need to create a price table (as you have done) and create your foreign keys on it. You need some way of telling which is the current price though. One way is to give each record an iteration column, so the highest value in it is the latest price. Before you scream - this is a bit rubbish but is the only way I can see it fitting your data.

INSERT INTO ProductPrices (sku, PriceOld, PriceNew, iteration)
SELECT sku, PriceOld, PriceNew, ID
FROM OldProductTable

The primary key is sku and iteration. You want the latest price you just select the product and return the record with the highest iteration.

A better way to do it would be to have the concept of recording the price that was valid  date range:

Product ID,Price,StartDate,EndDate

Live prices will not have an end date (null). When you want to change a price you simply update the end date and add a new record with the start date being the date the new price comes in to effect.

That's just me though! Be happy to see other peoples views.


OLTP to OLAP process causing deadlocks and impacting customers I should preface this with the fact that I'm a .net developer who has fallen into DBA and performance work because it has become business critical, so I'm missing the DBA skills for this kind of work. Please assume that some of the basic tenets of OLAP+OLTP are things I am unfamiliar with.

In my organization there is a concept of "Consolidating" the OLTP data to a series of tables prefixed by conso which are essentially denormalized structures (the OLAP, I think). This occurs overnight and the jobs which perform the work are extremely slow, on the order of several hours. They also do not scale well as we add more customers the processing time has shot up.

There is also parameters to these consolidation jobs that cause them to run in a smaller context, and this is triggered when a customer updates the data for a particular metric. That is generally pretty fast.

Unfortunately, the night time consolidation jobs are causing deadlocks for some customers and other issues that make the process extremely problematic. We might wake up to alarm bells because the consolidation worked for 1/2 of the customers and not the rest.

What can be done to avoid this issue? Is there a best practice for running OLTP=>OLAP transactions? Determining the source of the deadlocks are outside of my experience and I did not write the consolidation jobs. I would leverage the person who wrote the original jobs, but they are not competent or this wouldn't be happening in the first place (I assume).

Perhaps this problem has been solved meanwhile. Databases need dedicated knowledge and skills, any 10 year old kid can play around with SQL Server Management Studio, but creating and maintaining databases is not that easy.

For SQL Server , make sure that Snapshot Isolation level is enabled. It's disabled by default, with the database in data locking mode, where long running queries block write acces, or even certain read accesses. I think Snapshot Isolation was introduced in SQL Server for analysis/OLAP.

Also, the OLTP tables should have proper indices. There should be somebody in the company knowing at least the basics of all this.


How to reinstall a legacy DB2 Server 7.x for Linux? We have to move our legacy database dump to a new Linux installation. I know DB2 7.x is out of support for over a decade, but is there a way to obtain a DB2 7.x Server installation media for Linux? Barring that, what other options do we have?

DB2 certification guides, like this one on Amazon, often contain CDs with trial versions of the DB2 server. If you can get hold of one, install it on a fittingly ancient operating system, and roll back the clock to before the trial expiration threshold, you might have a shot at restoring your old backup.


SQL Server 2017 installation unauthorized error I'm getting the below errors while installing SQL Server on a Windows machine.
I'm using my admin account which has permissions (I’m in Administrations group. Verified here -> Computer management -> Groups)
Does it ring any bells based on the below errors to anyone? Are there any known workarounds. Spent a day without any good solutions. This is a fresh installation btw. The worst part is I can't even uninstall it as it complains about the same unauthorized error.

I'm using my admin account which has permissions (I’m in Administrations group ...

Let's get the obvious one out of the way ...
Did you run the installer program "as Administrator" ?
Just being in the "Administrators" Group stopped being enough to "break" your machine somewhere around Windows Vista!
To get into an "elevated" security context, wherein you can make changes to your machine, you must pass through the User Account Control (U.A.C.) "Challenge" by running the installer program "as Administrator".


How to get all logged in users to SQL Server database for this week? I'm using SQL Server 2008R2 and I setup a testing database that I need to know who tried logging in so far. Is there a stored procedure or a View or anything that I can gather these type of information for this week or any specific day?

This is the same server that also has the production database, not sure if this will matter with performance issues as I'm reading online that Traces could affect?

Thanks for any advice.

You have a few options, some of which were already pointed out.

First, though, if these events haven't been captured so far there is no "historic" way of obtaining the data. There may be ways to get some of the data but chances are you won't get all or even most of it.

Going forward, however, it seems you only want those who successfully connect to the instance. This can be complicated if something such as connection pooling with a sql login, but I'll leave that as something for you to decide on how you want to track it back to the users.

Server Side Trace

Even though traces are deprecated they still work well. If it's just a successful login needed then this can be fairly low overhead. It has well known functions to load into tables and do your analysis.

http://msdn.microsoft.com/en-us/library/cc293613.aspx

Extended Events

Since you only need the successful logins, there is an event "sqlserver.login" that can work well for you. Send it to an event_file target and it's fairly simple as well. There are also functions to load the files and do your analysis.

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

Logon Triggers

Probably my least favorite option but here for completeness. The logins could be logged to a table, but why re-invent the wheel? Trace and extended events will do this for you.

http://msdn.microsoft.com/en-us/library/bb326598.aspx

SQL Audit

I didn't bunch this with extended events, even though in reality it leverages the same framework. This is an enterprise only feature, and while it can do this and is targeted for items such as this... I feel most aren't running enterprise edition.

http://msdn.microsoft.com/en-us/library/cc280386.aspx


what is the structure of a Mysql multi-column index? For example:
CREATE TABLE test(
col_1 INT,
col_2 INT,
col_3 VARCHAR(11),
col_4 VARCHAR(11)  )ENGINE = INNODB;
CREATE INDEX mul ON test(col_2,col_3,col_4);

Sorry,my English is not good,I mean create a multi-column index, what is this index structure？

Note: At the time this answer was written, the question was:

  In a B-Tree index, are all columns of a single row stored together, or
  are the columns stored in separate structures?

Most RDBMSs (including MySQL) implement indexes as B+Tree not B-Tree.
There are two types of B+Tree indexes: Clustered and Non-Clustered.

Both have in common that each intermediate level (including the root) of the index contains all columns that make up the key. The leaf level contains all key columns plus additional information.

In a clustered index, that additional information are all other columns of that table. For non-Clustered indexes, that additional information contains at least a reference to the actual table row. Some RDBMSs allow to also add additional columns here, so called "included" columns.

Either way, a B+Tree always contains the complete tuple relevant to the current index level within a single B-Tree node (which is usually equivalent to a data page. This storage organization is also called the "N-ary Storage Model" or NSM.

Some RDBMSs allow for column oriented storage, like SQL Server with the Column Store Index. That model is called the "Decomposition Storage Model" or DMS. However, a structure using the DSM is not organized as a B+Tree (or even a B-Tree).

There is a third form of storage that combines the two ideas. It is called PAX (partition attributes across). However it is rarely implemented.

References:

http://en.wikipedia.org/wiki/B%2BTree

http://www.pdl.cmu.edu/ftp/Database/pax.pdf

http://en.wikipedia.org/wiki/Column-oriented_DBMS


what happens If I remove mysql-bin.index file? Because of the file storage full, we are removing mysql bin log files.
Accidentally, we removed mysql-bin.index file.
What happens if I remove mysql-bin.index file?
These below are my DBMS info. Thank you.
MariaDB 10.4
Single Instance
engine: InnoDB Engine

If you erased all the binary logs, the mysql-bin.index can also go.
Do you still need binary logging ? You must restart MariaDB, and the mysqld process will recreate mysql-bin.index it for you.
If you no longer want binary logging, you must comment out log-bin from the MariaDB config file. You still need to restart MariaDB.


SSMS IntelliSense doesn't work on particular database I have SSMS 2014 installed on my desktop, and many 2012 and 2014 databases running in our data center.

So far I found one database (database A) doesn't have IntelliSense working, for exampletyping:

SELECT [put cursor here] FROM sys.objects

and moving cursor between select and from, pressing CTRL + J, I can see popup options for other databases, just not for database A. I have checked all options and refresh local cache, all don't work,  and I am using an account which has sysadmin permission.

The compatibility level is 11.0
Version is

  Microsoft SQL Server 2012 - 11.0.5556.0 (X64) Oct 31 2014 16:50:24 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

I can log on to the db server which is in US and see IntelliSense working there, it just doesn't on my desktop (in Australia). Is it most likely a latency issue? is there a timeout limit or setting for this feature?

It might be latency, or it might be something else. Run a SQL Server Profiler trace on the remote DB, starting the trace just prior to connecting via SSMS remotely. IntelliSense works by running queries to get the meta-data, so you will be able to see if it is even attempting to work or not, and if so, potentially if it is running into any problems.

You should filter the ApplicationName column on LIKE %IntelliSense%, though the full name of the program is Microsoft SQL Server Management Studio - Transact-SQL IntelliSense. Events to select: SQL:BatchStarting, SQL:BatchCompleted, SQL:StmtStarting, SQL:StmtCompleted, SP:Starting, SP:Completed, SP:StmtStarting, SP:StmtCompleted, RPC:Starting, and RPC:Completed.

Try it first on a DB that works so you know what the normal / expected behavior should look like. Hitting Control + Shift + R to force a reload of the local cache should produce a decent amount of activity captured by your trace.


Always-on Avaialbility Group Secondary replica NOT identical to Primay Can I have Always-on availability group primary node not identical to secondary?
example:
Primary Replica
RAM 64, Cores 8
Secondary Replica
RAM 256, Cores 32

As I estimate read-only queries to be 4 times writes, and I will use read routing to secondary I found a recommendation to have both always-on replicas to be comparable, and I don't know if it is a recommendation, or it means identical Thank you

It is supported, the recommendation from the docs is:

  For a given availability group, all the availability replicas should
  run on comparable systems that can handle identical workloads.

Recommendations for Computers That Host Availability Replicas

Having a larger system host a secondary replica would qualify, as both systems can handle the workload.  This is really no different than having multiple readable replicas.

In your estimate of read/write split, make sure you understand that even for a synchronous replica the redo of the log is asynchronous, and so the secondary will always be slightly behind the primary.  Any read-only query that cannot tolerate this latency must be run against the primary.  EG in an application if you write data, and then immediately query data to display to the user, expecting to see the data you just wrote, both queries must hit the primary.

Note that in case of a failure of the secondary, read-only routing will direct all traffic to the primary, which it may not be capable of handling.  So if you do this you may want to have reporting workloads connect to the secondary replica directly, instead of through the Availability Group Listener.

Also note that the IO requirements of a secondary are sometimes more than the primary, as the secondary is writing the database through the redo process, while the primary is writing the database through checkpoint/lazy writer.


How to display the query result separately for each item in an IN clause? Maybe it's a novice question, but I am trying to get the sum of balances for each account present in the table.

I am trying the below

Select
    SUM(BALANCE)
from
    dbo.account_balances
where
    ACCT IN (
            1000, 1001, 1002...)

This is giving me the total balance and I can see why. I need help in figuring out how to see the balances for each account individually in query result.

I do not have the rights to modify the table. There are no NULLs.

That's easy:

Select ACCT, SUM(BALANCE)
from dbo.account_balances
where ACCT IN ( 1000, 1001, 1002...)
GROUP BY ACCT


MySQL updating table with values selected from same table in one go I want to update quite a few values in a table. Said values already exist in another table.

I'm selecting said values using the following query:

SELECT c.last_purchase,
       so.customer_id,
       so.modified_at
  FROM customer c
 INNER JOIN sale_order so  ON c.customer_id = so.customer_id
                          AND c.country = so.country
 WHERE so.created_at > c.last_purchase

So far so good. But my sql skills aren't too great(hey, I'm being honest here).

The correct values are so.modified_at. I want to update c.last_purchase to match so.modified_at.

Originally I was thinking of using a WHEN THEN combination. Or perhaps some sort of stored procedure.

I could this by using a bash script or any other programming language that knows how to connect to a database. But I'd like, if possible, to do this strictly using MySQL. If not possible I'll have to settle for a bash script(I already wrote one).

Honestly, I have no idea where to start.

Any pointing in the right direction is more than appreciated.

As per my understanding, you need to update last_purchase field with modified_at field by joining customer table and sale_order table using the conditions you mentioned in your query.

Then try the following query

UPDATE customer   AS c
  JOIN sale_order AS s
   SET c.last_purchase = s.modified_at
 WHERE c.customer_id = s.customer_id
   AND c.country = s.country
   AND s.created_at > c.last_purchase;


Comparing table structure and contents using pg_dump output from two different databases If I have the output of pg_dump from two different databases, how can I get the difference in structure and contents of all the tables between these two databases?

Simplest way would be to dump the schema as plain sql and use text diff.

Or you can use tool like apgdiff which produces ddl statements representing the difference between the two database schemas.


TEMP table alternatives for pl/pgsql I am trying to emulate MERGE behavior with pl/pgsql:

-- Generate the data from funtion
CREATE TEMP TABLE temp_x  (id int, id2 int, data text, created_at timestamp, updated_at timestamp)  ON COMMIT DROP;
INSERT INTO temp_x SELECT * FROM set_gernating_function(p);

-- DELETE record with same id2
DELETE FROM x WHERE NOT EXISTS (SELECT 1 FROM temp_x WHERE temp_x.id=x.id) AND id2=p.id2;

-- UPSERT by (id, id2)
INSERT INTO x
    SELECT * FROM temp_x z
  ON CONFLICT(id, id2) DO UPDATE
    SET
      updated_at=excluded.updated_at,
      data=excluded.data;

But the temp table usage is generating bloat in pg_class and pg_attr table very fast -- Faster then I could do vacuum -- and affect other queries. Any idea?

There are some restrictions:

Preserve the created_at time if the id already exist
x have a delete-trigger, so no unnecessary delete.
The set_generating_function is slow
The size for temp_x is small. (< 50)
The run rate is very high (thousands per second)

You don't need the temp table:

You can do this in a single statement:

with temp_x (id, id2, data, created_at, updated_at) AS (
   SELECT *
   FROM set_gernating_function(p)
), deleted as (
  DELETE FROM x
  WHERE NOT EXISTS (SELECT 1
                    FROM temp_x
                    WHERE temp_x.id = x.id)
    AND id2=p.id2
)
-- UPSERT by (id, id2)
INSERT INTO x
SELECT *
FROM temp_x z
  ON CONFLICT(id, id2) DO UPDATE
    SET updated_at = excluded.updated_at,
        data = excluded.data;


Nulling other Date parameters in SSRS on selection of one I have set up multiple dates as input parameters and in SQL query, I've set up the WHERE clause like:

WHERE datecol1 >= @date1 OR (@date1 IS NULL)

So that handles dates as optional parameters. I have three date parameters. I want to make the report such that if the user selects one date, other two should be nullified, like the null should become checked.

Also, right now, I have this setting in default values part of the date parameter that i want to be dependent on the other date parameter.

What am I doing wrong here? Because this still does not work.

In the screenshots you're setting the expression for the Default values which will only change the values on the initial display of the report. This will not do anything to keep a user from changing those values, so you can easily get a render request with all parameters populated with something other than the default.

I'd suggest making it clear to your report users what is happening with these dates, don't do magic with their parameters behind the scenes. To do this, create an option in each parameter set (you can do something like UNION and hardcode the label and key) to ensure that report execution not only does what you expect, but makes this is clear to any other developer or user.

The user might select something like:

Param1 - "Not Used"
Param2 - 20151202
Param3 - "Not Used"

You can force this behavior with cascading parameters. You can run into issues with updates to cascading parameters though.

Setup like this:

@Param1

SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES

@Param2

SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES
WHERE -1 = @Param1

@Param3

SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES
WHERE -1 = @Param1
AND -1 = @Param2

This ensures that you can only select the "Not Used" option for param2 if param1 selected anything except "Not Used". You can still set the default to be not used for all of these.


Design a betting system for Fifa World Cup Russia 2018 Currently, I am working on a betting system for the next Fifa World Cup Russia 2018.
For this system, I have some strict points(constraints):

Authentication will be via external api, so no password or sensitive informations about users.
There are 4 types of betting: bet on a match, a bet on who will win the world cup, and a last one for the winner of each group.
To determine if a user won a bet, there are 3 possibilities: first one if the score he put is exactly the same, then he will win 4pts, the second one if the score is wrong, but the difference of goals is right then 3pts, the last one if one possibility from the last, then we check if he chose the right winner of the match and he gets 2pts. Otherwise, no points.
For matches which can contain penalties (from second roud), there is an extra betting (after typing score for the match, there is possibility to type who will win if they play 120min).
I want to have a statistic for the admin (games won or lost, and users bets ..etc).

As you can see in the picture below, that is what I tried out (not a complete version, i am stuck here) :

What can be the best way to store the extra bets(as said, there are extra bets, such as the winner of this cup, the winner of each group, and the winner in the case the match goes to penalties)?

I recommend that you work through the bets as follows:

First, determine what data you need to have for each type of bet. Then, look at what's common to all bets, and what only applies to some. Where possible, see if some things that appear different can be turned into something common. Last, look at where you connect to other tables, and figure out how to do that most effectively.

For example (I may get some of this wrong; not sure of how the World Cup matches all fit together):

All bets would involve:

a person making the bet
a winning team
a match of some sort (can you tell based on rounds, etc. what match would determine a group winner in advance, and what match will be the final match?)

Some bets involve:

Winning team's goals
Losing team's goals
120 minute match or not

So I would try to build a bet table that would allow for any type of bet. If score is irrelevant for some bets, ignore it. Treat the "penalty" bets as if they are separate from the regular bets (assuming the "penalty" bet is scored independently of the regular bet's score; if there's one score, then you have to keep them together).

Since ultimately each bet will be determined by a single match (again, assuming I'm following correctly, and that winning isn't a "best two out of three" or something), I'd tie each bet to its user and to the specific match that will determine it. If the "two out of three" situation is true, try to define a "match" such that it covers either a specific game, or a series of games; or, define two different things a bet can tie to, a match or a series (or some such). If you can have "match" and "series" bets, it might make sense for them to be in separate tables (especially if all series bets have certain different options than all match bets); still, you'd want to keep the tables as similar as possible, as you're likely to want to pull some information through both tables. Do try to minimize the this sort of table split; it does complicate calculations, even if it keeps the overall DB design cleaner.

Your actual solution will be different, I'm sure, but this should help point you to the basic principles you can use.


Prevent THREADPOOL waits due to idle worker thread trimming After reading Unusual THREADPOOL Waits by Josh Darnell, a Twitter user mentioned there is an undocumented trace flag to prevent trimming idle workers:

The idea is that once SQL Server has created enough threads to service the peak workload, it should not then trim worker threads (release them to the OS) after 15 minutes or so of them not being needed.
The idle worker threads will continue to use resources (e.g. memory) but there will not be the burst of THREADPOOL waits when more workers are suddenly required. Apparently this can be of assistance when using Always On Availability Groups.
What is this undocumented trace flag, and how does it work?

The trace flag is 8061.
It is undocumented, so should only be enabled when suggested by Microsoft support.
The flag needs to be enabled globally (or on start up):
-- Enable globally
DBCC TRACEON (8061, -1);

SQL Server checks to see if it should trim idle workers in:
sqldk!SchedulerMonitor::CheckScheduler

It skips routine excess worker trimming if trace flag 8061 is set.
Worker trimming is performed by:
sqldk!WorkDispatcher::TrimIdleWorkers

Worker trimming will still be performed if the following memory value is greater than zero:
sqldk!SOS_OS::sm_WorkerPressureCount

Quite rightly, the trace flag will not prevent trimming under all scenarios, but it does work for the purpose expressed in the question.