Sql getting debit, credit and balance issue I am using the query below to get Debit/Credit Balance from table statement

SELECT  t.[InvoiceID], t.S_Type as Type,
        t.Date, t.Debit, t.Credit, b.Balance
FROM Statement as t CROSS apply
     (SELECT Balance = SUM(Debit) - SUM(Credit)
      FROM Statement as x
      WHERE (x.date < t.date or
             x.date = t.date
            ) AND
            x.CustID = t.CustID
     ) b
WHERE t.CustID ='48' and date between '2015-01-01' and '2016-01-01'
ORDER BY t.date

output

InvoiceID   Type                Date    Debit   Credit  Balance
51         Service Invoice  2015-08-29  500.00  0.00    500.00
51         Receipt Voucher  2015-09-07  0.00    500.00  0.00
76         Service Invoice  2015-09-28  1000.00 0.00    1500.00
208        Sales Invoice    2015-09-28  500.00  0.00    1500.00
119        Sales Invoice    2015-10-31  500.00  0.00    2000.00
76         Receipt Voucher  2015-11-21  0.00    500.00  500.00
208        Receipt Voucher  2015-11-21  0.00    500.00  500.00
119        Receipt Voucher  2015-11-21  0.00    500.00  500.00
165        Service Invoice  2015-12-01  500.00  0.00    1000.00
165        Receipt Voucher  2015-12-22  0.00    500.00  500.00
224        Service Invoice  2015-12-31  500.00  0.00    1000.00

First, how can I get each Receipt Voucher under its invoice?

Second, when I have the Receipt Voucher with same date as the invoice how can I be sure it shows under the Invoice using order by type to get correct values for the balance?

The expected output - invoices ordered by date and its Receipt Voucher under it.

InvoiceID   Type           Date        Debit   Credit  Balance
51        Service Invoice 2015-08-29  500.00  0.00    500.00
51        Receipt Voucher 2015-09-07  0.00    500.00  0.00
76        Service Invoice 2015-09-28  1000.00 0.00    1000.00
76        Receipt Voucher 2015-11-21  0.00    500.00  500.00
208       Sales Invoice   2015-09-28  500.00  0.00    1000.00
208       Receipt Voucher 2015-11-21  0.00    500.00  500.00
119       Sales Invoice   2015-10-31  500.00  0.00    1000.00
119       Receipt Voucher 2015-11-21  0.00    500.00  500.00
165       Service Invoice 2015-12-01  500.00  0.00    1000.00
165       Receipt Voucher 2015-12-22  0.00    500.00  500.00
224       Service Invoice 2015-12-31  500.00  0.00    1000.00

The main thing you need is to generate the required order sequence using row_number().

seq = row_number() over
(
  partition by t.CustID
  order by t.InvoiceID,
           t.Date,
           CASE WHEN t.S_Type = 'Receipt Voucher' THEN 1 ELSE 2 END
 )

than use it for calculation of the cumulative balance

;

WITH cte
AS (
    SELECT  CustID,
        [InvoiceID],
        S_Type,
        DATE,
        Debit,
        Credit,
        seq = row_number() OVER (
            PARTITION BY CustID
            ORDER BY InvoiceID,
                DATE,
                CASE
                    WHEN S_Type = 'Receipt Voucher'
                        THEN 1
                    ELSE 2
                    END
            )
    FROM Statement
    )
SELECT c.[InvoiceID],
    c.S_Type AS Type,
    c.DATE,
    .Debit,
    c.Credit,
    b.Balance
FROM cte c
CROSS APPLY (
    SELECT Balance = SUM(Debit) - SUM(Credit)
    FROM cte AS x
    WHERE x.CustID = c.CustID
        AND x.seq <= c.seq
    ) b
WHERE c.CustID = '48'
    AND DATE BETWEEN '2015-01-01'
        AND '2016-01-01'
ORDER BY seq


Problem connecting to AlwaysOn Cluster Good day, I created a AlwaysOn HA cluster in Azure, 2 Server 2012s both with SQL 2012  Ent updated and patched on the same subnet.. These are the guides I followed:

Tutorial: AlwaysOn Availability Groups in Windows Azure (GUI)
 Tutorial: Listener Configuration for AlwaysOn Availability Groups in Windows Azure

AZURESQL01 and AZURESQL02 are the nodes and with their powers combined they form CAPTAIN_CLUSTER01. There are 3 instances with 3 AG groups and 3 listeners and I opened the firewall ports.

We have a VPN to our AZURE and I can connect to AZURESQL01 and AZURESQL02 and all the instances over the VPN, but I cannot connect to the cluster or the listeners at all. I can do so from either AZURESQL01 or AZURESQL02, but not from outside or our AZURE network. All I get is  various Login timeout messages.

I have double checked the setup and the ports and the DNS entries, but I cannot connect.

Am I a moron and missing something really silly, or is there some configuration not covered in the installation tutorial that I am missing?

Microsoft Azure completely ignores IPs of any internal network on Azure, if you want any computers to connect to a listener in an AlwaysOn availability group make sure 100% sure you specify the VIP, Virtual IP, of the Cloud Service and not the internal IP of the cluster. You will not be able to connect to the cluster or to the listener.

When on the Dashboard view, note the address shown under Public Virtual IP (VIP) Address.
Completely ignore the internal IP Address for listener configuration.


Filling two tables with a Foreign Key Relationship I need to create a star schema of table and apply on SSMS. To be simplier I focus on two tables:

One containing general informations about clients. Each client has a particular ID but can appear several times because he can be of multiple type.
One containing financial informations about them that are gathered every 3 months, each set of info about one client at a specific time will have an unique ID.

I set the client table as the parent table. I create it that way:

CREATE TABLE Customers
(
CostumerID INT IDENTITY (1,1) PRIMARY KEY,
Info 1 nvarchar,
    Info 2 nvarchar,
    Info 3 nvarchar
)

I create the financial informations table this way:

CREATE TABLE FinInfo    (
 FinInfoID INT IDENTITY (1,1) PRIMARY KEY,
 CustomerID INT,
 Date date,
 Info1 INT,
 Info2 INT,
 CONSTRAINT FK_Customers FOREIGN KEY(CustomerID)
 REFERENCES Customers(CustomerID)
 ON DELETE CASCADE
 ON UPDATE CASCADE     )

The thing is I don't know how to fill the costumerID in the FinInfo table without knowing it in the first hand.

CREATE TABLE Customers
(
CostumerID INT IDENTITY (1,1) PRIMARY KEY,
Info1 NVARCHAR(50),
    Info2 NVARCHAR(50),
    Info3 NVARCHAR(50)
)
GO
CREATE TABLE FinInfo    (
 FinInfoID INT IDENTITY (1,1) PRIMARY KEY,
 CustomerID INT,
 Date date,
 Info1 INT,
 Info2 INT,
 CONSTRAINT FK_Customers FOREIGN KEY(CustomerID)
 REFERENCES Customers(CostumerID)
 ON DELETE CASCADE
 ON UPDATE CASCADE
 )
 GO

DECLARE @CustomerID INT
 INSERT INTO dbo.Customers
         ( Info1, Info2, Info3 )
 VALUES  ( N'info1', -- Info1 - nvarchar
           N'info2', -- Info2 - nvarchar
           N'info3'  -- Info3 - nvarchar
           )
SELECT @CustomerID = SCOPE_IDENTITY()
INSERT INTO dbo.FinInfo
        ( CustomerID, Date, Info1, Info2 )
VALUES  ( @CustomerID, -- CustomerID - int
          GETDATE(), -- Date - date
          7, -- Info1 - int
          8  -- Info2 - int
          )
GO


Restarting database server or just the database I am an application programmer. We have a SQL Server 2012 instance that hosts many related databases (around 12 databases). The vendors did some changes to one of the database and the application and they want us to reboot the database server. I did some research and found out that rebooting a db server is always not a great option unless changes have been made in the server configuration. Is there a way to restart just that database? Would that help?

You can cycle the database by doing:

alter database YourDatabase
set offline
with rollback immediate; -- this line will rollback ongoing transactions
go

And then to bring the database back online you can do:

alter database YourDatabase
set online;
go

This is a way to "restart" the database itself directly within SQL Server, without bouncing either the SQL Server service(s) or the server operating system itself.

As for the question of "would that help?", there is no way for us to answer that.  You should be asking the vendor why do I need to restart the database server?  Oftentimes people that don't know what they are particularly doing will cycle an entire environment after a migration/upgrade/change.  Why?  "Just in case".

If the vendor made a change directly to the database (DDL/DML, whatever) then that change takes effect immediately.  So you really need to follow up with the vendor to find out why they want the database server restarted.


Automating the creation of postgres database I'm trying to automate the creation of a postgres database running on windows 10.
I'd like to be able to start from a default installation of postgres and automate the following steps (not necessarily in this order)

drop and re-create new database
drop and re-create new tablespaces
drop and re-create new users.
build database objects (tables, views, procedures, triggers etc)
grant users required permissions.
populate tables with initial data.

It's not too difficult to reduce this to a series of database commands (so long as they're executed in the correct order) the problem is that for few commands I keep encountering the error 'cannot be issued inside a transaction block'. This means that some commands like DROP DATABASE IF EXISTS and CREATE TABLESPACE can only be run (as far as I can tell) by manually single-stepping them, whereas I can call my table-creation scripts as a single block and re-create a 100 tables in a fraction of a second.
I'm not too worried about the extra few seconds I'm wasting by single-stepping some of the individual commands, but by doing some of it manually I run the risk of missing steps and making mistakes.
How can I automate (i.e. write scripts for) the whole process?

Update, script looks a bit like this...
DROP DATABASE IF EXISTS ????

DROP TABLESPACE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????

ALTER DEFAULT privileges IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM ????;
ALTER DEFAULT privileges IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM ????;

DROP USER IF EXISTS ????;
DROP USER IF EXISTS ????;
DROP USER IF EXISTS ????;

CREATE USER ???? PASSWORD '????';
CREATE USER ???? PASSWORD '????';
CREATE USER ???? PASSWORD '????';

CREATE TABLESPACE fastspace OWNER ???? LOCATION 'c:/????/????';
CREATE TABLESPACE slowspace OWNER ???? LOCATION 'd:/????/????';

DROP DATABASE ????;

CREATE DATABASE ????
  WITH OWNER = ????
       ENCODING = 'UTF8'
       TABLESPACE = fastspace
       LC_COLLATE = 'English_United Kingdom.1252'
       LC_CTYPE = 'English_United Kingdom.1252'
       CONNECTION LIMIT = -1;

GRANT CREATE ON DATABASE ???? TO ????;

COMMENT ON DATABASE ????
  IS '????database';

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO ????;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ????;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ????;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ????;

ALTER DEFAULT privileges IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ????;

Many (most) of the above commands can be run as part of a transaction block, but a few can't (dropping and creating the database for example).

Just run the script with autocommit turned on (the default in psql) and use an explicit transaction block for those statements that should run in a single transaction:
DROP DATABASE IF EXISTS ????

DROP TABLESPACE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????

begin;
  ALTER DEFAULT privileges IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM ????;
  ALTER DEFAULT privileges IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM ????;

  DROP USER IF EXISTS ????;
  DROP USER IF EXISTS ????;
  DROP USER IF EXISTS ????;

  CREATE USER ???? PASSWORD '????';
  CREATE USER ???? PASSWORD '????';
  CREATE USER ???? PASSWORD '????';
commit;

CREATE TABLESPACE fastspace OWNER ???? LOCATION 'c:/????/????';
CREATE TABLESPACE slowspace OWNER ???? LOCATION 'd:/????/????';

DROP DATABASE ????;

CREATE DATABASE ????
  WITH OWNER = ????
       ENCODING = 'UTF8'
       TABLESPACE = fastspace
       LC_COLLATE = 'English_United Kingdom.1252'
       LC_CTYPE = 'English_United Kingdom.1252'
       CONNECTION LIMIT = -1;

begin;
  GRANT CREATE ON DATABASE ???? TO ????;

  COMMENT ON DATABASE ???? IS '????database';

  GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO ????;
  GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ????;
  GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ????;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO ????;

  ALTER DEFAULT privileges IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ????;
commit;


Can a single SQL Server instance issue the same SYSDATETIME twice? I am contemplating using a datetime2(7) as a clustered index.

If I were to always populate that column using SYSDATETIME is it possible for the same instance to issue the same value twice (assuming we don't do something like set the system clock back)?

Alternatively, what is a cohesive strategy for ensuring uniqueness on such a column, even if it that comes at a cost of time precision?

For my use, I really only care about the seconds, but I will be doing a lot of range queries on this column so the CI seems appropriate.

Yes. The resolution of the Windows clock is decent, but not perfect. You should use another field, such as an INT IDENTITY, to ensure uniqueness.  But go ahead and cluster on the date first, if it suits your needs.

Alternatively, you could use a DATETIME PK, but supervise inserts with a stored procedure that verifies the uniqueness of the incoming date, and pauses if necessary.  I can whip up an example if you'd like.

Edit:

Another option, borrowing from ypercube's comment, would be a non-unique clustered index on the date field, and a unique non-clustered primary key on the ID field. This would keep your other indices smaller, as they would point to the narrow ID column, but would also entail two hops to get to actual data.


Really slow join, limit and order_by query I've got these two tables:

books (about 2 million records in total)

Containing a unique ID (isbn) aswell as an integer value representing popularity (index, but not unique).

category_pointers (also about 2 million records)

Containing a category_id (representing "fiction" [example]) and book_id.

Now, I want to fetch all books in the category "fiction" (category_id 22) and order them by popularity. This is my query:

SELECT * FROM books JOIN (
  SELECT category_pointers.isbn FROM category_pointers
  RIGHT JOIN books ON books.isbn = category_pointers.isbn
  WHERE category_pointers.category_id = 22
  ORDER  BY books.popularity DESC
  LIMIT  0, 30
) AS t ON t.isbn = books.isbn

The query takes about 2 minutes to run. Does anybody know how to point me in the right direction?

Thanks!

UPDATE
Definition for books

CREATE TABLE `books` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `isbn` bigint(20) NOT NULL,
  `title` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `published` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `pages` int(11) NOT NULL,
  `weight` double(8,2) NOT NULL,
  `type` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `added_to_index` int(11) NOT NULL DEFAULT '0',
  `last_refine` int(11) NOT NULL DEFAULT '0',
  `sub_title` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `author` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `author_id` int(11) NOT NULL,
  `publisher` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `publisher_id` int(11) NOT NULL,
  `language` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `details_url` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `image` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `popularity` int(11) NOT NULL,
  `update_authority` int(11) NOT NULL DEFAULT '0',
  `edition` int(11) NOT NULL DEFAULT '0',
  `format` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `band_type` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `saved_image` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `has_downloaded_image` int(11) NOT NULL DEFAULT '0',
  `published_year` int(11) NOT NULL DEFAULT '0',
  `last_price_check` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `books_isbn_unique` (`isbn`),
  KEY `books_popularity_index` (`popularity`),
  KEY `books_added_to_index_index` (`added_to_index`),
  KEY `books_published_year_index` (`published_year`),
  KEY `books_last_price_check_index` (`last_price_check`)
) ENGINE=InnoDB AUTO_INCREMENT=1748944 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

And category_pointers

CREATE TABLE `category_pointers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `isbn` bigint(20) NOT NULL,
  `category_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `category_pointers_isbn_index` (`isbn`),
  KEY `category_pointers_category_id_index` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1866535 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

I see some issues:

category_pointers is missing a unique key on its natural primary key.  Try using (category, isbn) as the primary key. It may be helpful to have the reverse index ('isbn', 'category'). This query can run strictly on the index.
category_pointers has a surrogate key for its primary index.  I would drop it and use the natural key (category, isbn).   I see this pattern far too frequently.  A surrogate key on a join table is a code smell to me.
The books_popularity_index may work better as a compound index defined as (popularity, isbn).  In index on (isbn, popularity) may help some search strategies.
The books table has a valid natural key isbn and a surrogate key id.  I would drop the id column as the primary index and use isbn as the primary key.
Use of RIGHT JOIN rather than INNER JOIN overrides the choice of driving table.  However, you are missing indexes which may help the query run faster.

Items 1 and 3 may improve your query performance.  Items 2 and 4 may be related to possible fragmentation of index ordered database tables.

I see two likely search paths:

small category: category - isbn - popularity.  This requires a sort on populary after all books in the category are found.  A (isbn, popularity) index might help.
large category: popularity - isbn - category.  Finds first thirty matching records without requiring a sort.  A large unpopular category may perform slowly.

Running the query prefixed by EXPLAIN should tell you how the data is being searched for.  This may help understand why the query is slow.  In most cases, each of the tables should be accessed by an index.  Only the index on category_pointers should be required.  (This may require both indexes specified above.)


How to model multiple types of contacts? I'm not sure if this can be called a polymorphic association - what is needed is to for each Message to have a Sender Contact and a Recipient Contact.
Both Sender Contact and Recipient Contact could be one of 2+ types. Currently SMS and Email are supported but more may come.

I have this urge to eliminate a possible duplication that would appear in case of having two different tables - sender_contacts and recipient_contacts. Also type column is a kinda of a DB-modeling smell.
But I'm not a DB modelling expert and rather looking for an advise from an experts how to properly model those relationships.

Any input is appreciated.

Thanks.

It depends on the data associated with each entity, the semantics of the data and the queries that will be used for the data.

For example, if “sender contact” and “recipient contact” can be the same and have the same attributes, it would be best to keep them in a single table. If they are strictly different, keeping them in different tables might be better.

Similar with contact types: I would have a common table that holds contacts for all types so that this table can be the target of a foreign key constraint. If just a few attributes differ (e.g. “phone number” versus “e-mail address”) just have them all in the same table and add a check constraint that makes sure they are NOT NULL according to the type. If a lot of attributes differ, you can consider adding foreigb key references to type specific tables.

Try to compose the SQL statements that will be used with these tables most often. If they look natural and simple enough and can be supported with few additional indexes, you probably got it right.


How to remove multiple records from one table based on the non-existence of a record in another table? I have two tables, 'Products' and 'Details'.

--------------------------
Products [ID, Name]
--------------------------
1, Link's Simple Syrup
2, Team Shirt
3, Animal DVD

-----------------------------------------------------------
Details [ID, Product_id, attribute_name, attribute_value]
-----------------------------------------------------------
1, 1, potency, "high"
2, 2, color, "blue"
3, 2, size, "large"
4, 2, words, "go team"
5, 3, title, "Animals with wide angle lenses"
6, 3, price, "1 million dollars"

So say 'Team Shirt' gets removed from the Products table, and now I need to clean up (remove) the 3 leftover Details it used.
Basically I want to look at each record from the Detail table and see if it's product_id corresponds to an existing ID in the products table.
If I can construct a SELECT statement that returns only these orphaned Detail records, then I can change select to DELETE and remove them.
That's my thinking anyway, any help creating the SQL query to accomplish that or suggestions on a different approach altogether if necessary would be much appreciated.

pseudocode of the logic might be something like this:

For each record in Details{
    if post_id is a NULL ID in Products delete this record
}

Two ways to accomplish that:

DELETE FROM details WHERE product_id NOT IN (SELECT id FROM products)

However, it is recommended to use left join:

DELETE details FROM details LEFT JOIN products ON details.product_id=products.id WHERE products.id IS NULL


Get Single greatest date for each record I have a list of Devices with a gps data table. I would like to grab the last gps coordinates received for each device in the query.

SELECT g.[TimeStamp], d.DeviceId, p.IsHistorical, g.Latitude, g.Longitude, g.Speed, g.Bearing, g.Fix, g.SatelliteCount, p.MessageTypeId
FROM Obd.Device d
LEFT JOIN obd.Packet p ON p.DeviceId = d.Id
LEFT JOIN obd.GPSData g ON g.PacketId = p.Id
WHERE d.ISDId = 1000 AND (g.Latitude IS NOT NULL OR g.Longitude IS NOT NULL)

Another way is to use window functions, either ROW_NUMBER() or MIN():

WITH
  ct AS
  ( SELECT
        g.[TimeStamp], d.DeviceId, p.IsHistorical,
        g.Latitude, g.Longitude, g.Speed, g.Bearing,
        g.Fix, g.SatelliteCount, p.MessageTypeId,
        rn = ROW_NUMBER() OVER (PARTITION BY d.DeviceID
                                ORDER BY g.[TimeStamp] DESC)
    FROM Obd.Device d
      JOIN obd.Packet p ON p.DeviceId = d.Id
      JOIN obd.GPSData g ON g.PacketId = p.Id
    WHERE d.ISDId = 1000 AND (g.Latitude IS NOT NULL OR g.Longitude IS NOT NULL)
  )
SELECT
    [TimeStamp], DeviceId, IsHistorical,
    Latitude, Longitude, Speed, Bearing,
    Fix, SatelliteCount, MessageTypeId
FROM ct
WHERE rn = 1 ;

Irrelevant to the question but the condition on g.Latitude and g.Longitude makes the LEFT joins equivalent to INNER joins.


Optimize Database Structure The service I created will increase the number of API call, actually we only insert on our log table arround 200k entries every days.
Soon we will need to support 1-2 Millions entries each day on our Database.
Each API call = One entrie on the DB.

How can I increase the performance of the table logs_fb ?
What is the number maximum of rows on a table ?

For multi-million row tables, you really need to consider normalizing columns wherever practical.  For example, there may be only a few thousand distinct user_agent values, you you are saving dozens or hundreds of bytes for multiple copies.  using a SMALLINT UNSIGNED linked to another table would shrink that to 2 bytes.

Why worry about space?  It has some impact on speed.  It forestalls running out of disk space.

Be consistent, and use reasonable datatypes:  If you won't have more than, say, 64K users, declare user_id in both tables as SMALLINT UNSIGNED.

What language needs 255 characters to describe?  Consider using an ENUM.

IP addresses, even IPv6, can be compressed into BINARY(16) (16 bytes) instead of the up-to-39 you currently have.  (And 255 is overkill.)

20 INSERTs per second is no problem for MySQL.
What is the API hitting?  A web server?  REST?  Java?  Can that handle 20/second?

What will the PRIMARY KEY be?  What SELECTs will be applied to the dataset?  These need to be discussed to talk about indexes, and whether you will have performance problems when reading.

Max number of records?  You will run out of disk space before you hit any database limit.  I think there is a limit of 64TB.  I've seen a billion-row table in action.

Do the math.  When will you run out of disk space?  Will you drop "old" data after some period of time?  If so, let's talk about an efficient way to do that -- else you will come back with a problem of a huge DELETE.


How to find the fitting version of pgadmin III? If you have different versions of PostgreSQL database servers you need a pgadmin III version with the respective pg_dump and pg_restore included.

I am running pgadmin III on a windows machine.

For example for PostgreSQL server 8.4.x you need pgadmin III with pg_dump 8.4.x, for PostgreSQL server 8.3.x you need pgadmin III with pg_dump 8.3.x and so on.

What I cannot figure out is how do you know which version of pgadmin III contains which version of pg_dump and pg_restore? I could not find the info on http://www.pgadmin.org/download/windows.php

I could just install some versions and try out, but maybe there is a better way.

pgadmin III V 1.8.4 comes with pg_dump 8.3.15

The most up-to-date version is always available with the most-up-to date version of postgres. So the coming 1.14 of pgAdmin support 9.1 with it's features, 1.12 -> 9.0, 1.10 -> >8.4. I noticed that pgAdmin will disable (or disallow you to use) certain features that do not support your installed pg version.

Sidenote: I made bad experiences with using the build-in backup/restore tools from pgAdmin (on Linux), no idea why this wasn't working but one machine just wasn't able to import a backup made by another one with same version, specs,... Had this issue many times, so I always prefer the command line export/import. The latter has never failed and offers more options for exporting and importing.


Postgres GIN index on (VARCHAR, JSONB) I have a table with schema along the lines of:

CREATE TABLE table1(
  account VARCHAR(64) NOT NULL,
  json    JSONB       NOT NULL
);

And I'd like to create an index on it that will allow me to run queries such as:

SELECT *
FROM table1
WHERE account = :account
  AND json ... -- Some JSON search expression

So I tried adding an index on this table like this:

CREATE INDEX index1 ON table1 USING GIN (account, json);

And I'm getting:

ERROR: data type character varying has no default operator class for
       access method "gin"
Hint: You must specify an operator class for the index or define a default
      operator class for the data type.

I've tried adding _varchar_ops:

CREATE INDEX index1 ON table1 USING GIN (account _varchar_ops, json);

And then I got:

ERROR: operator class "_varchar_ops" does not accept data type character varying

I realize I can simply add the account as a field of every json document, but I'd like to avoid this duplicity and potential gotcha or room for discrepancy, and just have the account as a column.

How can I solve this and get a combined index on these two fields?

Two indexes

Simply create two separate indexes. PostgreSQL will use both where appropriate.

CREATE INDEX ON table1 (account);
CREATE INDEX ON table1 USING GIN (json);

Using an extension

Or you can use the btree_gin.

  btree_gin provides sample GIN operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, "char", varchar, text, bytea, bit, varbit, macaddr, inet, and cidr.

It looks like this,

CREATE EXTENSION btree_gin;
CREATE INDEX ON table1 USING gin (account,json);

Under normal circumstances, I'd likely use two indexes.


Why does COALESCE function not work for this query? I am trying to count the number of columns that is retrieved from the subquery, and some subqueries may be empty. So instead of displaying it as an empty cell, I want it to be 0. Found out that I can do this by using the COALESCE function, but for example this query still gives me an empty cell:

CREATE VIEW third_table AS
SELECT *
FROM  (second_table
NATURAL FULL JOIN (
   SELECT assignment_id, COALESCE( count(*), 0 ) AS num_60_79
   FROM (
      SELECT assignment_id, mark_as_percent
      FROM avg_required_table
      WHERE mark_as_percent >= 60
      AND mark_as_percent < 80
      ) a
   GROUP BY assignment_id
   ) b);

Is this how COALESCE is supposed to be used?

To answer your question

COALESCE would be utterly pointless where you placed it to begin with. The aggregate function count() never returns NULL. And logic dictates that num_60_79 can never be lower than 1 in the subquery. Related:

Query optimization or missing indexes?

But that's irrelevant, because if a subquery returns no row, then it cannot return any values at all.

To be precise, in your case the subquery may return any number of rows but, when joining to second_table, if there is no row with matching values in (assignment_id, num_60_79) then all columns from the subquery b are filled with NULL values instead. Related:

Multiple correlated subqueries with different conditions to same table

NATURAL FULL JOIN is a very exotic way to join tables. Especially for somebody still learning how to use COALESCE. Quoting the manual:

  NATURAL is shorthand for a USING list that mentions all columns in
  the two tables that have the same names.

and:

  FULL OUTER JOIN returns all the joined rows, plus one row for each
  unmatched left-hand row (extended with nulls on the right), plus one
  row for each unmatched right-hand row (extended with nulls on the left).

I think it's safe to assume there is no column named num_60_79 in second_table and you surely wouldn't want to involve it in the join conditions if there was one. You only want to join on the column assignment_id, and that's what you should put in the query instead of the NATURAL key word.

Also, while FULL JOIN is theoretically possible here, we would typically see a LEFT JOIN.

You also don't need two layers of subqueries and some other noise in the query.

Proper query

All things considered, while you are ...

  trying to count the number of columns that is retrieved from the subquery

... my educated guess is you want this query instead:

SELECT s.*, COALESCE(b.num_60_79, 0) AS num_60_79
FROM   second_table s
LEFT   JOIN (
   SELECT assignment_id, count(*) AS num_60_79
   FROM   avg_required_table
   WHERE  mark_as_percent >= 60
   AND    mark_as_percent <  80
   GROUP  BY assignment_id
   ) b USING (assignment_id);


How costly are InnoDB rollbacks? I recently heard a fellow co-worker mention that InnoDB rollbacks are costly and negatively impact performance. How true is this statement?

To give a context, we use spring+hibernate and set @Transactional(readonly=true) for transactions where we need to only retrieve data. We also do see large number of rollbacks in sys.statements_analysis table.

InnoDB uses "optimistic" MVCC - when you do some changes, it applies them to the real tables and may even write them to disk. Commit only ensures that all the writes were finished and makes the changes visible and is otherwise almost instantaneous. That means that the rollback has to undo all these changes, possibly rewriting the pages on disk again and it has to do some more work in finding the previous versions in the undo logs etc. And the code is imho more optimized for the "primary" writes than for rollbacks too.

So the rollback may take at least the same and possibly more time than the inserts/updates/deletes took. I've met cases where someone tried to delete million of rows, killed the transaction after few hours (because it kept most of the table locked and they wanted to get rid of it and batch it properly next time) and the rollback took few more hours - in the worst cases they decided to restart the server (by force, because it does not respond until done) and found out that it continued the rollback during startup, effectively becoming inacessible for few more hours (before the kill it at least worked for rest of the data).

But as I wrote - the rollback time depends directly on the number of writes the transaction did and indirectly on how long it was active (more precisely how many changes other concurrent transactions did - the size of undo logs, which are "global" and not per-transaction). So if most of your transactions are select-only or not write heavy, the possible rollbacks possibly do not have to do too much work.

Contrast with other implementations, for example Firebird DB where when I executed a big delete, it finished surprisingly fast, but then when I tried to commit that, it took much longer than the delete itself.


counting how many likes one user gives another We recently installed a "like" extension on our phpbb message board, which is stored in a table we'll call "likes". I've been struggling to construct a query I've called "admirer", where it counts how many likes one user has given another. The output would ideally look like the following:

user_id_to | like count | user_id_from
---------------------------------------
    sam    |      6     |      mike
    sam    |      3     |      john
    mike   |      4     |      sam
    john   |      5     |      mike

Hopefully that helps. I have a query where it counts how many likes users get from a specified user_id in the WHERE clause as shown below:

SELECT users.username, COUNT(likes.like_id) AS love
FROM likes LEFT JOIN users ON users.user_id = likes.user_id_to
WHERE likes.user_id_from = [someone's user id]
GROUP BY users.username
ORDER BY love DESC;

However, I'm struggling to modify it without the WHERE, and have it display the count of likes from any user to any user. Any suggestions would be greatly appreciated.

Two things:

Group by both user IDs
Inner join to the users' table two times, use different alias to join it with different user id

SELECT u_to.username as user_to, u_from.username as user_from, count(*) as like_count
FROM likes
INNER JOIN users u_to on likes.user_id_to=u_to.user_id
INNER JOIN users u_from on likes.user_id_from=u_from.user_id
GROUP BY u_to.username, u_from.username
ORDER BY like_count DESC


best way to avoid redundant aggregate functions and/or group by columns Suppose I have two tables:

Foo:

id
baz

Bar:

id
foo_id
boom

So a Foo has many Bars.  I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set of Foos but I also want some properties from Foo.  The two most straightforward ways of doing this are ugly:

Method #1: Unnecessary Aggregate Functions

select
  foo.id,
  min(foo.baz) as baz,
  min(bar.boom) as min_boom
from
  foo
join
  bar on foo.id = bar.foo_id
group by
  foo.id;

Method #2: Unnecessary Group By Column

select
  foo.id,
  foo.baz,
  min(bar.boom) as min_boom
from
  foo
join
  bar on foo.id = bar.foo_id
group by
  foo.id,
  foo.baz;

This is not so bad when there's only one additional column from Foo besides "id", but if there are many columns that need to be included then the grouping becomes much less efficient.  A query like this gets around both these issues, but seems unwieldy:

select
  foo.id,
  foo.baz,
  x.min_boom
from
  foo
join
  (select
    foo_id,
    min(boom) as min_boom
  from
    bar
  group by
    foo_id) x on x.foo_id = foo.id;

Is there a better way?  Platform is Postgres if that matters.

If id is defined as the primary key, you can omit grouping by all the foo columns you want for the output as long as you are grouping by the id. This special case of grouping is in accordance with the current SQL standard and has also been covered in the PostgreSQL manual, starting from version 9.1:

  When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

(Emphasis added.)

So, if foo.id is the PK, this query would be valid:

select
  foo.id,
  foo.baz,
  foo.whatever,
  min(bar.boom) as min_boom
from
  foo
join
  bar on foo.id = bar.foo_id
group by
  foo.id;


Truncate log file mirrored database In SQL Server 2008, I have a mirrored database with a log file that has increased to 80GB, which is not normal and I have serious problem. I would like to decrease the log file size by truncating it.

Some info:

My database is in Full Recovery Model.
Backups for both database and log file taken every day.
My database is mirrored to a mirror SQL Server.
My database has active connections.
I have read so many articles and opinions but still I cannot decide what I should do in order to truncate log file without consequences.

Should I turn to simple recovery model and truncate?

Should I stop mirroring, do what I should do to principal database and then enable mirroring?

Should I create a new log file to another disk?

I have read so many things but I am really confused. Could someone give me a hint how should I cope with this problem?

Should I turn to simple recovery model and truncate?

That would be the worst thing to do it would break away mirroring and if you have big database you would again have to follow the whole process of creating mirroring again that could well be a tedious task.

  Should I stop mirroring, do what I should do to principal database and then enable mirroring?

Stopping mirroring is not a solution here and not going to help you. You need to find why your database log file grew so much my guess is huge DML operation is scheduled or ETL process taking place or rebuild of all indexes followed by update stats.

  Should I create a new log file to another disk?

I dont know where you read this but this is no where related to your problem. Writing to log file is sequential and only after completely utilizing first log file SQL Server will move to second so this is again not going to help

  My database is in Full Recovery Model. Backups for both database and log file taken every day

How often you take log backup, is it just once in a day. In full recovery model only transaction log backup would truncate log and allow other transactions to use log file and thus limiting the size of log so I would first suggest you to increase the frequency of transaction log backup.

At last you must read why transaction log keeps growing and run out of space


Is There A Way To Tell How Many Times A Query Has Executed? I am attempting to determine the number of times a particular query has executed in the course of a day. Is there a simple script that I can run to determine this?

You can use this query with few caution and need to understand thoroughly what the result is telling you.

sys.dm_exec_query_stats (Transact-SQL)

  Returns aggregate performance statistics for cached query plans in SQL
  Server. The view contains one row per query statement within the
  cached plan, and the lifetime of the rows are tied to the plan itself.
  When a plan is removed from the cache, the corresponding rows are
  eliminated from this view.

If a plan is not cached you will not see those query and does not
mean those did not run.
No way to aggregate accurately per day, you only get last execution time and number of times that the plan has been executed since it was last compiled.

Copied from above link and added 2 more columns.

SELECT qs.execution_count,
       creation_time,
             last_execution_time,
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1,
                 (CASE WHEN qs.statement_end_offset = -1
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
                       ELSE qs.statement_end_offset end -
                            qs.statement_start_offset
                 )/2
             ) AS query_text,
     qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid,
     qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
--WHERE qt.text like '%SELECT%'
ORDER BY qs.execution_count DESC;

If you need an exact count for 24 hours for any query you will need to set up your own extended event collection.


Find the days difference between two dates per month I have a table 'data' in which there are two columns i.e. Startdate and Enddate.
Startdate is the date when an employee joined working in a company and Enddate is the date when he left. I need to find the working days of employee per month.
If the Startdate is  '2015-06-21' and the Enddate is '2015-08-21' then the working days will be:
June 10days
July 31days
August 21days

I have the query for the total days for the work:
Select datediff(dd,startdate,enddate)
from tablename

but I need to take the days as above (per month).
Update
I actually have three columns, 'Startdate', 'Enddate' and 'Salary'. The full query needs to divide the salary over the calendar months. If salary is 620 then for the above dates sample the result is:
June-for 10 days = 100
July = 310
August = 210

Pretending this is your real table:

CREATE TABLE #Employees
(
  EmployeeID INT PRIMARY KEY,
  StartDate  SMALLDATETIME,
  EndDate    SMALLDATETIME
);

INSERT #Employees(EmployeeID, StartDate, EndDate)
  VALUES(1,'20150621','20150821');

Then this is one approach to get the results you're after (you'll need to replace #Employees with dbo.YourRealTableName of course, and this assumes you won't have anyone who has worked more months than the number of rows in your version of sys.all_columns - if you have a Numbers table that is a better approach). I added year to the output because, hopefully, you have employees that last longer than 12 months and probably some of those will last past January 1st - so being able to tell which June is which might be important.

;WITH x AS
(
  SELECT rn1 = ROW_NUMBER() OVER (ORDER BY [object_id])-1
  FROM sys.all_columns
),
y AS
(
  SELECT
    e.EmployeeID,
    x.rn1,
    rn2 = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY x.rn1 DESC),
    e.StartDate,
    EndDate = COALESCE(e.EndDate, DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)),
    sm = DATEADD(DAY, 1-DAY(StartDate), StartDate)
  FROM x INNER JOIN #Employees AS e
  ON x.rn1 <= DATEDIFF(MONTH, e.StartDate, COALESCE(e.EndDate, GETDATE()))
),
z AS
(
  SELECT
    EmployeeID,
    s = CASE rn2 WHEN 1 THEN StartDate
      ELSE DATEADD(MONTH, rn2-1, sm) END,
    e = CASE rn1 WHEN 0 THEN DATEADD(DAY, 1, EndDate)
      ELSE DATEADD(MONTH, rn2, sm) END
  FROM y
)
SELECT
  EmployeeID,
  [Month] = DATENAME(MONTH, s),
  [Year] = YEAR(s),
  WorkingDays = DATEDIFF(DAY, s, e)
FROM z
ORDER BY EmployeeID, s;
GO

This works for all employees, but you can easily add a filter inside y to limit it to a single employee or a subset. You might also want to filter for only those employees with an EndDate; it wasn't clear to me what you wanted to do with employees who still work there, so I assumed you would want to count working days up until today.

Don't forget to clean up:

DROP TABLE #Employees

Edit for updates (please stop changing the requirements):

CREATE TABLE #Employees
(
  EmployeeID INT PRIMARY KEY,
  StartDate  SMALLDATETIME,
  EndDate    SMALLDATETIME,
  Salary     INT
);

INSERT #Employees(EmployeeID, StartDate, EndDate, Salary)
  VALUES(1,'20150621','20150821',620);

Query:

;WITH x AS
(
  SELECT rn1 = ROW_NUMBER() OVER (ORDER BY [object_id])-1
  FROM sys.all_columns
),
y AS
(
  SELECT
    e.EmployeeID, e.Salary, x.rn1,
    rn2 = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY x.rn1 DESC),
    e.StartDate,
    EndDate = COALESCE(e.EndDate, DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)),
    sm = DATEADD(DAY, 1-DAY(StartDate), StartDate)
  FROM x INNER JOIN #Employees AS e
  ON x.rn1 <= DATEDIFF(MONTH, e.StartDate, COALESCE(e.EndDate, GETDATE()))
),
z AS
(
  SELECT
    EmployeeID, Salary,
    StartDate, EndDate,
    s = CASE rn2 WHEN 1 THEN StartDate
      ELSE DATEADD(MONTH, rn2-1, sm) END,
    e = CASE rn1 WHEN 0 THEN DATEADD(DAY, 1, EndDate)
      ELSE DATEADD(MONTH, rn2, sm) END
  FROM y
)
SELECT
  EmployeeID,
  [Month] = DATENAME(MONTH, s),
  [Year] = YEAR(s),
  WorkingDays = DATEDIFF(DAY, s, e),
  Portion = Salary * 1.0 * DATEDIFF(DAY, s, e) / (1+DATEDIFF(DAY, StartDate, EndDate))
FROM z
ORDER BY EmployeeID, s;

Results I get (there are decimals because your salary won't always be nicely divisible by the number of working days):

EmployeeID  Month   Year  WorkingDays  Portion
----------  -----   ----  -----------  ----------------
1           June    2015  10           100.000000000000
1           July    2015  31           310.000000000000
1           August  2015  21           210.000000000000

Again, clean up:

DROP TABLE #Employees


How to do a minor upgrade of PostgreSQL on Windows, e.g. 9.3.0 to 9.3.1? What is the recommended way to perform a minor upgrade from PostgreSQL e.g. 9.3.0 to 9.3.1 using the Enterprise DB built windows installer?  Should I uninstall first or just install over the existing installation?

The current installation was performed with postgresql-9.3.0-1-windows-x64.exe.  Now I want to upgrade using postgresql-9.3.1-1-windows-x64.exe.

On Windows, simply stopping the postgresql service, then running postgresql-9.3.1-1-windows-x64.exe on top of the existing 9.3.0 works.  No uninstall necessary.  Of course, a backup is recommended.

Clear, explicit documentation for the update procedure on Windows is absent.  Note that the documentation link provided by @dezso has been moved in the current manual to:
https://www.postgresql.org/docs/current/static/upgrading.html

The PostgreSQL Release Notes typically document migration tips in Appendix E.
 For example,

Appendix E. Release Notes https://www.postgresql.org/docs/current/static/release.html
Section E.1. Release 10.1, subsection E.1.1. Migration to Version 10.1
https://www.postgresql.org/docs/current/static/release-10-1.html#idm46428658121200

The best source of Windows installer information was the Enterprise DB forums.  Here are some posting I found that addressed the question...

2010 April - What's the right way to upgrade to a new version of PostgreSQL
https://web.archive.org/web/20121210220213/http://forums.enterprisedb.com/posts/list/2246.page
2009 September - Upgrade procedure when using one-click installer?
https://web.archive.org/web/20130603094838/http://forums.enterprisedb.com:80/posts/list/1906.page#6791

Note that the upgrade info moves around from release to release.  For example,

Version 10 location:
Chapter 18. Server Setup and Operation, section 18.6. Upgrading a PostgreSQL Cluster
https://www.postgresql.org/docs/10/static/upgrading.html
Version 9.3 location:
Chapter 17 Server Setup and Operation, section 17.6 Upgrading a PostgreSQL Cluster
http://www.postgresql.org/docs/9.3/static/upgrading.html
Version 9.0 location:
Chapter 24 Backup and Restore, section 24.4 Migration Between Releases
http://www.postgresql.org/docs/9.0/static/migration.html

Note that as of June 2017, EnterpriseDB replaced their community discussion forums affecting the links in this answer(https://web.archive.org/web/20171021012954/https://www.enterprisedb.com/news/enterprisedb-announces-new-postgres-rocks-online-user-forum).  I was able to hunt down some of the original posts on the Wayback Machine.  One dead link that I was unable to repair is: "2010 January - Upgrading to 8.4.2 from 8.4.1",
http://forums.enterprisedb.com/posts/list/2115.page#7888.


Understanding "bitmap heap scan" and "bitmap index scan" I'll try to explain my misunderstandings by the following example.

I didn't understand fundamentals of the Bitmap Heap Scan Node. Consider the query SELECT customerid, username FROM customers WHERE customerid < 1000 AND username <'user100'; the plan of which is this:

Bitmap Heap Scan on customers  (cost=25.76..61.62 rows=10 width=13) (actual time=0.077..0.077 rows=2 loops=1)
  Recheck Cond: (((username)::text < 'user100'::text) AND (customerid < 1000))
  ->  BitmapAnd  (cost=25.76..25.76 rows=10 width=0) (actual time=0.073..0.073 rows=0 loops=1)
        ->  Bitmap Index Scan on ix_cust_username  (cost=0.00..5.75 rows=200 width=0) (actual time=0.006..0.006 rows=2 loops=1)
              Index Cond: ((username)::text < 'user100'::text)
        ->  Bitmap Index Scan on customers_pkey  (cost=0.00..19.75 rows=1000 width=0) (actual time=0.065..0.065 rows=999 loops=1)
              Index Cond: (customerid < 1000)

My understanding of this node:

As explained there, the bitmap heap scan reads table blocks in sequential order, so it doesn't produce random-table-access overhead which happens as doing just Index Scan.

After Index Scan has been done, PostgreSQL doesn't know how to fetch the rows optimally, to avoid unneccessary heap blocks reads (or hits if there is a hot cache). So to figure it out it generates the structure (Bitmap Index Scan) called bitmap which in my case is being generated by generating two bitmaps of the indexes and performing BITWISE AND. Since the bitmap has been generated it now can read the table optimally in a sequential order, avoiding unnecessary heap I/O-operations.

That's the place where a lot of questions come.

QUESTION: We have just a bitmap. How does PostgreSQL knows by just a bitmap anything about rows' physical order? Or generates the bitmap so that any element of it can be mapped to the pointer to a page easily? If so, that explains everything, but it's just my guessing.

So, can we say simply that the bitmap heap scan -> bitmap index scan is like a sequential scan but only of the appropriate part of the table?

How does PostgreSQL knows by just a bitmap anything about rows' physical order?

The bitmap is one bit per heap page. The bitmap index scan sets the bits based on the heap page address that the index entry points to.

So when it goes to do the bitmap heap scan, it just does a linear table scan, reading the bitmap to see whether it should bother with a particular page or seek over it.

  Or generates the bitmap so that any element of it can be mapped to the pointer to a page easily?

No, the bitmap corresponds 1:1 to heap pages.

I wrote some more on this here.

OK, it looks like you might be misunderstanding what "bitmap" means in this context.

It's not a bit string like "101011" that's created for each heap page, or each index read, or whatever.

The whole bitmap is a single bit array, with as many bits as there are heap pages in the relation being scanned.

One bitmap is created by the first index scan, starting off with all entries 0 (false). Whenever an index entry that matches the search condition is found, the heap address pointed to by that index entry is looked up as an offset into the bitmap, and that bit is set to 1 (true). So rather than looking up the heap page directly, the bitmap index scan looks up the corresponding bit position in the bitmap.

The second and further bitmap index scans do the same thing with the other indexes and the search conditions on them.

Then each bitmap is ANDed together. The resulting bitmap has one bit for each heap page, where the bits are true only if they were true in all the individual bitmap index scans, i.e. the search condition matched for every index scan. These are the only heap pages we need to bother to load and examine. Since each heap page might contain multiple rows, we then have to examine each row to see if it matches all the conditions - that's what the "recheck cond" part is about.

One crucial thing to understand with all this is that the tuple address in an index entry points to the row's ctid, which is a combination of the heap page number and the offset within the heap page. A bitmap index scan ignores the offsets, since it'll check the whole page anyway, and sets the bit if any row on that page matches the condition.

Graphical example

Heap, one square = one page:
+---------------------------------------------+
|c____u_____X___u___X_________u___cXcc______u_|
+---------------------------------------------+
Rows marked c match customers pkey condition.
Rows marked u match username condition.
Rows marked X match both conditions.

Bitmap scan from customers_pkey:
+---------------------------------------------+
|100000000001000000010000000000000111100000000| bitmap 1
+---------------------------------------------+
One bit per heap page, in the same order as the heap
Bits 1 when condition matches, 0 if not

Bitmap scan from ix_cust_username:
+---------------------------------------------+
|000001000001000100010000000001000010000000010| bitmap 2
+---------------------------------------------+

Once the bitmaps are created a bitwise AND is performed on them:

+---------------------------------------------+
|100000000001000000010000000000000111100000000| bitmap 1
|000001000001000100010000000001000010000000010| bitmap 2
 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
|000000000001000000010000000000000010000000000| Combined bitmap
+-----------+-------+--------------+----------+
            |       |              |
            v       v              v
Used to scan the heap only for matching pages:
+---------------------------------------------+
|___________X_______X______________X__________|
+---------------------------------------------+

The bitmap heap scan then seeks to the start of each page and reads the page:

+---------------------------------------------+
|___________X_______X______________X__________|
+---------------------------------------------+
seek------->^seek-->^seek--------->^
            |       |              |
            ------------------------
            only these pages read

and each read page is then re-checked against the condition since
there can be >1 row per page and not all necessarily match
the condition.


pg_ctl status responds with "available" during failed startup I am using pg_ctl -D $PGDATA status to monitor whether my postgres standby database is up and running. Sometimes the database fails to start due to a missing WAL file, but its status is reported as "available".

Is there a simple way to see if postgres is available using the pg_ctl status option, or do I need to write a script to query the postgres database and monitor the output from script?

The pg_ctl status command tests that the postmaster process (corresponding to $PGDATA) does exist from the point of view of the operating system. In the positive case, it reports that it's running (note that the word available is not used).

Essentially that's implemented by reading postmaster.pid and checking the result of kill(that_pid, 0).

It does not mean that this PostgreSQL instance does accept connections or is able to execute queries. To that purpose, better use psql or a script like Nagios check_postgres plugin.


How do I full text search on a datetime column? I created is a DATETIME column. I also tried to change all Collation's of that table to the same utf8 type but it wouldn't stick.  Is it possible to do a FULLTEXT search on a DATETIME column? (%like% is way too slow).

The error I get is:

1283 - Column 'created' cannot be part of FULLTEXT index

--
edit:

$add_24 = $i + 1440;

$format_second = date("Y-m-d H:i", strtotime('-'.$add_24.' minutes'));

$p_price = mysqli_fetch_array(mysqli_query($con, "SELECT created FROM products WHERE created LIKE '$format_second%'"));

FULLTEXT only applies to string datatypes, such as VARCHAR and TEXT.  After all, it is looking for "words".

The charset/collation of a table is only the default for newly added columns.

You can ALTER TABLE ... CONVERT TO ... to change all the columns in a table.  But before doing so, tell us why you are doing it and whether there is already non-English text anywhere in table.  Using that ALTER may just make things worse.

What, in particular, are you searching for in the DATETIME?  It may be possible to change to a 'range' query that may run efficiently with an index.  Let's see the attempted query and SHOW CREATE TABLE.

More

WHERE created = '2018-01-11 20:49:01'

will check for a specific second

or

WHERE created >= '2018-01-11'
  AND created  < '2018-01-11' + INTERVAL 1 DAY

will check for any created in a specific day.

Etc.

Either of these examples may use an INDEX (not FULLTEXT) containing created.  I say "may" because there could be other issues.  Please provide the SELECT and SHOW CREATE TABLE.

(The API, such as mysqli, is not relevant to the question.)

Still More

Looking at this formulation...

SELECT  symbol,created
    FROM  products
    WHERE  symbol='".$ccc['symbol']."'
      AND  created LIKE '%".$date_format."%'
    ORDER BY  id DESC
    limit  0,48

I see that INDEX(symbol) is likely to be helpful, but nothing having to do with created is useful.  This is because of the leading wildcard on the LIKE, and because an index won't be used with a DATETIME and LIKE.  The way to get performance via an index is as discussed above (using a range).


Collations and codesets in PostgreSQL 9.x In Windows 7, the collation is set to "English_United States.1252" when I select the "English, United States" locale in the installer. In Linux, the collation is set to "en_US.UTF-8" by default.

I haven't found any way to set the collation's codeset to UTF-8 in Windows so I'm just wondering if the databases will behave differently in these example cases? Or in general, what's the impact of the codeset part of a collation to begin with?

I have the encoding set to UTF-8 in both databases and the question is whether the difference in codesets of collations will cause difference in behavior.

The documentation is not very clear with respect to the relationship between the encoding / character set of the database and the ctype / codeset of the collation. All that it mentions are the following statements (all found on the 22.3. Character Set Support documentation page):

  each database's character set must be compatible with the database's LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings

  On Windows, ..., UTF-8 encoding can be used with any locale.

  Important: On most modern operating systems, PostgreSQL can determine which character set is implied by the LC_CTYPE setting, and it will enforce that only the matching database encoding is used. On older systems it is your responsibility to ensure that you use the encoding expected by the locale you have selected. A mistake in this area is likely to lead to strange behavior of locale-dependent operations such as sorting.

There is an implication here that the LC_CTYPE value only has rules for the characters in its Character Set. A value of 1252 indicates an Extended ASCII Code Page for Windows Latin1. All of those characters can be encoded into UTF-8 (you current encoding), but that doesn't necessarily mean that locale-aware functions such as upper, lower, initcap, etc will behave as expected when operating on characters that exist outside of the Code Page. This should be testable by running one of those functions on a character that is not in the Windows Latin1 / Code Page 1252 character set. For example, Latin Small Letter Nj U+01CC:

ǌ

should capitalize to:

Ǌ

So, if (sorry, I don't have PostgreSQL at the moment to test with) the following;

SELECT upper('ǌ'), lower('Ǌ');

returns:

Ǌ   ǌ

then it looks very positive that the "1252" LC_CTYPE value is not adversely affecting anything. It would still be good to try a SELECT with an ORDER BY since sorting was mentioned a few times in the documentation as an area that would likely be affected if these values -- encoding and LC_CTYPE -- were in conflict.

You cannot change either LC_COLLATE or LC_CTYPE once a database has been created, so you could try creating a new database to see if you can get the desired settings, regardless of what the installer thinks you should have:

CREATE DATABASE my_db_name WITH
            ENCODING 'UTF8'
            LC_COLLATE='English_United States.UTF8'
            LC_CTYPE='English_United States.UTF8'
            TEMPLATE=template0;

You might need to look in the pg_collation system catalog to see what is available.


Getting current number of connections in mysql I want to know if there is any parameter to show the current number of mysql connections (or peak value in the past 10 minutes or something else). Right now, I only see max and max_used connections.
mysql> show global status like '%connection%';
+-----------------------------------+---------------------+
| Variable_name                     | Value               |
+-----------------------------------+---------------------+
| Connection_errors_accept          | 0                   |
| Connection_errors_internal        | 0                   |
| Connection_errors_max_connections | 434                 |
| Connection_errors_peer_address    | 0                   |
| Connection_errors_select          | 0                   |
| Connection_errors_tcpwrap         | 0                   |
| Connections                       | 2380515             |
| Max_used_connections              | 152                 |
| Max_used_connections_time         | 2020-07-09 19:28:43 |
+-----------------------------------+---------------------+
9 rows in set (0.00 sec)

mysql> show global variables like '%connection%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin1            |
| collation_connection     | latin1_swedish_ci |
| max_connections          | 300               |
| max_user_connections     | 0                 |
+--------------------------+-------------------+

max_connections limits the number of connections overall.  But usually many are in "Sleep" mode, therefore encurring virtually no load.
There is one "extra" connection allowed -- this lets 'root' get in to see what is going wrong, even when the max is hit.
Max_used_connections is a high-water-mark toward max_connections.
Threads_running may be closest to what you are looking for.  It is the current number of connections not in "Sleep".  It is often "1", namely the connection you are using to read the value.  A value of "10" is rather high.  "100" probably means that MySQL is stumbling over itself.
SHOW PROCESSLIST gives you hints of some of the above info, plus clues of what queries are running.
Connections is a counter that started when the server started.  Divide it by Updtime to get an average of new connections.  1/second is pretty typical.  100/second is high, but not necessarily 'bad'.
There is no metric to recent "recent", etc.  The would require a monitoring program or script.  I would not do SHOW GLOBAL STATUS more than once a minute, else it might have an adverse impact on the system.
Connection_errors_max_connections a counter that is optimally 0.  Since you have 434 and Max_used_connections = 152, I will guess that you started max_connections with the default of 151, hit the error, then raised max_connections to 300.  Am I right?
Raising to 300 is fine, though not necessary on 'most' machines.  You should also see if having lots of connections is justified by your app.  Maybe something else is going wrong.
A common issue is to have the webserver that talks to MySQL allow lots of connections.  It then simply assumes it can hand things off to MySQL; instead, it should allow fewer connections to the webserver itself.
If Threads_running gets high, response time will probably suffer.  The likely remedy is to find "slow" queries via the slowlog.  The fix may be a better index or a reformulation of a query.  Alas, Threads_running is hard to monitor.
For the slowlog:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
Another tunable: wait_timeout controls how quickly an idle connection will be disconnected.  Check that the clients are disconnecting when finished.


how to get complementary information from sp_readerrorlog when auditing what are the failing logins in one of my server, using the sp_readerrorlog procedure I get the following result, when running the following query:

 EXEC sp_readerrorlog 0, 1, 'Login failed'

it shows me the IP address of the servers where from the login was attempted.

I know that ping -a resolve ip addresses to hostnames

any ideas, even vague ones, or indications, as how I could get to those hostnames using T-SQL?

lets, say I can get to the ip address and put it into a variable. how then could I go and do a magic and get to the hostname for that particular ip address?

As other comments on your question indicated, there are probably better ways other than TSQL to get this data, but here is something I cobbled together using TSQL.

--Declare variables
--OldDelim and NewDelim are used to split on multi-char (Thanks to Aaron Bertrand for the idea)
DECLARE @olddelim NVARCHAR(10) = 'client:'
    ,@newdelim NCHAR(1) = NCHAR(9999);-- pencil (✏)
DECLARE @RowsToProcess INT
DECLARE @txt TABLE (txt VARCHAR(100))
DECLARE @cmd VARCHAR(100)
DECLARE @LogInfo TABLE (LogDate DATETIME,ProcessInfo VARCHAR(100),txt VARCHAR(max))

--Retrieve errorlog information
INSERT INTO @LogInfo
EXEC sp_readerrorlog 0,1,'Login failed'

--temp table to hold the results of parsing the errorlog info
DROP TABLE IF EXISTS #DataToProcess

--add row number to facilitate while loop processing and updates of temp table
SELECT *
    ,trim(replace(value, ']', '')) AS HostNameIP
    ,convert(VARCHAR(30), NULL) AS HostName
    ,ROW_NUMBER() OVER (
        ORDER BY newid()
        ) AS rn
INTO #DataToProcess     --insert into temp table
FROM @LogInfo li
CROSS APPLY (           --cross apply split information to get HostIP
    SELECT value
    FROM string_split(replace(li.txt, @olddelim, @newdelim), @newdelim)
    WHERE value LIKE '%]%'
    ) ca

SET @RowsToProcess = (
        SELECT count(*)
        FROM #DataToProcess
        )
DECLARE @cntr INT = 1

WHILE @cntr <= @RowsToProcess
BEGIN
    SET @cmd = 'nslookup ' + (
            SELECT HostNameIP
            FROM #DataToProcess
            WHERE rn = @cntr
            )

    DELETE FROM @txt
    INSERT INTO @txt
    EXEC xp_cmdshell @cmd

    --Update temp table with HostName from nslookup
    UPDATE #DataToProcess
    SET HostName = (
            SELECT trim(replace(txt, 'Name:', ''))
            FROM @txt
            WHERE txt LIKE '%name%'
            )
    WHERE rn = @cntr

    SET @cntr += 1
END

SELECT LogDate, ProcessInfo, txt, HostNameIP, HostName
FROM #DataToProcess

| LogDate                 | ProcessInfo | txt                                                                                | HostNameIP    | HostName                |
|-------------------------|-------------|-----------------------------------------------------------------------------------------------------------------|---------------|-------------------------|
| 2019-03-07 14:40:07.320 | Logon       | Login failed for user 'xyz'. Reason: Could not find a login matching the name provided. [CLIENT: 10.165.32.119] | 10.165.32.119 | sc-hgrckb2.anywhere.com |
| 2019-03-07 15:06:51.540 | Logon       | Login failed for user 'zzz'. Reason: Could not find a login matching the name provided. [CLIENT: 10.165.32.119] | 10.165.32.119 | sc-hgrckb2.anywhere.com |


Low performance with dynamic queries and cursors Doing some legacy work, I found the following procedure that used to take between 1.5 to 6 hours, totally not expected when dealing with less than 2 million records. After the change it takes less than 3 minutes.

Why this script was taking too much time?

  The script extracts only one value from one field, between 20 and 40
  different sources, each source has from 100 to 100,000's records. Total records at the destination table is up to from half million to 2 millions.
  The table SOURCE_TABLE_FIELDS contains the table names, fields and criteria to build the dynamic queries.

DECLARE source_c CURSOR FOR   --cursor starts here
SELECT table_name, field_name, condition
FROM SOURCE_TABLE_FIELDS

OPEN source_c

FETCH NEXT FROM source_c
INTO @table, @field, @condition;

WHILE @@FETCH_STATUS = 0
    BEGIN

        PRINT 'DEBUG CHECKPOINT: START LOOP'

        declare @sql as nvarchar (1000)
        declare @sql_source as nvarchar(1000)

        set @sql_source = 'select ' + @field + ' as SOURCE_FIELD from ['  + @table + '] A '
                        + ' WHERE 1=1 ' +
                        + @condition +
        'EXCEPT SELECT DISTINCT DEST_FIELD FROM ' + @DESTINATION;
        set @sql = 'INSERT INTO ' + @DESTINATION + '(DEST_FIELD) ' + @sql_source;

    EXEC SP_EXECUTESQL @SQL
    PRINT 'DEBUG CHECKPOINT: END LOOP'
    FETCH NEXT FROM source_c INTO @table, @fild, @condition;
    END
CLOSE source_c;
DEALLOCATE source_c;

  Look at the PRINT 'DEBUG' marks; sometimes the last line printed was from the end, sometimes the one at the start. It could take up to an hour to jump. (I checked this by printing timestamps and everything).

By sometimes I mean, usually after the first 10 loops, randomly. No matter if I sorted out the sources with small records at first or at last.

Important remarks:

I don't have permission to access the SQL-Profiler
I don't have access to sys.dm_exec_query_stats or any other high level stuff.
I'm not a DBA, only a developer but with tons of SSIS work lately.

If you are considering using cursors in SQL Server, it may be helpful to use arguments that best fit your use case, as they may significantly change the performance of the cursor.

Conveniently, there have been many performance studies on these arguments in blog posts to look at. All possible arguments can be found for the current SQL Server version here: DECLARE CURSOR (Transact-SQL)

My favorite for cases similar to yours is:

DECLARE CURSOR LOCAL FAST_FORWARD

LOCAL tells SQL Server that the data set your working with is the only one it should care about. FAST_FORWARD provides flexibility to choose between static and dynamic SQL (though one downside is that it inhibits parallelism). One example of why these two arguments matter when determining cursor performance can be found here: Using SQL Server cursors – Advantages and disadvantages

Aside from this, large data sets tend to perform better, and are more accurate by using set based operations. This is due to two things: the nature of changing data sets/the limitations of cursors when data changes, and the inability to take advantage of parallelism which could severely cripple performance. Like Peter and I mention in the comments below this answer, fully testing and understanding cursor options, or not even using a cursor at all, could drastically change not only your query performance, but your consistency.


LSN mentioned in Backup is missing in Backupset I was testing some restore scenario for one of my requirement.
SO while i was restoring i got the below error.
--Msg 4305, Level 16, State 1, Line 79
--The log in this backup set begins at LSN 38000000116400001, which is too recent to apply to the database.
--An earlier log backup that includes LSN 38000000116100001 can be restored.

So i checked the backupset table to find the missing log backup.
But i couldn't find that LSN in the results.

I was testing a scenario that is why the full backups are taken every 15 minutes and tlog backup schedules every 5 minutes.
I notice that Full backup and Tlog backup occurs at the same time every 15 minutes.Does that create any issue while restoring.?

Also the restore scenario i am trying is as below:
I start with a full backup  restore,then i restore all tlog backups and i skip full backups which occurred in between.?
Please suggest if there is any issues in that?

But i couldn't find that LSN in the results.

The tran log backup taken at 09:00 contains the lsn 38000000116100001.

  Full backup and Tlog backup occurs at the same time every 15 minutes.Does that create any issue while restoring.?

No. Full backups never affect the log chain.

  I start with a full backup restore,then i restore all tlog backups and i skip full backups which occurred in between.?

Yes that is correct.


How to do housekeep the database in sql server 2008 by specific date range? I have database with around 160 GB size. The database contains the data is from 2013 to until today, there are 180 tables, I want to remove the records in specific date range like 2013 to 2014 in all tables. How to do in SQL Server 2008, Is there any query technique or any tool in SSMS?

Deleting data is easy from the syntax point of view, but that's not all there is. DELETE FROM myTable WHERE someDate < deletionDate might give you a few nasty suprises.

Constraints might block you from doing a delete. A textbook example is a foreign key constraint between customer and order tables. Deleting a customer is not allowed as long as there are orders. If it would, you would end up having orders not linked to a customer. In order to overcome this kind of limitations, you must understand the database schema and start deleting from the right table first (or rely to constraint to use ON DELETE CASCADE.)

Don't underestimate the performance effects. If you are lacking proper indexing (or have overextensive indexing), deleting records might generate serious a performance hit by creating lots of unnecessary index updates and/or full table scans. In addition, write locks will cause overall performance hits for any operations from the affected tables.

Transaction log management is a common gotcha. Pruning a lot of data will generate lots of transactions and you log files will go sky high. As a best practice, break the delete operations in batches. Aaron's blog has detailed discussion about the issue.


Whats the best and fast way to import a huge DBF file to a Postgres in Windows? I have some huge DBF files (about 35 GB) to import to Postgres.
Whats the best and fast way to do it?

OBS: I have 300 GB of free disk space and 8 GB RAM.

We faced a similar problem but our solution involved some coding. I don't know if that's an option for you.

We followed the usual phases:

We read the DBF metadata and created an analogous SQL 'create table' statement. The mapping is fairly straightforward except that we checked the number of digits for numeric types and coerced them to ints or longs (int8) as appropriate. Our data had a lot of foreign keys and it was better to treat them as int8 than numeric(15,0).
We read the DBF metadata again and created a (java) prepared statement that would insert a single line of data.
We "called" that prepared statement for each row in the DBF file. This makes sure we don't have problems with inadvertent SQL injection, e.g., names like O'Reilly. In reality we used batches of 400 rows (ps.addBatch() and ps.executeBatch() in java) and used an explicit begin/commit block of 20 insert statements. Both dramatically improve performance.
Finally we added any primary keys.

We could have gone through an intermediate file and used COPY but it was easier (and safer) for us to do it programmatically.

We also wrote our own DBF reader. It only took a day and we were able to work around some numeric overflow bugs that we found in third-party libraries. You can find enough information on Google that this isn't too hard.

One thing to be careful about is that "dbf" is vague. All dBase files have the same overall structure but there are tweaks from different publishers and versions. They're documented but you'll want to determine what version your file uses and then code to it.


How do I uninstall the old version of Postgres on CentOS 7? I just installed Postgres 9.6 on CentOS 7.  However when I run

[root@server tmp]# /usr/bin/psql --version
psql (PostgreSQL) 9.2.23

It still seems to be pointing to my old version.  How do I uninstall the old version (or figure out what its called) without harming the new version?

The newer version of Postgres will be in /usr/pgsql-9.6 (or a similar location) so you can simply use the alternatives command to make this one the default.


Interpretation of multiple join order I have a problem using multiple joins in one SQL Statement. For example, I have 10 Tables which are tbl1, tbl2, tbl3,..,tbl10. I would like to select some columns from each table.

If I use joins in between, I don't know how to use Left, Right, etc. in the SQL statement from priority aspect. I mean is it organized with parentheses or something else?

Relations for example are like following:

My query is as following:

SELECT T1.col1, T2.col2, T3.col3, T4.col1 AS Expr1
FROM T1
INNER JOIN T1_T2 ON T1.id = T1_T2.T1_FK
INNER JOIN T2 ON T1_T2.T2_FK = T2.id
LEFT JOIN T2_T3 ON T2.id = T2_T3.T2_FK
INNER JOIN T3 ON T2_T3.T3_FK = T3.id
LEFT JOIN   T3_T4 ON T3.id = T3_T4.T3_FK
INNER JOIN T4 ON T3_T4.T4_FK = T4.id

How does SQL Server interpret the joins? Can one represent it like execution priorities with parentheses? I mean: ((T1 INNER Join T1_T2) INNER JOIN T2) LEFT JOIN T2_T3) ?

select tbl1.col1,tbl2.col2,tbl3.col3,
tbl4.col4,tbl5.col5,tbl6.col6,tbl7.col7,tbl8.col8,
tbl9.col9,tbl10.col10

from tbl1.col1 as tbc1

left outer join tbl2.col2 as tbc2 on tbl1.PK=tbl2.FK
left outer join tbl3 as tbc3 on tbl2.PK=tbl3.FK
left outer join tbl4 as tbc4 on tbl3.PK=tbl4.FK
left outer join tbl5 as tbc5 on tbl4.PK=tbl5.FK
left outer join tbl6 as tbc6 on tbl5.PK=tbl6.FK
left outer join tbl7 as tbc7 on tbl6.PK=tbl7.FK
left outer join tbl8 as tbc8 on tbl7.PK=tbl8.FK
left outer join tbl9 as tbc9 on tbl8.PK=tbl9.FK
left outer join tbl10 as tbc10 on tbl9.PK=tbl10.FK

where tbl1.col1='some particular column value'
Group By tbl1.col1
Order tbl1.col1;

I am writing the sql query but it will depend on your table structure . How many there is PK and FK in your each table.


Who is using a SQL Server database user credential? I have a database user -- let's say "x" -- and I want to know how many Windows (desktop) users are using this database user (who2 or who is only give the current result, I want to know the entire history)?

Actually this question arise when you join any new organization and you don't know how many Windows or desktop users are using a specific database user. Is there any way to know this? How do you track all Windows users who are using this (x) user credential?

You could use a logon trigger perhaps, going forward (see here and here).

For historical information, by default, SQL Server doesn't even track successful logins (though this can be turned on - however it means you will need to parse the SQL Server log for some little bit of information). So while you might get some information (such as which workstations were using which logins) from the default trace, it would rely on them performing actions that get logged there (and would only go back as far as your default trace data goes anyway).

Also see this answer.


Is it safe to set xp_cmdshell value 1? I need to use xp_cmdshell in one of SQL job.So is it safe to reconfigure xp_cmdshell to 1 or can i set value 1 in the starting of SQL statements and set it back to 0 in the end of SQL Statement ?

No, it is not very safe if you get untrusted input into the database (i.e. if it is on a web server or such). You basically hand the user a really dangerous tool if you have any SQL injection vulnerability. I would not run that risk on public-facing servers.

Internal servers may be another thing, but internal attacks are often overlooked, so probably "we" should protect against internal attack also.

Also, if setup correctly (see https://msdn.microsoft.com/en-us//library/ms175046.aspx ) you need a proxy account and all kinds of SQL Server pereparations to use it.

I would always suggest using SSIS or SQL Server Agent to perform OS level tasks.


SQL Server 2008 R2 upgrade to 2017 I need to upgrade and migrate SQL Server, SSRS, SSIS to the newest OS version and SQL Server version.

Server OS is currently 2008 R2 with SQL Server 2008 R2 Ent. installed.

My original plan was to do in place upgrades and then migrate to new server OS. It sounds like SQL Server 2017 is not supported on 2008 R2, so I'm not sure whether or not this is an option.

What is the best migration/upgrade path for server OS upgrade and SQL Server version upgrade?

Windows Server 2008 R2 and SQL Server 2008 R2 Ent -> Windows Server 2019 and SQL Server 2017 Ent.

As always it depends...

Whats the complexity of your instance?
How many SSIS packages / SSRS reports do you need to move along?
How much time do you have to perform this upgrade?
Whats your maintenance windows within which the service can be inaccessible?
And many many more...

Before you start looking for any kind of answers I would recommend to create an inventory of what exactly do you have.
Very helpful thing is to add source control over your entire DB server. With that in hand you can "mask it a bit" and then share with the community - much higher chances for good answers.


pass data from deleted table to another This is the situation:

I have a table, with name TABLE_1 I would like delete TABLE_1 and then create another table with the same name and some of its columns.

TABLE_1 has records, but I need to pass it to the new table TABLE_1

What is the the most effective way to to this?

UPDATE

old TABLE_1:
    ID int(11) NOT NULL
    ACTIVE int(11) NOT NULL
    SENT_DATE DATETIME
    SENT INT(11) NOT NULL
    DESCRIPTION VARCHAR(50)

new TABLE_1:
    ID_TABLE_1 int(11) NOT NULL AUTO_INCREMENT
    STATUS TINYINT(1) NOT NULL DEFAULT(1)
    SENT_DATE DATETIME
    SENT TINYINT(1) DEFAULT(0)

In the ACTIVE and SENT columns are stores just 1 or 0. The relation between columns are (from old to new):

* ID -> ID_TABLE_1
* ACTIVE -> STATUS
* SENT_DATE -> SENT_DATE
* SENT -> SENT

If you want to keep the existing rows and don't want the table renamed but only a few columns altered/changed/dropped, you can do this with a single ALTER TABLE statement. This will only fail if there is no index on (id):

ALTER TABLE table_1
   CHANGE COLUMN id
      id_table_1 int NOT NULL AUTO_INCREMENT,
-- ADD PRIMARY KEY
--    (id_table_1),
   CHANGE COLUMN active
      status TINYINT NOT NULL DEFAULT 1,
   CHANGE COLUMN sent
      sent TINYINT DEFAULT 0,
   DROP COLUMN description ;

Tested at SQL-Fiddle


Accessing Encrypted SQL Server database through a web-based application I have secured a database within SQL Server Instance on my local machine using Netlib Encryptionizer. I can access the database by securing my SQL Server instance using the same password as encrypted database password otherwise it is unaccessible through SQL Server which is what i want.

However, I want a web-based application to access my encrypted database even when my SQL Server Instance is unsecured.

Can anyone help me with this??

I see this is an old thread so I don't know if you still monitor it. I'm from NetLib Security. Can I suggest you open up a support incident here?
https://netlibsecurity.com and then click Support.


Is there a query to check the current WAL size in PostgreSQL? I'm using a hosted PostgreSQL database where I don't have shell access.  Is there a query I can use to check the current WAL log size?

If you have superuser access, you can use:
select *
from pg_ls_dir('pg_xlog');

which will return one row for each file in the directory pg_xlog. As the size for a WAL segment is fixed, you can easily calculate the total size by multiplying the number of rows by 16MB:
select count(*) * pg_size_bytes(current_setting('wal_segment_size')) as total_size
from pg_ls_dir('pg_xlog') as t(fname)
where fname <> 'archive_status';

Alternatively you can use pg_stat_file() to return information about the files:
select sum((pg_stat_file('pg_wal/'||fname)).size) as total_size
from pg_ls_dir('pg_xlog') as t(fname);

Starting with Postgres 10 you can use:
select sum(size)
from pg_ls_waldir()


SHRINK FILE works with SA but not as a normal user My problem is that when I try to shrink the Log it works when I am logged in as SA but when I log in as a "normal" USER it does not shrink the log. (SQL Express 2008r2)

I have tried to find a answer to my problem but I cant. So please help me! =)

The last thing you want is allowing a normal user to run DBCC SHRINKxxx

This should be reserved for highly privileged users to run in a few rare circumstances. This should not be a regular or normal operation.

Permissions for both DBCC SHRINKDATABASE and DBCC SHRINKFILE are

  Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

If you really want to run this because you know better, then use a stored proc with EXECUTE AS

CREATE PROC dbo.IKnowBetter
WITH EXECUTE AS OWNER -- escalates to db_owner in dbo schema
AS
DBCC SHRINKFILE (1);
DBCC SHRINKFILE (2);
GO
GRANT EXECUTE ON dbo.IKnowBetter TO OikUser;


Dividing a column into three columns I have this table:

Name    Address                  Code
James   CT                       Homeaddress
Mark    mark@goole.com           Email
John    John@hotmail.com         homeemail
...

This is what I want:

Name        Homeaddress      Email           homeemail
james       CT
mark                         mark@google.com
john                                         John@hotmail.com

I am creating a view and dont have privilages to alter the base tables. I was thinking maybe an IF statement might help

I think you can use CASE expression. Something like that:

SELECT Name,
  CASE Code WHEN 'Homeaddress' THEN Address ELSE NULL END AS Homeaddress,
  CASE Code WHEN 'Email' THEN Address ELSE NULL END AS Email,
  CASE Code WHEN 'homeemail' THEN Address ELSE NULL END AS homeemail
FROM yourtable


Can you grant an Active Directory Group rights on a linked server? I am trying to grant rights to an Active Directory Group on a Linked Server. I am unable to do it; it only allows me to grant rights to individual active directory users. Does anyone know how to achieve this?

You can do by creating a role for the AD Group user and assigning permissions to it.

Create your Active Directory Group as a login in SQL Server as below :

USE master;
GO
CREATE LOGIN [COMPANYDOMAIN\ADGroup] FROM WINDOWS;
GO

Then create a user from the login in the database

USE DatabaseName;
GO
CREATE USER [COMPANYDOMAIN\ADGroup] FROM LOGIN [COMPANYDOMAIN\ADGroup];
GO

Now Grant appropriate permissions to the user

Note: Granting a server permission to a database role is not possible

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER to [COMPANYDOMAIN\ADGroup];


Hierarchical query from two tables Help me create a hierarchical query.
I have two table:

Table 1(dic_kgs)

KGS_ID    KGS_PARENT_ID    KGS_CODE
1                           A
2              1            A0
3              1            A1
4                           B

and so on

Table 2(dic_kgs_strings)

KGS_ID    LANG_ID    KGS_NAME    KGS_ADD_INFO
1            1        Name1
2            1        Name2
3            1        Name3
4            1        Name4

and so on.

I want to get result in the follow view:

A Name1
A0 Name2
A1 Name3
B Name4

I have writen only part of query:

select kgs_code
  from dic_kgs
  start with dic_kgs.kgs_parent_id is null
      connect by prior dic_kgs.kgs_id = dic_kgs.kgs_parent_id;

Thanks.

Assuming kgs_id is the primary key of both tables (which is a little strange), join the tables before doing the connect by:

select kgs_code, kgs_name
from (select * from dic_kgs join dic_kgs_strings using(kgs_id))
start with kgs_parent_id is null
connect by kgs_parent_id = prior kgs_id;

alternatively look up kgs_name after the connect by (this approach can be helpful in more complicated situations such as if not all records in dic_kgs have a corresponding record in dic_kgs_strings):

select kgs_code, ( select kgs_name
                   from dic_kgs_strings
                   where kgs_id=kgs_code.kgs_id ) as kgs_name
from dic_kgs
start with kgs_parent_id is null
connect by kgs_parent_id = prior kgs_id;


Snapshot Folder Location for Replication with Clustered Instance I am assigned to create a transactional replication with a SQL Server clustered instance for reporting purposes. I'm planning to use the Distributor and the Subscriber on the same box.

Regarding the snapshot folder, is it ok to place it on the same box where the Distributor\Subscriber resides? Will there by any problems?

As long as you can access that folder from all the nodes of the cluster there shouldn't be any issue.

Just make sure you have a decent nw connection between the cluster nodes and the replication box, otherwise the generation of the snapshots will take a long time.


User roles and permissions Look at the attached graphic.

I'm trying to keep this as simple as possible.

Basically I want to have multiple admins(or superusers) who own regular users who own sites.

Admins can give regular users permission or role to a website.
Admins can see and edit all sites of the users they own.
Users can only see & edit their own sites. (based on the role).
One site can be managed by multiple users.

I don't know if I should have permissions table with multiple rows for each role so one user can have many rows for each role or maybe use an array in one row only... Idk. I'm stuck there.

What's the best way to do this simply?

P.S: I know the graphic has User2 under site1 and site2 but I don't need it that complex. Same users for same site but different roles is sufficient. No need for 1 regular user to edit 2 sites.

The simplest structure would be a PERMISSIONS table like this:

create table PERMISSIONS
( Site_ID int
, User_ID int
, Role char(1)
, primary key (Site_ID, User_ID, Role) -- scenario 1
-- OR:
, primary key (Site_ID, User_ID) -- scenario 2
)

Use values for Role such as:

A = Administrator
P = Publisher
E = Editor

How many rows you need depends on how your code interprets the data.  You could have very simple code which interprets each permission strictly at face value.  To do an administrative task, the user would need an 'A' permission.  To do a publishing task, they would need a 'P' permission, etc.  In this case an administrator would need three records per site (one each of A, P, E).  Similarly publishers would need two records per site and editors only one.  There is a risk in this approach that data consistency errors could creep in, like someone could be a publisher, but not an editor (by virtue of the 'E' record being missing).

Alternatively, you could make your code more complex, in which case each user only needs one permission record per site.  In this scenario, administrative tasks require an 'A' permission, publishing requires an 'A' or a 'P' and editing requires 'A', 'P', or 'E'.  The advantage of this approach is that there is less data to maintain and you won't have inconsistencies.

Note too that you do not need to have the Users.Parent column.  If an administrator has an 'A' permission for a site, that automatically gives them control of all of the users for that site.


Does this normalized model for artist music records make sense? I want to make a database about one artist's music records... CD, Vinyl, Cassette, 8 Track, Singles, Ep, Lp, etc...

I believe I have a good normalized model that will achieve my needs, however before creating the database, I want to ask the experts so that my model is solid from the start.

Here are some models that I'm looking to implement:

ALBUM (Vinyl)
-------------
**VINYL**
SIDE A
   1. Track name
   2.. Track name
   3. Track name
   ...
SIDE B
   1. Track name
   2. Track name
   3. Track name
   ...

ALBUM (Double Vinyl)
-------------
**VINYL 1**
SIDE A
   1. Track name
   2. Track name
   3. Track name
   ...
SIDE B
   1. Track name
   2. Track name
   3. Track name
   ...

**VINYL 2**
SIDE A
   1. Track name
   2. Track name
   3. Track name
...
SIDE B
   1. Track name
   2. Track name
   3. Track name
   ...

ALBUM (CD)
-------------
**CD**
   1. Track name
   2. Track name
   3. Track name
   ...

   Extra Tracks
   11. Track name
   12. Track name
   ...

ALBUM (Compilation)
-------------
**CD 1**
   1.Track name
   2. Track name
   3. Track name
   ...
**CD 2**
   1.Track name
   2. Track name
   3. Track name
   ...
**DVD**
   1. Track name
   2. Track name
   3. Track name
   ...

ALBUM (8 Track)
-------------
**8 TRACK**
Program 1
   1. Track name
   2. Track name
   3. Track name
   ...

Program 2
   1. Track name
   2. Track name
   3. Track name
   ...

Program 3
   ...
Program 4
   ...

ALBUM (Cassette)
-------------
**CASSETTE**
SIDE A
   1. Track name
   2. Track name
   3. Track name
   ...

SIDE B
   1. Track name
   2. Track name
   3. Track name
   ...

Basically, there are albums that contain discs that contain tracks.  1 album can have multiple discs and 1 disc can only be part of one album.  1 disc can have multiple tracks and 1 track can be in multiple discs.

This is a basic normalized model I came up with. Is this a good foundation?

album (album_id, name, description, date_released, ...)

disclabel (disclabel_id, disclabel)
disc (disc_id, album_id, disclabel_id, description, position)

song (song_id, name, duration, lyrics, ...)

disclocation (disclocation_id, disclocation, position)
disctrack (disc_id, disclocation_id, song_id, tracknumber)

Here's an example of similar Data Model: http://www.databaseanswers.org/data_models/cd_collection/index.htm

This model has a lot of clarity than what you have describe. Take a look.