Categories
Mastering Development

Why my Geometry filter in SQL Server is not working?

I have uploaded the Local Government Area Boundary of the NSW, Australia into my MS-SQL database and try to filter for Sydney Area. Following is the my Script: CREATE TABLE [dbo].[nsw_lga_polygon_shp]( [id] [int] IDENTITY(1,1) NOT NULL, [geom] [geometry] NULL, [lg_ply_pid] [nvarchar](15) NULL, [dt_create] [date] NULL, [dt_retire] [date] NULL, [lga_pid] [nvarchar](15) NULL, [nsw_lga_sh] [date] NULL, [nsw_lga__1] […]

Categories
Database Development

why would a simple query have poor performance on MS SQL Server 2014 or 2014SP3 when on other versions it performs well?

I am running the following query on MS SQL Server 2014 (and also on 2014SP3) SET NOCOUNT ON CREATE TABLE #GUIDs( PartyNames_GUID UNIQUEIDENTIFIER NULL, Party_GUID UNIQUEIDENTIFIER NULL, FirstName NVARCHAR(255) NULL ) insert #GUIDs(Party_GUID) select top 1 Party_GUID FROM Party a join PartyNames b on a.Party_ID = b.Party_ID –Give the optimizer all kinds of choices. create […]

Categories
Mastering Development

I can’t figure out how to Order by with string_agg

I have this query (I am using SQL Server 2019) and is working fine (combining Dates and Notes into one column). However, the result I am looking for is to have the latest date show up first. How can I achieve that from this query? SELECT ID, ​(SELECT string_agg(​concat(Date, ‘: ‘, Notes), CHAR(13) + CHAR(10) […]

Categories
Mastering Development

Will a table with an int primary key outperform its uid equivalent?

I am working with a legacy Sql Server database which uses UNIQUEIDENTIFIER and am considering performance. If I have two tables, idencial except for the Identity column, something like this: CREATE TABLE [uidExampleTable] ( [exampleUid] UNIQUEIDENTIFIER CONSTRAINT [DF_uidExampleTable_uid] DEFAULT (newid()) NOT NULL, [name] VARCHAR (50) NOT NULL, [createdDate] DATETIME NOT NULL, CONSTRAINT [PK_uidExampleTable] PRIMARY KEY […]

Categories
Development

.NET and SQL layer for Price calculation based on date and distance

SQL table structure: TripDetails table MasterRateChartTable: Expected result: Here based on Source-DestinationName-TransporterName-IssuedOn date the price vary for the trips. I need to calculate the price per trip which need to shown in UI. Can you please help me to create Stored procedure and webapi in C#. Based on “IssuedOn” date falls under which startDate-EndDate and […]

Categories
Development

How to write query on field with list of delimitered categories

I have a table as below: CREATE TABLE [dbo].[UseCase]( [Id] [int] IDENTITY(1,1) NOT NULL, [UseCase] [text] NOT NULL, [Comment] [nvarchar](40) NOT NULL, [Categories] [nvarchar](255) NULL ) GO insert into [dbo].[UseCase]([UseCase],[Comment],[Categories])VALUES(‘This is use case 1′,’This is comment 1 for use case 1′,’Cat1|cat3|cat9’) insert into [dbo].[UseCase]([UseCase],[Comment],[Categories])VALUES(‘This is use case 2′,’This is comment 1 for use case 2′,’Cat2|cat9’) […]

Categories
Development

SQL: One 2 many rows with many rows combined into one column

I have four tables, namely tblProject (one record), tblTeamMembers (many records), tblProjectStatus (look-up table) and tblProjectScoresComments (many records). Using MSSQL 2017. Below is the table definitions: 1) tblProject: [ProjectID] [INT] IDENTITY(1,1) NOT NULL, [ProjectName] [NVARCHAR](150) NOT NULL, [CommunityProblem] [NTEXT] NOT NULL, [IctSolveCommunityProblem] [NTEXT] NOT NULL, [TeamMemberRoles] [NTEXT] NOT NULL, [ProjectImpact] [NTEXT] NOT NULL, [HelpRaiseFunds] [NTEXT] […]

Categories
Development

How can I construct a data model to bind to a SQL table with an XML column

I’m trying to use ASP.NET Core to read from a SQL database. I’m using Microsoft.EntityFrameworkCore.DbContext and I have it working except for any tables that have an XML column When try and read from one of these tables I get the following error: System.InvalidOperationException HResult=0x80131509 Message=The entity type ‘SqlXml’ requires a primary key to be […]

Categories
Development

Getting error “Incorrect syntax near the keyword ‘SELECT'” in SQL Server [closed]

Table schema: CREATE TABLE [dbo].[PunchClock] ( [Id] [int] IDENTITY(1,1) NOT NULL, [StaffId] [int] NULL, [LocationId] [int] NULL, [PunchIn] [datetime] NOT NULL, [PunchOut] [datetime] NULL, [PunchType] [varchar](1) NULL, [IsApproved] [bit] NOT NULL, [IsLate] [bit] NOT NULL, [ApprovedPunchIn] [datetime] NULL, [ApprovedPunchOut] [datetime] NULL, [ManagerComments] [nvarchar](100) NULL, [PunchInComments] [nvarchar](100) NULL, [PunchOutComments] [nvarchar](100) NULL, [ApprovedBy] [int] NULL, [ApprovedOn] [datetime] […]

Categories
Development

`bcp in` fails with “INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER'”

I exported a table (created with EF Core 2.2) with bcp %database%.MyTable out MyTable.dmp -n -T -S %sqlserver%. On reimporting it with bcp %database%.MyTable in MyTable.dmp -n -T -S %sqlserver% I get this error: SQLState = 37000, NativeError = 1934 Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]INSERT failed because the following SET options […]