The intent of this SQL Server query is to: - Dynamically work rebuild/reorganize all indexes in all tables in all databases on a server. - Rebuild or reorganize depending on the specified fragmentation threshold values. - Attempt to perform an online rebuild. If an online rebuild can not be performed due to column types, do an offline rebuild if allowed, otherwise just do a reorganize. - Excludes system databases like master, msdb, tempdb, model, and distribution I'm interested in a code review for correctness in logic. ``` --Reorganize and/or rebuild in all tables on all databases based on fragmentation thresholds DECLARE @iReorganizeMinThreshold INT = NULL; --Fragmentation amount between this and the rebuild threshold will cause a reorganize (NULL = never) DECLARE @iRebuildMinThreshold INT = NULL; --Fragmentation amount above this value will cause a rebuild (-1 = always rebuild, NULL = never) DECLARE @bAllowOfflineRebuild BIT = 0; --If true, indexes will be rebuilt even if they can't be rebuilt with online option. If false they will be reogranized instead --If you’re running SQL Server 2012 SP1 – SP2, parallel online index rebuilds can cause corruption. --https://sqlperformance.com/2014/06/sql-indexes/hotfix-sql-2012-rebuilds --https://support.microsoft.com/en-ca/help/2969896/fix-data-corruption-occurs-in-clustered-index-when-you-run-online-index-rebuild-in-sql-server-2012-or-sql-server-2014 PRINT 'Rebuilding/Reorganizing all indexes on every table in every database...' DECLARE @tblIndexes AS TABLE ( DatabaseName VARCHAR(255), SchemaName VARCHAR(255), TableName VARCHAR(255), IndexName VARCHAR(255), AvgPageFragmentation INT, CanRebuildOnline BIT ) PRINT 'Gethering indexes from all databases..' INSERT INTO @tblIndexes EXEC sp_MSforeachdb 'USE [?] SELECT [Database] = DB_NAME(dps.DATABASE_ID), [Schema] = OBJECT_SCHEMA_NAME(dps.OBJECT_ID), [Table] = OBJECT_NAME(dps.OBJECT_ID), [Index] = si.NAME

Basically this is my first login form. I am using SQL and C# WinForms. I made user roles such as "Admin" and others and the user is taken to a specific WinForms, according to his appointed role (appointed by me manually now). I have also allowed users to create their new accounts, in which they pick their user name and password but the role still needs to be appointed by me. I'd like to know if what I've done is ok or if it needs improvement, and where. ``` namespace My_PROGRAM { public partial class Login : Form { SqlConnection loginCon = new SqlConnection("Data Source=******;Initial Catalog=***;Persist Security Info=True;User ID=*****;Password=**********"); public Login() { InitializeComponent(); } private void btnLogin_Click(object sender, EventArgs e) { loginCon.Open(); SqlDataAdapter loginAdapter = new SqlDataAdapter("SELECT [Role] FROM [dbo].[LOGIN_Tab] WHERE Name ='"+ userNameTextobx.Text +"' and Password='"+ userPasswordTextbox.Text +"' ", loginCon); DataTable result = new DataTable(); loginAdapter.Fill(result); try { if (result.Rows.Count == 1) { switch(result.Rows[0]["Role"] as string) { case "Admin": { this.Hide(); AdminMenu aMenu = new AdminMenu(); MessageBox.Show("Login was succesful. Welcome back " + userNameTextobx.Text + " !!"); aMenu.Show(); break; } case "Planner": { this.Hide(); PlannerMenu pMenu = new PlannerMenu(); MessageBox.Show("Login was succesfu

I used the following Stack Overflow questions as references for writing this code: - Select columns from result set of stored procedure - What is the equivalent of String.Join on TSQL? The idea behind my stored procedure is to give an explanation of why a particular worker is unsuitable to care for a particular patient under specific circumstances. For example, you obviously can't assign an RN to do physical therapy visits. I have a Disciplines table, which has a structure that's kind of like this: ``` -------------------------------------------- | Discipline | Discipline ID | Other Field | -------------------------------------------- | RN | 1 | 2 | -------------------------------------------- | PT | 2 | 5 | -------------------------------------------- | CNA | 3 | 7 | -------------------------------------------- | MD | 4 | 23 | -------------------------------------------- ``` Obviously this is hypothetical data and I've modified the column names a bit (this is mostly for illustration). What I want is to retrieve the list of Disciplines as a single string and concatenate it with an existing string. Workers might have several disciplines. (This is determined in a table that's not shown). An example of one possible output would be `The worker does not have the appropriate disciplines. They have the following discipline(s): RN, PT` (if that employee is both a RN and a PT - not too likely, obviously, but let's assume that they are). I haven't included the entire stored procedure, but this is pretty representative and is the main part that I'm looking for a critique of: ``` -- @IsAppropriate is a bit type SELECT @IsAppropriate = dbo.fnCheckIfWorkerHasDisciplineForSvc(@BusinessUnitID, @WorkerID, @ServiceTypeID, GetDate(), GetDate()) IF @IsAppropriate = 0 BEGIN SET @output = 'The worker does not have the appropriate disciplines. They have th

I need to query the table below and list the distinct items at the top of my result set and the duplicates will follow, the order of the duplicates does not matter. I am working with Sql Server 2016 if that offers any nice tricks for improvement. Table design ``` create table Items(Id INT,Item VARCHAR(5)) insert into Items values(1,'Cat') insert into Items values(2,'Dog') insert into Items values(3,'Dog') insert into Items values(4,'Cat') insert into Items values(5,'Fish') insert into Items values(6,'Cat') insert into Items values(7,'Dog') ``` Here is my current query. It does function correctly but seems very poor and brute force'ish. ``` --Select all the items but have the distinct ones appear at the top and repeats following SELECT Id= (SELECT TOP 1 Id FROM Items it2 WHERE it2.Item = it1.Item ORDER BY Id ASC), Item FROM Items it1 GROUP BY Item UNION ALL SELECT * FROM Items WHERE Items.Id NOT IN ( SELECT Id= (SELECT TOP 1 Id FROM Items it2 WHERE it2.Item = it1.Item ORDER BY Id ASC) FROM Items it1 GROUP BY Item ) ``` Results ``` 1, Cat 2, Dog 5, Fish 3, Dog 4, Cat 6, Cat 7, Dog ```

I'm mainly looking at usage of Dapper, but in general, any comments relating SQL -> C# object mapping best practices would be appreciated. I've got two super simple objects I'm working with: ``` public class Subcategory { public Guid Id { get; set; } public Guid CategoryId { get; set; } public string Name { get; set; } } public class Category { public Category() { Subcategories = new List(); } public Guid Id { get; set; } public string Name { get; set; } public ICollection Subcategories { get; set; } } ``` `Id` for both of them is set by the db on insert via T-SQL's `newsequentialid()`. I'm writing repository classes for each of the objects that conform to the following interface (actually implemented via an abstract class): ``` public interface IMaintanceRepository where T: class { Guid Create(T toCreate); T Read(Guid id); T Update(T toUpdate); void Delete(Guid id); } ``` Right now, I'm trying to write the `Update` method in the `Category` repository. I have the following written, but I'm not sure if it's the most performant Dapper code as written. ``` public override Category Update(Category toUpdate) { var deleteRemovedSubcategories = @" DELETE FROM dbo.Subcategories WHERE CategoryId = @Id AND Id NOT IN @Ids"; var addNewSubcategories = @" INSERT INTO dbo.Subcategories (CategoryId, Name) VALUES (@CategoryId, @Name)"; var updateExistingSubcategories = @" CREATE TABLE #subcategories ( Id uniqueidentifier NOT NULL, CategoryId uniqueidentifier NOT NULL, Name varchar(50) NOT NULL ); INSERT INTO #subcategories VALUES (@Id, @CategoryId, @Name); UPDATE s1 SET s1.CategoryId = s2.CategoryId, s1.Name = s2.Name FROM dbo.Subcategories s1 INNER JOIN #subcategories s2 ON s1.Id = s2.Id; DROP TABLE #subcategories;"; var updateCategory = @"

I try to avoid keeping passwords etc. in memory or in plain text anywhere. But I am on a huge time crunch and this will only be used internally this week then probably won't get touched again. I just want to make sure this is secure and if not, what the risk is. I'm mostly concerned about the password sitting in memory. I'm not sanitizing data but for this particular case it isn't needed. My specific questions are: - From a security perspective, would this code be acceptable in a SQL Importer? - Besides data sanitation, is there a best practice somewhere that I'm missing in this? ``` Console.WriteLine("Enter the Server Name. Ex: SQLMASTER"); //Gets the server to connect to, an IP address is also acceptable. ServerName = Console.ReadLine(); Console.WriteLine("Enter the Database. Ex: Accounts"); //The actual database to use. DatabaseName = Console.ReadLine(); Console.WriteLine("Use Windows authentication? Y/N"); //you will usually use this. YesNo = Console.ReadLine(); if (YesNo.ToLower() != "y") { Console.WriteLine("Enter your Username (Domain name may be required). Ex: MyName"); UserName = Console.ReadLine(); Console.WriteLine("Enter your Password. Ex: ********"); Password = ReadPassword(); //Builds the connection string with the data we collected above. We're going to send this to the SQL Connection. (Username and Password) ConnectionString = "Data Source = " + ServerName + "; Initial Catalog = " + DatabaseName + "; User ID = " + UserName + "; Password = " + Password; Password = ""; //We do this to clear the password from memory UserName = ""; //Clearing username from memory } else { //Builds the connection string with the data we collected above. We're going to send this to the SQL Connection. (Windows Authentication) ConnectionString = "Data Source = " + ServerName + "; Initial Catalog = " + DatabaseName + "; Integrated Security=SSPI"; //User ID = " + UserName + "; Password = " + Password; } //Create a new connec

I am writing a program that reads from DB and outputs to a CSV file. Besides the regular columnar data there are 2 JSON fields data as well. The table layout looks like this (other fields removed for brevity): `+----+--------------+-------------+-----------------------+ | ID | Product_Type | Json_Data | Demographic_Questions | +----+--------------+-------------+-----------------------+ | 1 | DPI | {some_JSON} | {another_JSON} | +----+--------------+-------------+-----------------------+ | 2 | Travel | {some_JSON} | {another_JSON} | +----+--------------+-------------+-----------------------+ ` Program logic - Read data from DB - Store columnar data into a `map` - Convert JSON data into CSV format and store into the `map` - Write `map` into CSV file The main program `public static void extractData(String lastRunDateTime, String extractionType) throws Exception { List> flatJson = new ArrayList>(); String result = ""; ResultSet rsData = null; List productType = new ArrayList<>(); // Store Product Type name for SQL & CSV creation try { conn = dbUtil.dbConnect(); String sqlQuery = "SELECT DISTINCT Product_Type FROM Mapping WHERE Extraction_Type = '"+ extractionType +"'"; st = conn.prepareStatement(sqlQuery); rsData = st.executeQuery(); while(rsData.next()) { productType.add(rsData.getString("Product_Type")); } // Currently there are 4 product types in DB for(int i = 0; i map = new LinkedHashMap(); //LOG.debug("Sys_Policy_No = " + rsData.getString("Sys_Policy_No")); map.put("ID", rsData.getString("ID")); map.put("Product_type", rsData.getString("Product_type")); // Read JSON data and convert to columns result = rsData.getString("Json_Data"); if(result != null && result.length() != 0) { addKeys("", new ObjectMapper().

I have a problem where I need to replace every base part of URLs in a body of text. However, I only want to replace it in specific URLs. Only URLs that go to "resource.aspx" that have the "source" query string parameter need to get the replacement. Other URLs could have the target base part and we don't want to touch those. The resource urls are expected to appear inside quotes. I made a UDF that takes the text body, the base URL you want to replace, and the base URL to replace with and searches through the text and does the base URL replacement on any "resource URLs" it finds. Does the algorithm look sound? Anything I missed? Better ways to do this? I was sad to learn that REPLACE() does not work with a pattern, even though it's second paramter is named "string_pattern" :(. I was hoping it would work like .NET's Regex.Replace() (I know we don't have regex in t-sql, but a pattern based replace function would be nice sigh) ``` -- ============================================= -- Author: John Doe -- Create date: 11/29/2016 -- Description: Replaces base URLs in text body of every URL that points to -- resource.aspx that has "source" query string parameter. -- Replaces specified base url with specified replacement -- ============================================= CREATE FUNCTION dbo.udfReplaceResourceBaseURLs ( @TextBody NVARCHAR(MAX), @BaseUrlToReplace NVARCHAR(MAX), @BaseUrlReplacement NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN -- Create Copy of the original body. We will search and modify the copy in the loop DECLARE @EmailBodyCopy NVARCHAR(MAX) SET @EmailBodyCopy = @TextBody DECLARE @ResourceURLPattern NVARCHAR(MAX) = '%' + @BaseUrlToReplace + '/myResource.aspx%source=%' -- Do replacements while resource URLs are found in the copy DECLARE @resourceURLStart INT = PATINDEX(@ResourceURLPattern, @EmailBodyCopy) WHILE @resourceURLStart > 0 BE

Last night in The 2nd Monitor there was a discussion about postless users - I wanted to see how many there are, whether they're one-timers that showed up once, registered and never came back, and whether they vote at all. So I whipped up this query on SEDE, showing voters and their post count, as well as other information: ``` with sub as ( select distinct Users.Id ,Users.DisplayName ,Users.CreationDate ,Users.LastAccessDate ,datediff(day, cast(Users.CreationDate as date), cast(Users.LastAccessDate as date)) MembershipDays ,case when cast(Users.CreationDate as date) = cast(Users.LastAccessDate as date) then 1 else 0 end IsOneTimer ,sum(case when Posts.Id is null then 0 else 1 end) over (partition by Users.Id) PostCount ,Users.Reputation ,Users.UpVotes + Users.DownVotes TotalVotes ,Users.UpVotes ,Users.DownVotes from Users left join Posts on Users.Id = Posts.OwnerUserId where Users.Id > 0 ), agg as ( select Id ,DisplayName ,CreationDate ,LastAccessDate ,IsOneTimer ,MembershipDays ,case when MembershipDays = 0 then TotalVotes else TotalVotes / cast(MembershipDays as decimal) end VotesPerDay ,PostCount ,Reputation ,TotalVotes ,UpVotes ,DownVotes from sub where TotalVotes > 0) select Id ,DisplayName ,CreationDate ,LastAccessDate ,IsOneTimer ,MembershipDays ,round(VotesPerDay, 3) VotesPerDay ,PostCount ,Reputation ,TotalVotes ,UpVotes ,DownVotes from agg order by PostCount ,cast(VotesPerDay as int) desc ,cast(LastAccessDate as date) desc ,TotalVotes desc ,Reputation ,IsOneTimer ,CreationDate ``` Returns 26740 rows returned in 211 ms (cached). Is there any way it could be improved, performance or otherwise? Postless Users query on SEDE.

This code iterates through the table `@Table1` to get the `Purchaser`, `PurchaserID`, `SaleID` store the values in a variable, then queries a secondary table to get data for the specific purchaser. 2 different data sets (one for daily, one for weekly) checks if a folder for the purchaser already exists. If not, it creates then saves the .csv and emails it to the email address listed for the purchaser in `@Table1`. The code works, but how would you improve it? ``` CREATE TABLE [dbo].[AI]( [ID] [int] IDENTITY(1,1) NOT NULL, [saledate] [datetime] NULL, [trn] [varchar](max) NULL, [purchaser] [varchar](max) NULL, [primaryaddress] [varchar](max) NULL, [secondaryaddress] [varchar](max) NULL, [city] [varchar](200) NULL, [state] [varchar](50) NULL, [zip] [varchar](10) NULL, [purchaserid] [varchar](50) NULL, [saleID] [varchar](50) NULL) CREATE TABLE [dbo].[LI]( [ID] [int] IDENTITY(1,1) NOT NULL, [saledate] [datetime] NULL, [trn] [varchar](max) NULL, [purchaser] [varchar](max) NULL, [primaryaddress] [varchar](max) NULL, [secondaryaddress] [varchar](max) NULL, [city] [varchar](200) NULL, [state] [varchar](50) NULL, [zip] [varchar](10) NULL, [itempurchased] [varchar](max) NULL, [amtpurchased] [int] NULL, [amtshipped] [int] NULL, [purchaserid] [varchar](50) NULL, [saleID] [varchar](50) NULL) ``` ``` DECLARE @SendEmailTo VARCHAR(MAX), @Purchaser NVARCHAR(500), @AI nvarchar(4000), @body nvarchar(max); DECLARE @emailbody varchar(MAX),@filedate varchar(500), @sql varchar(8000), @DateToAppend nvarchar(100); DECLARE @LI nvarchar(4000), @PurchaserID varchar(50), @SaleID varchar(50); DECLARE @chkdirectory as nvarchar(4000), @folder_exists as int, @EmailAttachment nvarchar(MAX); Declare @Data1 Table (purchaser varchar(500), purchaserid varchar(50), saleid varchar(50), email varchar(100), active varchar(5)) INSERT INTO @Data1 (purchaser, purchaserid, saleid, email, active) VALUES ('Green', 'G12', '22', '12', 'xxx@gmail.com', '1'), ('Red', 'R11', '10', '14', 'xxx1@gmail.com', '1') Se